|
I am trying the max function right now and see if that will work in the context of the report....if it does I'll advise..... On Thu, 18 Aug 2005 08:09:13 -0500, Vernon Hamberg wrote > Hi Charles > > Since Douglas needs the last date, the summary functions are still > usable. Peter mentioned the MAX() function - that is one of the > summary functions. In the SQL manual I think these are called column > functions (IIRC), as opposed to scalar functions like substr() and > concat(). > > A different context helps sometimes - in Query/400 summary screen > there are 4 or 5 columns we can check off for summaries. One is the > MAXIMUM. There are similar functions in SQL for each column - TOTAL, > SUM, AVG, MAX, MIN, even STDDEV, IIRC, which is not in Query/400. > > Vern > > At 07:24 AM 8/18/2005, you wrote: > > >Douglas, > > > >It seems that the requirement for date sold switches the query from a > >summary one to a detail one. Unless you normally have multiple sales of > >the same item to the same customer on the same day. > > > >Thus, you're going to have to move the summary stuff from the query to the > >RPG program itself. > > > >C/EXEC SQL > >C+ DECLARE DETAILCURSOR CURSOR > >C+ FOR SELECT > >C+ ILNINV#A, > >C+ ILNSOLDTO, > >C+ ILNINVDAT, > >C+ ICMNAME, > >C+ ILNSTK, > >C+ ILNDESC, > >C+ ILNEPRICE, > >C+ ILNEUAVCST, > >C+ ILNEPRICE - ILNEUAVCST AS MARGIN > >C+ FROM INVMARGIN > >C+ WHERE > >C+ ILNINV#A = :ALPHPARM AND > >C+ ILNINVDATE >= :SDATE AND ILNINVDATE <= :EDATE > >C+ ORDER BY > >C+ ILNINV#A, > >C+ ILNSOLDTO, > >C+ MARGIN DESC > >C/END-EXEC > > > > /free > > // code to handle breaks > > wTotalPrice += ILNEPRICE; > > wTotalCost += ILNEUAVCST; > > wTotalMargin += MARGIN; > > // other code as required > > /end-free > > > > > > > >Charles Wilt > >-- > >iSeries Systems Administrator / Developer > >Mitsubishi Electric Automotive America > >ph: 513-573-4343 > >fax: 513-398-1121 > > > > > > > -----Original Message----- > > > From: midrange-l-bounces@xxxxxxxxxxxx > > > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme > > > Sent: Thursday, August 18, 2005 8:13 AM > > > To: Mid Range List > > > 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. > > > > > > > > > > > > If you bought, it was hauled by a truck - somewhere, sometime. > > > > > > -- > > > This is the Midrange Systems Technical Discussion > > > (MIDRANGE-L) mailing list > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx > > > To subscribe, unsubscribe, or change list options, > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx > > > Before posting, please take a moment to review the archives > > > at http://archive.midrange.com/midrange-l. > > > > > > > > > >-- > >This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list > >To post a message email: MIDRANGE-L@xxxxxxxxxxxx > >To subscribe, unsubscribe, or change list options, > >visit: http://lists.midrange.com/mailman/listinfo/midrange-l > >or email: MIDRANGE-L-request@xxxxxxxxxxxx > >Before posting, please take a moment to review the archives > >at http://archive.midrange.com/midrange-l. > > -- > This is the Midrange Systems Technical Discussion (MIDRANGE-L) > mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To > subscribe, unsubscribe, or change list options, visit: > http://lists.midrange.com/mailman/listinfo/midrange-l or email: > MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment > to review the archives at http://archive.midrange.com/midrange-l. If you bought, it was hauled by a truck - somewhere, sometime.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.