I am writing a query using common table expression in SQL SERVER. The Table is t
ID: 3849508 • Letter: I
Question
I am writing a query using common table expression in SQL SERVER. The Table is to get a sum and then output a color. For example if the sum is 36 the output should be 'Green'. 12 represents green, 20 represents yellow, 30 represents red. So for example if I add 12+12+12, the sum is 36 and therefore the color 'green' shout be outputted. Or 20+20+12 = 52 so that should output 'Yellow'. Below is my code so far.
WITH tempt(tempID, tempValue, Temp Result)
AS
(
SELECT ID, Value, Result,
CASE
WHEN Value = 10 THEN 12
WHEN Value = 20 THEN 20
WHEN Value = 30 THEN 30
END AS 'Signals'
FROM (Base Table)
)
SELECT ID, Signals
sum(CASE WHEN Signals = 36 THEN 'Green' END)
sum(CASE WHEN Signals = 52 THEN 'Yellow' END)
FROM (Base Table)
group by Signals, ID;
I am pretty much trying to call the Signal column and have values added seperately, not as whole, but I am struggling to do that.
Explanation / Answer
Find the query below.
WITH tempt(tempID, tempValue, Temp Result)
AS
(
SELECT ID, Value, Result,
CASE
WHEN Value = 10 THEN 12
WHEN Value = 20 THEN 20
WHEN Value = 30 THEN 30
END AS 'Signals'
FROM (Base Table)
)
SELECT ID, Signals
sum(CASE WHEN Signals = 36 THEN 'Green' END)
sum(CASE WHEN Signals = 52 THEN 'Yellow' END)
FROM (Base Table), tempt
group by Signals, ID;
Note: You have to use first sub query alias name at main query.
for ex, syntax of the with clause is :
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.