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