Re: How to use buckets for month/year comparisons in SQL only.

Adam

If it's easy in RPG, I'd say do it in RPG - I use SQL a lot but I don't have a religious impulse to use only SQL

Having said that, I guess you might want to use correlated table expressions - the WITH AS kind of thing, one for current year, one for last year, with the buckets you need - then run some kind of JOIN on them with whatever compare operation you need - does that even come close to responding to your question?

HTH
Vern

Adam West wrote:

Hi-
I have a SQL statement in use that gets the history data and current data and then I can write a basic report. This works fine. Now I have to make this new report that will show only the Quoted fields for the current month/year versus Last month /year as well as 3 months prior.

I was not sure if I do my grouping in the SQL which is pasted below. I would previously just do this in RPG but now I am attempting to wean myself off of this method. The starting point will come from previous month for now although may become a parameter but not for now. So for now the report starts with April 2009/April 2008 and going back 3 mo's.

SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC, SOP10200.SOPTYPE, SOP10100.DOCDATE,
'Current' as source

FROM ((((PBS.dbo.SOP10200
SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))
INNER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR

UNION ALL

SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC, SOP30300.SOPTYPE, SOP30200.DOCDATE,
'History' as source

FROM ((((PBS.dbo.SOP30300
SOP30300 LEFT OUTER JOIN PBS.dbo.IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))
LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR