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

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];

-- 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;