|
|
Posted by Razvan Socol on 10/08/05 09:50
> I'm [..] trying to return dates for which attendance has not been taken for students, but should have been.
First, let's try to get the dates where attendance should have been
taken. For this step, we are only considering the StudentSiteHistory
table, the student's CurrentStatus (I'm assuming 'A' for active) and
the working days from the Calendar table:
SELECT SSH.StudentID, SSH.SiteID, C.Date
FROM StudentSiteHistory SSH
INNER JOIN Student S ON S.StudentID=SSH.StudentID
INNER JOIN Calendar C
ON C.Date BETWEEN SSH.SiteStartDate AND SSH.SiteEndDate
WHERE S.CurrentStatus='A' AND C.Workday<>0
Now, let's add the SiteTerms, SiteExceptionDays and
StudentActivityHistory tables:
SELECT SSH.StudentID, SSH.SiteID, C.Date
FROM StudentSiteHistory SSH
INNER JOIN Student S ON S.StudentID=SSH.StudentID
INNER JOIN Calendar C
ON C.Date BETWEEN SSH.SiteStartDate AND SSH.SiteEndDate
WHERE S.CurrentStatus='A' AND C.Workday<>0
AND EXISTS (
SELECT * FROM SiteTerms ST
WHERE ST.SiteID=SSH.SiteID
AND C.Date BETWEEN ST.StartOfTerm AND ST.EndOfTerm
) AND NOT EXISTS (
SELECT * FROM SiteExceptionDays SED
WHERE SED.SiteID=SSH.SiteID AND SED.SchoolDayType='N'
AND SED.SchoolDayStartTime>=C.Date
AND SED.SchoolDayStartTime<C.Date+1
) AND EXISTS (
SELECT * FROM StudentActivityHistory SAH
WHERE SAH.StudentID=SSH.StudentID AND SAH.Activity_Status='A'
AND C.Date BETWEEN SAH.StatusStartDate AND SAH.StatusEndDate
)
I have used subqueries to add these conditions, mainly because they are
more intuitive. I believe I could have used INNER JOIN-s instead of
EXISTS subqueries (adding a DISTINCT keyword or a GROUP BY), but
performance would have been worse.
In the SiteExceptionDays table, I'm assuming that the
SchoolDayStartTime and SchoolDayEndTime would store the date and the
time (unlike all the other datetime columns, which store only the date,
with a time of 0:00:00). Also I'm assuming that SchoolDayStartTime and
SchoolDayEndTime are always in the same day (for each row). This should
be verified with a CHECK CONSTRAINT (and it would have been useful if
you also provided all the constraints for these tables: primary keys,
foreign keys, unique constraints, check constraints).
Now, we only need to filter the days which have no attendance. Of
course, we can use a subquery (like above), but this time let's use a
LEFT JOIN:
SELECT X.StudentID, X.SiteID, X.Date
FROM (
SELECT SSH.StudentID, SSH.SiteID, C.Date
FROM StudentSiteHistory SSH
INNER JOIN Student S ON S.StudentID=SSH.StudentID
INNER JOIN Calendar C
ON C.Date BETWEEN SSH.SiteStartDate AND SSH.SiteEndDate
WHERE S.CurrentStatus='A' AND C.Workday<>0
AND EXISTS (
SELECT * FROM SiteTerms ST
WHERE ST.SiteID=SSH.SiteID
AND C.Date BETWEEN ST.StartOfTerm AND ST.EndOfTerm
) AND NOT EXISTS (
SELECT * FROM SiteExceptionDays SED
WHERE SED.SiteID=SSH.SiteID AND SED.SchoolDayType='N'
AND SED.SchoolDayStartTime>=C.Date
AND SED.SchoolDayStartTime<C.Date+1
) AND EXISTS (
SELECT * FROM StudentActivityHistory SAH
WHERE SAH.StudentID=SSH.StudentID AND SAH.Activity_Status='A'
AND C.Date BETWEEN SAH.StatusStartDate AND SAH.StatusEndDate
)
) X LEFT JOIN (
SELECT StudentID, SiteID, AttendanceDate
FROM DailyAttendance
WHERE Attend_Status='P'
) Y
ON X.StudentID=Y.StudentID
AND X.SiteID=Y.SiteID
AND X.Date=Y.AttendanceDate
WHERE Y.StudentID IS NULL
After I wrote these queries, I also saw Hugo's response and I was
amazed (again) how similar our queries are.
Razvan
Navigation:
[Reply to this message]
|