>SELECT ; > SaleDateLast, SPlast, SQlast, ; > SaleDatePrev, SPprev, SQprev, ; > SaleDateRest, SPrest, SQrest ; >FROM ( ; > SELECT 1 AS pk, SaleDate AS SaleDateLast, SUM(pp) AS SPlast, SUM(qq) AS SQlast ; > FROM mytable ; > WHERE SaleDate =(SELECT MAX(SaleDate) FROM mytable) ; > GROUP BY 1,2 ; > ) ld ; > JOIN ( ; > SELECT 1 AS pk, SaleDate AS SaleDatePrev, SUM(pp) AS SPprev, SUM(qq) AS SQprev ; > FROM mytable ; > WHERE SaleDate =(SELECT MAX(SaleDate) FROM mytable ; > WHERE SaleDate < (SELECT MAX(SaleDate) FROM mytable)) ; > GROUP BY 1,2 ; > ) pd ON ld.pk = pd.pk ; > JOIN ( ; > SELECT 1 AS pk, {} AS SaleDateRest, SUM(pp) AS SPrest, SUM(qq) AS SQrest ; > FROM mytable ; > WHERE SaleDate < (SELECT MAX(SaleDate) FROM mytable ; > WHERE SaleDate < (SELECT MAX(SaleDate) FROM mytable)) ; > GROUP BY 1,2 ; > ) rd ON rd.pk = pd.pk >>>
>>Sale Date P Q >>1/4/06 9 3 >>2/4/06 2 7 >>2/4/06 3 8 >>2/4/06 6 6 >>3/4/06 1 5 >>5/4/06 4 2 >>5/4/06 5 9 >>>>
>>latest_date P_lt q_lt date-1 p-1 q-1 date-rest p-rest q-rest >>5/4/06 9 11 3/4/06 1 5 {} 20 24 >>>>