Help with CASE statement - SQL Server Forums

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources