Author |
Topic  |
|
mimuk
Starting Member
19 Posts |
Posted - 09/05/2013 : 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
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 09/05/2013 : 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 - 09/05/2013 : 11:38:12
|
-- 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;
|
 |
|
mimuk
Starting Member
19 Posts |
Posted - 09/05/2013 : 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 - 09/05/2013 : 12:56:51
|
Try -12 instead of -13, otherwise provide some test data. |
 |
|
mimuk
Starting Member
19 Posts |
Posted - 09/06/2013 : 07:29:15
|
Thanks ifor. That returns the correct values. Brilliant!
Mim |
 |
|
|
Topic  |
|
|
|