Create Buckets for previous month/year vs. current

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create Buckets for previous month/year vs. current

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-24 : 14:32:33

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. My question is can I have these buckets distinctly in the SQL or must I use programming language to finesse these?

I was not sure if I do my grouping in the SQL which is pasted below. 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

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 14:40:20

Are you using reporting services?#

Easiest is to create one row per date so that your different months apppear in different rows.
Then create a column group in the report by date so that the different dates appear in different columns.
That makes the report very simple as well as the sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-05-24 : 23:01:51

Nr. I am not using Report Services. Is this difficult to aborb?
I am too new to SQL to know how to but are you saying I should code the SQL to only read the previous month and the previous 3 months and then I will have only the records I need. This seems fairly simple perhaps that is all this is?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 00:24:06

Yep.
If you do that then your reporting mechanism should be able to crosstab the result.
If not then put the result into a temp table and do the crosstab in t-sql - if you later change the reporting tool you can then just return the temp table data to do the work in the report.

Reporting services is pretty simple to pick up if you don't try to do too much to start with.
The difficult bit is integrating with the corporate security model.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources