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

Yes Let me look into your cool suggestion.

--- On Sun, 5/24/09, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

From: Vern Hamberg <vhamberg@xxxxxxxxxxx>
Subject: Re: How to use buckets for month/year comparisons in SQL only.
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: Sunday, May 24, 2009, 8:23 PM

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