July 29, 2009 at 6:09 am
Hi,
I'm trying to find an efficient solution to total some values at a certain period.
Using the sample below:
Is it possible to get the reults from Approach 2 using a CASE as Approach1 ?
Is there a better way that I have not thought of perhaps?
All help is appreciated.
CREATE TABLE #tempValue
(ValueID int,
YearValue int,
Amount decimal (18,2),
Manager varchar(50)
)
INSERT INTO #tempValue
SELECT1, 2000, 100, 'Peter'
UNION ALL
SELECT 2, 2000, 200, 'Peter'
UNION ALL
SELECT 3, 2009, 500, 'Peter'
UNION ALL
SELECT 4, 2009, 500, 'Peter'
--------------------------------------------------------------------
--Approach1: -> Undesired Result:
--------------------------------------------------------------------
SELECT
Manager,
CASE WHEN YearValue = 2000 THEN SUM(Amount) ELSE 0 END AS '2000',
CASE WHEN YearValue = 2009 THEN SUM(Amount) ELSE 0 END AS '2009'
FROM #tempValue
GROUP BY Manager, YearValue
--------------------------------------------------------
--Approach2: ->Preferred Result:
--------------------------------------------------------
SELECT
#tempValue.Manager,
SUM(v1.Amount) as '2000',
SUM(v2.Amount) as '2009'
FROM
#tempValue
LEFT JOIN #tempValue as v1
ON #tempValue.ValueID = v1.ValueID
AND v1.YearValue =2000
LEFT JOIN #tempValue as v2
ON #tempValue.ValueID = v2.ValueID
AND v2.YearValue =2009
GROUP BY #tempValue.Manager
DROP TABLE #tempValue
July 29, 2009 at 9:48 am
The last query returns the results you want, I think. I added some more data to the sample and you should notice that your second solution that provides your "desired" results now return incorrect results. It doubles each manager's data.
CREATE TABLE #tempValue
(
ValueID int,
YearValue int,
Amount decimal(18, 2),
Manager varchar(50)
)
INSERT INTO
#tempValue
SELECT
1,
2000,
100,
'Peter'
UNION ALL
SELECT
2,
2000,
200,
'Peter'
UNION ALL
SELECT
3,
2009,
500,
'Peter'
UNION ALL
SELECT
4,
2009,
500,
'Peter'
UNION ALL
SELECT
1,
2000,
100,
'John'
UNION ALL
SELECT
2,
2000,
200,
'Steve'
UNION ALL
SELECT
3,
2009,
500,
'John'
UNION ALL
SELECT
4,
2009,
500,
'Steve'
--------------------------------------------------------------------
--Approach1: -> Undesired Result:
--------------------------------------------------------------------
SELECT
Manager,
CASE WHEN YearValue = 2000 THEN SUM(Amount)
ELSE 0
END AS '2000',
CASE WHEN YearValue = 2009 THEN SUM(Amount)
ELSE 0
END AS '2009'
FROM
#tempValue
GROUP BY
Manager,
YearValue
--------------------------------------------------------
--Approach2: ->Preferred Result:
--------------------------------------------------------
SELECT
#tempValue.Manager,
SUM(v1.Amount) as '2000',
SUM(v2.Amount) as '2009'
FROM
#tempValue LEFT JOIN
#tempValue as v1
ON #tempValue.ValueID = v1.ValueID AND
v1.YearValue = 2000 LEFT JOIN
#tempValue as v2
ON #tempValue.ValueID = v2.ValueID AND
v2.YearValue = 2009
GROUP BY
#tempValue.Manager
--------------------------------------------------------
-- New Approach: ->Preferred Result:
--------------------------------------------------------
SELECT
Manager,
SUM(CASE WHEN YearValue = 2000 THEN Amount
ELSE 0
END) AS '2000',
SUM(CASE WHEN YearValue = 2009 THEN Amount
ELSE 0
END) AS '2009'
FROM
#tempValue
GROUP BY
Manager
DROP TABLE #tempValue
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 29, 2009 at 1:23 pm
Thanks Jack, it works for me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply