Matrix Issue (Variance not Sum)
-
Hi There,
Ive been from pillar to post on this topic now and I'm still no nearer to achieving my goal
I have a matrix as follows
Year
2009 2010
Asset
A 45 65
B 17 10
C 100 89
I want to have a variance column on the end so results would be;
Year
2009 2010 Var
Asset
A 45 65 20
B 17 10 -7
C 100 89 -11
But all I can seem to do is sum them, Ive tried custom code calulated fields and everything I can think of but nothing is working, I'm sure is probably really simple but any help at all will be really appreciated
Thanks
Carl.
-
It is easy, unless there is something you are not telling us.
Do us the courtesy of posting the schema or code for the things you've tried.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Ive tried a matrix
Asset [PeriodYear]
[Asset] [Count)TaskID)]
Total [Count(TaskID)]
But no matter what I do when adding a total it only sums
Sorry I dont know how else to explain this!
Cheers
Carl.
-
Last I heard, adding produces a sum and subtracting produces a difference (or variance).
I don't know what else to tell you.
You're in a T-SQL forum.
Surely you aren't saying that you don't know how to subtract one column in a table from another. But on the off chance that you are:
select asset, [2009], [2010], [2010]-[2009] as variance
from dbo.Matrix
Notice that I enclosed your column names in square brackets. That's because 2010-2009 = 1 every time. The brackets were needed to tell SQL you were referring to column names, not constant values.
Word of advice: NEVER give your columns names that are integers. Call them y2010, y2009, etc.
If this is indeed the answer, you could have saved us both a lot of hassle by posting your query, and not forcing me to speculate. Please read this article [/url]and follow it for future posts. I'm off to work. Have a good day.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
My column names are not [2009] and [2010] my column name is PeriodYear and then I have 5000 records for example that are 2009,2010 along with an asset so for example 6 records could be:
Asset PeriodYear
A 2009
A 2009
B 2010
C 2009
C 2010
C 2010
I then want the matrix to show:
Year 2009 2010
Asset
A 2 0
B 0 1
C 1 2
but having achieved this I want to beableto show a variance column, which it wont let me!
-
Something like this?
DECLARE @T TABLE(Asset CHAR(1),PeriodYear INT)
INSERT INTO @T(Asset,PeriodYear)
SELECT 'A',2009 UNION ALL
SELECT 'A',2009 UNION ALL
SELECT 'B',2010 UNION ALL
SELECT 'C',2009 UNION ALL
SELECT 'C',2010 UNION ALL
SELECT 'C',2010;
SELECT Asset,
SUM(CASE WHEN PeriodYear=2009 THEN 1 ELSE 0 END) AS [2009],
SUM(CASE WHEN PeriodYear=2010 THEN 1 ELSE 0 END) AS [2010],
SUM(CASE WHEN PeriodYear=2010 THEN 1
WHEN PeriodYear=2009 THEN -1 ELSE 0 END) AS [Var]
FROM @T
GROUP BY Asset
ORDER BY Asset;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
carl.meads (7/13/2011)
My column names are not [2009] and [2010] my column name is PeriodYear and then I have 5000 records for example that are 2009,2010 along with an asset so for example 6 records could be:Asset PeriodYear
A 2009
A 2009
B 2010
C 2009
C 2010
C 2010
I then want the matrix to show:
Year 2009 2010
Asset
A 2 0
B 0 1
C 1 2
but having achieved this I want to beableto show a variance column, which it wont let me!
See? You did know what else to tell us. 😀
If Mark's solution doesn't work for you, wrap your existing query that produces the totals in a subquery or CTE that renders the "matrix" you described. Then use my query against the cte. Like this:
;with matrix as (your query goes here)
select asset, [2009], [2010], [2010]-[2009] as variance
from Matrix
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply