SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with CASE statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kcarbone1970
Starting Member

USA
31 Posts

Posted - 02/28/2014 :  18:29:40  Show Profile  Reply with Quote
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)

Singapore
17645 Posts

Posted - 02/28/2014 :  23:10:41  Show Profile  Reply with Quote
what is the error message ?


KH
Time is always against us

Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 02/28/2014 :  23:39:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000