Hello I have this query that should produce about 600 records (for each student) and it works fine until I add an additional filter in the where clause by which reduces the number of records to about half. I need the condition be applied to display all the records. Any suggestions greatly appreciated. The additional filter is (a.DT >= '2013-09-01')
DECLARE @SC INT = 30, @GR INT = 9;
s.FN + ' ' + LN AS NAME ,
( CASE WHEN s.RC1 = 700
AND ETH = 'Y' THEN 'Hispanic'
WHEN s.RC1 = 600
AND ETH = 'N' THEN 'African American'
END ) AS Ethnicity ,
( CASE WHEN s.U7 = 'G' THEN 'Yes'
END ) AS Gate ,
( CASE WHEN s.U9 IN ( 'R', 'D', 'S' ) THEN 'Yes'
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
END) >= 1 THEN 'No'
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 ,