SQL that does a weekly; to do monthly

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

SQL that does a weekly; to do monthly

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-10 : 16:24:20

We have an existing SQL that drives a Report, as a weekly. It takes a from and to date, and shows the sales per that week.

I need to modify this if possible to make it for a month.

WHen I put a month in now, it will display 4 weeks via the Reporting Services matrix.
If someone sees how to make this in such a way that it would display monthly totals, assuming the user enteres a from and to dates for an entire month:

SELECT SOP10200.XTNDPRCE Price, SOP10200.QUANTITY quantity, DATEADD(WK,DATEDIFF(WK,0,SOP10100.DOCDATE),0) ID,
SOP10200.ITEMNMBR + char(13) + IV00101.ITEMDESC + char(13) + 'Par Level:' ITEMDESC, IV00101.ITMGEDSC
FROM dbo.sop10200 AS SOP10200 INNER JOIN
dbo.iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.rm00101 ON SOP10100.CUSTNMBR = dbo.rm00101.CUSTNMBR
Where SOP10100.DOCDATE between @StartDate and @EndDate and dbo.rm00101.CUSTNAME = @CustName
order by IV00101.ITMGEDSC, IV00101.ITEMDESC, DATEADD(WK,DATEDIFF(WK,0,SOP10100.DOCDATE),0)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-10 : 16:44:15

The statement only returns raw data it does not do any Grouping and Totals. So if your current report has weekly totals included than I would say that is happening in your "reporting Services matrix" so you would need to change that (or add a new report).

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-10 : 16:53:59

try this


SELECT   sop10200.xtndprce                                                         price, 
         sop10200.quantity                                                         quantity, 
         Dateadd(mm,Datediff(mm,0,sop10100.docdate),0)                             id, 
         sop10200.itemnmbr + Char(13) + iv00101.itemdesc + Char(13) + 'Par Level:' itemdesc, 
         iv00101.itmgedsc 
FROM     dbo.sop10200 AS sop10200 
         INNER JOIN dbo.iv00101 AS iv00101 
           ON sop10200.itemnmbr = iv00101.itemnmbr 
         INNER JOIN dbo.sop10100 AS sop10100 
           ON sop10200.soptype = sop10100.soptype 
              AND sop10200.sopnumbe = sop10100.sopnumbe 
         INNER JOIN dbo.rm00101 
           ON sop10100.custnmbr = dbo.rm00101.custnmbr 
WHERE    sop10100.docdate BETWEEN @StartDate AND @EndDate 
         AND dbo.rm00101.custname = @CustName 
ORDER BY iv00101.itmgedsc, 
         iv00101.itemdesc, 
         Dateadd(mm,Datediff(mm,0,sop10100.docdate),0)

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-10 : 16:57:51

explanation: i'm assuming here the matrix is being grouped on the "id" column, if that's the case we have to change it to have a monthly date instead of a weekly date.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-10 : 17:10:33

Thank you Rohit, that is awesome!

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-10 : 23:42:38

How can you get the month as a separate text item for the report?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources