Hi Sergey:
Nothing is ever so simple. I left out another level of complexity, thinking I would be able to do it, but I can't seem to work it out. Here's the next level
Salesman Sale Date P Q
Joe 2/4/06 2 7
Joe 2/4/06 3 8
Joe 2/4/06 6 6
Joe 3/4/06 1 5
Joe 5/4/06 4 2
Joe 5/4/06 5 9
Boe 1/4/06 1 5
Boe 2/4/06 2 6
Boe 2/4/06 3 7
Boe 4/4/06 4 8
Boe 4/5/06 5 9
Boe 4/5/06 6 0
Doe 3/4/06 1 7
Doe 6/4/06 2 8
I want the following results:
sman lst_date Plt qlt date-1 p-1 q-1 date-rest p-rest q-rest
Joe 5/4/06 9 11 3/4/06 1 5 {} 11 21
Boe 4/5/06 11 9 4/4/06 4 8 {} 6 18
Doe 6/4/06 2 8 3/4/06 1 7 NULL NULL NULL
'Doe' only has two dates. This can be accomplished with LEFT OUTER JOIN. Otherwise I can't figure out how to handle the salesman level.
Thanks,
Yossi
>
>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
>
>>
>>Consider the following data:
>>
>>
>>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
>>
>>
>>I want to construct a select statement that will return the following:
>>
>>
>>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
>>
>>
>>In other words, I want to get a total of products sold on the latest date,
whatever that is; products sold on the date before the latest date,
whatever that is; and products sold on all the other prior dates.
>>
>>Can this be done?
>>
>>Thanks,
>>
>>Yossi