Re: SQL Help needed

Douglas,

maybe you can use the MAX() function?

HTH,

Peter Colpaert
Application Developer
Massive - Kontich, Belgium
-----
Yoda of Borg are we.  Futile is resistance, assimilated will you be.
-----




"Douglas W. Palme" <dpalme@xxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
18/08/2005 14:12
Please respond to Midrange Systems Technical Discussion

 
        To:     "Mid Range List" <midrange-l@xxxxxxxxxxxx>
        cc: 
        Subject:        SQL Help needed


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, it 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:

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

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.