SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help with Multiple Row SUM in SQL 2008 R2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mimuk
Starting Member

17 Posts

Posted - 09/05/2013 :  10:43:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 09/05/2013 :  11:37:14  Show Profile  Reply with Quote
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];
Go to Top of Page

Ifor
Aged Yak Warrior

590 Posts

Posted - 09/05/2013 :  11:38:12  Show Profile  Reply with Quote

-- 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;
Go to Top of Page

mimuk
Starting Member

17 Posts

Posted - 09/05/2013 :  12:25:57  Show Profile  Reply with Quote
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
Go to Top of Page

Ifor
Aged Yak Warrior

590 Posts

Posted - 09/05/2013 :  12:56:51  Show Profile  Reply with Quote
Try -12 instead of -13, otherwise provide some test data.
Go to Top of Page

mimuk
Starting Member

17 Posts

Posted - 09/06/2013 :  07:29:15  Show Profile  Reply with Quote
Thanks ifor.
That returns the correct values. Brilliant!

Mim
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000