DECLARE @SC INT = 30, @GR INT = 9;
 
   SELECT  
s.FN + ' ' + LN AS NAME ,
s.ID ,
        s.GR ,
        s.SC ,
        
        ( CASE WHEN s.RC1 = 700
                    AND ETH = 'Y' THEN 'Hispanic'
               WHEN s.RC1 = 600
                    AND ETH = 'N' THEN 'African American'
               ELSE 'Other'
          END ) AS Ethnicity ,
        ( CASE WHEN s.U7 = 'G' THEN 'Yes'
               ELSE 'No'
          END ) AS Gate ,
        ( CASE WHEN s.U9 IN ( 'R', 'D', 'S' ) THEN 'Yes'
               ELSE 'No'
          END ) AS SpecialEd ,
        ( CASE WHEN s.LF = '1' THEN 'English Only'
               WHEN s.LF = '2' THEN 'Initially Fluent English Proficient'
               WHEN s.LF = '3' THEN 'English Learner'
               WHEN s.LF = '4' THEN 'Redesignated Fluent English Proficient'
               WHEN s.LF = '5' THEN 'TBD'
          END ) AS LF ,
          
        CASE WHEN  SUM(a.PID)  >= 2  
                  AND SUM(CASE WHEN a.DS = 'SUS'  THEN 1
                               ELSE 0
                          END) >= 1 THEN 'No'
             ELSE 'Yes'
        END AS [On Target?]
FROM    dbo.STU AS s 
        LEFT JOIN dbo.ADS AS a  ON a.PID = s.ID
WHERE  (a.DT >= '2013-09-01') AND
         s.GR = @GR
        AND s.SC = @SC
GROUP BY s.ID ,
        s.GR ,
        s.SC ,
        s.FN ,
        s.LN ,
        RC1 ,
        s.ETH ,
        s.U7 ,
        s.U9 ,
        s.LF