Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with CASE statement

Author  Topic 

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2014-02-28 : 18:29:40
Hello all I am trying to add a case statement to an existing query that contains a CTE, error is with field "Quadrant" ...probably syntax issues..thanks in advance!
 )
SELECT NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF, AG_C_or_Better,Affliation, CAHSEE,
CollegeTesting, AG_Schedule, Credits,

(CASE WHEN AG_C_or_Better = 'Yes' THEN 1 ELSE 0 END) +
(CASE WHEN CAHSEE = 'Yes' THEN 1 ELSE 0 END) +
(CASE WHEN Affliation = 'Yes' THEN 1 ELSE 0 END) +
(CASE WHEN CollegeTesting = 'Yes' THEN 1 ELSE 0 END) +
(CASE WHEN AG_Schedule = 'Yes' THEN 1 ELSE 0 END) +
(CASE WHEN Credits = 'Yes' THEN 1 ELSE 0 END)
AS Total,

CASE WHEN Total = 1 THEN 'Intensive'
WHEN Total = 2 THEN 'Strategic'
WHEN Total = 3 THEN 'Challenge' END AS Quadrant



FROM cteSource




GROUP BY NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF, AG_C_or_Better,Affliation, CAHSEE,
CollegeTesting, AG_Schedule, Credits


Cartesian Yak

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-28 : 23:10:41
what is the error message ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2014-02-28 : 23:39:25
I figured it out!! Thanks anyway

SELECT  NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF, AG_C_or_Better,Affliation, CAHSEE, 
CollegeTesting, AG_Schedule, Credits,
CASE WHEN Total IN ( 1, 2 ) THEN 'Intensive'
WHEN Total IN ( 3, 4 ) THEN 'Strategic'
WHEN Total IN ( 5, 6 ) THEN 'Challenge'
END AS Quadrant
FROM ( SELECT ( CASE WHEN AG_C_or_Better = 'Yes' THEN 1
ELSE 0
END ) + ( CASE WHEN CAHSEE = 'Yes' THEN 1
ELSE 0
END )
+ ( CASE WHEN Affliation = 'Yes' THEN 1
ELSE 0
END ) + ( CASE WHEN CollegeTesting = 'Yes' THEN 1
ELSE 0
END )
+ ( CASE WHEN AG_Schedule = 'Yes' THEN 1
ELSE 0
END ) + ( CASE WHEN Credits = 'Yes' THEN 1
ELSE 0
END ) AS Total,
NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF, AG_C_or_Better,Affliation, CAHSEE,
CollegeTesting, AG_Schedule, Credits
FROM cteSource
GROUP BY NAME ,
ID ,
GR ,
SC ,
Ethnicity ,
Gate ,
SpecialED ,
LF ,
AG_C_or_Better ,
Affliation ,
CAHSEE ,
CollegeTesting ,
AG_Schedule ,
Credits
) AS d


Cartesian Yak
Go to Top of Page
   

- Advertisement -