Re: SELECT is faster on SQL Server

On 2021-03-19 7:11 PM, Thomas Kellerer wrote:
> Frank Millman schrieb am 19.03.2021 um 10:16:
>>
>> cl_bal selects WHERE tran_date <= '2018-03-31'.
>>
>> op_bal selects WHERE tran_date < '2018-03-01'.
>>
>> The second one could be written as WHERE tran_date <= '2018-02-28',
>> but I don't think that would make any difference.
>
> I knew I overlooked something ;)
>
> But as one is a true subset of the other, I think you can merge that
> into a single SELECT statement:
>
>     select '2018-03-01' AS op_date,
>            '2018-03-31' AS cl_date,
>            a.source_code_id,
>            sum(a.tran_tot) AS cl_tot,
>            sum(a.tran_tot) filter (where tran_date < '2018-03-01') AS
> op_tot
>     FROM (
>        SELECT distinct on (location_row_id, function_row_id,
> source_code_id) source_code_id, tran_tot, tran_date
>        FROM prop.ar_totals
>        WHERE deleted_id = 0
>          AND tran_date <= '2018-03-31'
>          AND ledger_row_id = 1
>        ORDER BY location_row_id, function_row_id, source_code_id,
> tran_date DESC
>     ) AS a
>     GROUP BY a.source_code_id
>
Thanks very much Thomas - I did not know about FILTER.

But it does not quite work. If the SELECT does find a row where the max
tran_date is <= '2018-03-31' it correctly includes it in 'cl_tot'. But
the filter returns nothing for 'op_tot' because there is no
corresponding row where tran_date < '2018-03-01'.

But I have learned something new, so thanks for that.

Frank