March 9, 2004 at 2:25 pm
I have a problem that has been driving me nuts which you SQL guru's will no doubt say is simple, so some pointers would be very much appreciated.
I have an SP that reports all the classes in a given year and the number of pupils in each class. The pupil table also has a column that records their leaving date that will be Null or a valid date.
The code below works fine however the LeaverCount counts all records with a non null value what I want it to do is count only those pupils with a leaving date that falls between two specified dates.
Can anyone point me in the right direction please?
Glenn
Code Fragment:
SELECT PUPIL_CLASS.ClassID, CLASS.ClassName, COUNT(PUPIL_DETAILS.PupilID) AS 'PupilCount',
COUNT(PUPIL_DETAILS.LeaveDate) AS 'LeaverCount'
FROM PUPIL_DETAILS INNER JOIN PUPIL_CLASS INNER JOIN CLASS ON CLASS.ClassID = PUPIL_CLASS.ClassID ON
PUPIL_DETAILS.PupilID = PUPIL_CLASS.PupilID
WHERE (PUPIL_CLASS.ClassID IN (SELECT ClassID FROM CLASS WHERE YEAR_ID = 8))
GROUP BY PUPIL_CLASS.ClassID, Class.ClassName
March 9, 2004 at 3:01 pm
You could use the SUM() aggregate function along with a CASE expression:
SUM(CASE WHEN Pupil_Details.LeaveDate BETWEEN @StartDate AND @EndDate THEN 1 END) AS 'LeaverCount'
--Jonathan
March 9, 2004 at 3:21 pm
That did it!
Many thanks Jonathan
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply