December 15, 2014 at 2:14 pm
Hey All,
I'm trying to automate this report that 1 of our users manually updates in Excel. It's a report that just sums values for specific PKs in the database, and i need to get the SUM for each ID for each month.
Could anyone suggest a way i could build this using Report Builder? At first i tried setting up a script where each month would be for example :
DECLARE @Jan DECIMAL(6,2)
SET @Jan = (SELECT SUM(pyd_amount) from paydetail p
where p.itm_code in ('A','B','C','D')
and date between '1-1-2014' and '1-31-2014')
I get the correct values for January, but i can't figure out how to apply this expression such that it runs based on beginning and end of each month of the year, so 12 columns total with the summed value for N+1 itm_code.
I understood that using this logic, this report would have to run 12 separate queries to get the summed value for each month...
Please help! Thanks in advance!!
December 15, 2014 at 2:26 pm
Would the query below be a better starting point for you?
SELECT
DATEPART(Year, [date]) AS [Year]
,DATEPART(Month, [date]) AS [Month]
,SUM(pyd_amount)
FROM paydetail p
WHERE p.itm_code in ('A','B','C','D')
and [date] between '1-1-2014' and '1-31-2014'
ORDER BY DATEPART(Year, [date]), DATEPART(Month, [date]) AS [Month]
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 15, 2014 at 2:31 pm
I don't need the dates in the column headers, just the month, the data cells would be the sum amount for A, B, C and D; 12 columns, 4 rows excluding the headers. I guess we could put the year as a parameter in the report so they can select which year to run.
MY main question is though, would it be possible to build the script so that each column is it's own expression? Are there less expensive ways of getting the same result?
I'll give that a shot and will update tomorrow, thanks for the input!
December 15, 2014 at 4:49 pm
If you want to load variables, then as below. If you just want to list each month, remove the "@Jan = " and put " AS Jan" after the SUM.
SELECT
@Jan = SUM(CASE WHEN MONTH(p.date) = 01 THEN pyd_amount ELSE 0 END),
@Feb = SUM(CASE WHEN MONTH(p.date) = 02 THEN pyd_amount ELSE 0 END),
@mar = SUM(CASE WHEN MONTH(p.date) = 03 THEN pyd_amount ELSE 0 END),
--...
@Dec = SUM(CASE WHEN MONTH(p.date) = 12 THEN pyd_amount ELSE 0 END)
FROM paydetail p
WHERE p.itm_code in ('A','B','C','D')
and p.date >= '20140101'
and p.date < '20150101'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 15, 2014 at 5:09 pm
You mean like cross tabs?
December 16, 2014 at 6:47 am
Thank you!!! This worked great! Applied it as you suggested and the results are fairly close to previous month.
Thanks everyone else for your input!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply