Help with Multiple Row SUM in SQL 2008 R2

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

SQL Server 2012 Forums

Transact-SQL (2012)

Help with Multiple Row SUM in SQL 2008 R2

Author

Topic

mimuk
Starting Member

19 Posts

Posted - 2013-09-05 : 10:43:43

I have a Microsoft SQL Server 2008 R2 table as follows..

CREATE TABLE [dbo].[CRSpendByPeriod](
[crmcref] [char](6) NOT NULL,
[year] [numeric](5, 0) NOT NULL,
[mtdvalue_1] [numeric](10, 2) NULL,
[mtdvalue_2] [numeric](10, 2) NULL,
[mtdvalue_3] [numeric](10, 2) NULL,
[mtdvalue_4] [numeric](10, 2) NULL,
[mtdvalue_5] [numeric](10, 2) NULL,
[mtdvalue_6] [numeric](10, 2) NULL,
[mtdvalue_7] [numeric](10, 2) NULL,
[mtdvalue_8] [numeric](10, 2) NULL,
[mtdvalue_9] [numeric](10, 2) NULL,
[mtdvalue_10] [numeric](10, 2) NULL,
[mtdvalue_11] [numeric](10, 2) NULL,
[mtdvalue_12] [numeric](10, 2) NULL,
[ytdvalue] [numeric](10, 2) NULL,) 
ON [PRIMARY]
GO

If I select where year = 2013 and 2014 it returns 2 rows per crmcref (Account Number)

I need to sum mtdvalue_5, mtdvalue_6, mtdvalue_7, mtdvalue_8, mtdvalue_9, mtdvalue_10, mtdvalue_11, mtdvalue_12 where year is 2013 and then add mtdvalue_1, mtdvalue_2, mtdvalue_3, mtdvalue_4 where year is 2014

In essence the data is split into Financial Years and I need to work out the math based on Past 12 months (mtdvalue_1) being May and year being financial year (2014 being current year).

I would really appreciate some help.
Its worth noting I have no control of the database tables and structure (normalization), but I can create views.

Mim

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-05 : 11:37:14

I think what you want is something like this from what I understood:

SELECT
	COALESCE(a.crmcref,B.crmcref) AS crmcref,
	COALESCE(a.mtdvalue_5,0) + 
	COALESCE(a.mtdvalue_6,0) + 
	COALESCE(a.mtdvalue_7,0) + 
	COALESCE(a.mtdvalue_8,0) + 
	COALESCE(a.mtdvalue_9,0) + 
	COALESCE(a.mtdvalue_10,0) + 
	COALESCE(a.mtdvalue_11,0) + 
	COALESCE(a.mtdvalue_12,0) + 
	COALESCE(b.mtdvalue_1,0) + 
	COALESCE(b.mtdvalue_2,0) + 
	COALESCE(b.mtdvalue_3,0) + 
	COALESCE(b.mtdvalue_4,0) AS FYTotal
FROM
	CRSpendByPeriod a
	FULL JOIN CRSpendByPeriod b ON 
		a.crmcref = b.crmcref AND a.[year]+1 = b.[year];

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-09-05 : 11:38:12

[code]
-- Should use a number/tally table - google
-- Using spt_values for convenience.
WITH numbers
AS
(
SELECT number
FROM [master].dbo.spt_values
WHERE [type] = 'P'
AND number > 0
AND number < 13
)
,CRSpendYearMonth
AS
(
SELECT crmcref
,CASE N.number
WHEN 1 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '0501' AS datetime)
WHEN 2 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '0601' AS datetime)
WHEN 3 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '0701' AS datetime)
WHEN 4 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '0801' AS datetime)
WHEN 5 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '0901' AS datetime)
WHEN 6 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '1001' AS datetime)
WHEN 7 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '1101' AS datetime)
WHEN 8 THEN CAST(CAST(C.[year] - 1 AS char(4)) + '1201' AS datetime)
WHEN 9 THEN CAST(CAST(C.[year] AS char(4)) + '0101' AS datetime)
WHEN 10 THEN CAST(CAST(C.[year] AS char(4)) + '0201' AS datetime)
WHEN 11 THEN CAST(CAST(C.[year] AS char(4)) + '0301' AS datetime)
WHEN 12 THEN CAST(CAST(C.[year] AS char(4)) + '0401' AS datetime)
END AS YearMonth
,CASE N.number
WHEN 1 THEN mtdvalue_1
WHEN 2 THEN mtdvalue_2
WHEN 3 THEN mtdvalue_3
WHEN 4 THEN mtdvalue_4
WHEN 5 THEN mtdvalue_5
WHEN 6 THEN mtdvalue_6
WHEN 7 THEN mtdvalue_7
WHEN 8 THEN mtdvalue_8
WHEN 9 THEN mtdvalue_9
WHEN 10 THEN mtdvalue_10
WHEN 11 THEN mtdvalue_11
WHEN 12 THEN mtdvalue_12
END AS Amount
FROM dbo.CRSpendByPeriod C
CROSS JOIN numbers N
)
SELECT crmcref
,SUM(Amount) AS Amount
FROM CRSpendYearMonth
WHERE YearMonth >= DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 13, 0)
AND YearMonth < DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP), 0)
GROUP BY crmcref;
[/code]

mimuk
Starting Member

19 Posts

Posted - 2013-09-05 : 12:25:57

Thanks Guys...
James K your code is presenting the correct values that I need.. Fantastic. Thanks very much.

Ifor the values in your query are out.. I cannot work out why as I am slightly lost in your syntax (sorry)
it would be great if I could get yours working as I believe I can use your query for following months rather than altering Jame's code for October. (showing me past 12 months values)

Cheers in advance

Mim

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-09-05 : 12:56:51

Try -12 instead of -13, otherwise provide some test data.

mimuk
Starting Member

19 Posts

Posted - 2013-09-06 : 07:29:15

Thanks ifor.
That returns the correct values. Brilliant!

Mim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources