SQL Help needed

[PeteAmsterdam]

I need some serious sql help. I have a program that goes through our line item sales data and produces a report on a customer by customer basis detailing the summary data for the line items, IE: total sales, total cost,
margins, etc. However, in meeting with the sales staff last week, they want to know when each item was last purchased. Frankly I have no idea
whatsoever of how to include that in the statement.

Here is what I have right now:


DECLARE DETAILCURSOR CURSOR
FOR SELECT
ILNINV#A,
ILNSOLDTO,
ICMNAME,
ILNSTK,
ILNDESC,
SUM(ILNEPRICE),
SUM(ILNEUAVCST),
SUM(ILNEPRICE) - SUM(ILNEUAVCST) AS MARGIN
FROM INVMARGIN
WHERE
ILNINV#A = :ALPHPARM AND
ILNINVDATE >= :SDATE AND ILNINVDATE <= :EDATE
GROUP BY
ILNINV#A,
ILNSOLDTO,
ICMNAME,
ILNSTK,
ILNDESC
ORDER BY
ILNINV#A,
ILNSOLDTO,
MARGIN DESC


There is a field included in the file called ILNINVDATe which is the sale date, but I am only using that field thus far for record selection....any
ideas or suggestions would be appreciated.

 

[NoCoolHandle]

I would tend to believe that inlinvdate might be the one.. if so.

 FOR SELECT                                    
        ILNINV#A,                                 
       ILNSOLDTO,                                
        ICMNAME,                                  
        ILNSTK,                                   
        ILNDESC,
[blue]        ILNINVDATE,[/blue]                                  
        SUM(ILNEPRICE),                           
        SUM(ILNEUAVCST),                          
        SUM(ILNEPRICE) - SUM(ILNEUAVCST) AS MARGIN
    FROM INVMARGIN                                
    WHERE                                         
        ILNINV#A = :ALPHPARM AND                     
        ILNINVDATE >= :SDATE AND ILNINVDATE <= :EDATE
    GROUP BY                                         
        ILNINV#A,                                    
        [blue]ILNIVDATE,[/blue]
        ILNSOLDTO,                                   
        ICMNAME,                                     
        ILNSTK,                                      
        ILNDESC                                      
    ORDER BY                                         
        ILNINV#A,  
        ILNSOLDTO, 
        MARGIN DESC