Thanks, Sergey, that worked, except I had to add the salesman in the list.
Yossi
>Try
>SELECT ;
> SaleDateLast, SPlast, SQlast, ;
> SaleDatePrev, SPprev, SQprev, ;
> SaleDateRest, SPrest, SQrest ;
>FROM ( ;
> SELECT Salesman, SaleDate AS SaleDateLast, SUM(pp) AS SPlast, SUM(qq) AS SQlast ;
> FROM mytable mt11 ;
> WHERE SaleDate =(SELECT MAX(SaleDate) FROM mytable WHERE Salesman = mt11.Salesman) ;
> GROUP BY 1,2 ;
> ) ld ;
> JOIN ( ;
> SELECT Salesman, SaleDate AS SaleDatePrev, SUM(pp) AS SPprev, SUM(qq) AS SQprev ;
> FROM mytable mt21 ;
> WHERE SaleDate = (SELECT MAX(SaleDate) FROM mytable mt22;
> WHERE Salesman = mt21.Salesman AND SaleDate < (SELECT MAX(SaleDate) FROM mytable WHERE Salesman = mt22.Salesman)) ;
> GROUP BY 1,2 ;
> ) pd ON ld.Salesman = pd.Salesman ;
> LEFT JOIN ( ;
> SELECT Salesman, {} AS SaleDateRest, SUM(pp) AS SPrest, SUM(qq) AS SQrest ;
> FROM mytable mt31 ;
> WHERE SaleDate < (SELECT MAX(SaleDate) FROM mytable mt32;
> WHERE Salesman = mt31.Salesman AND SaleDate < (SELECT MAX(SaleDate) FROM mytable WHERE Salesman = mt32.Salesman)) ;
> GROUP BY 1,2 ;
> ) rd ON rd.Salesman = pd.Salesman
>
>>>So far, that's it. Why? Do you have two approaches?
>>
>>P.S. thanks for your quick responses!
>>
>>Yossi
>>
>>>How many more levels do you have?
>>>
>>>>
>>>>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.
>>>>
