|
|
Posted by Hugo Kornelis on 10/08/05 01:50
On Wed, 5 Oct 2005 17:40:58 -0500, Scott Marquardt wrote:
>My SQL acumen stems from just a couple courses, and everything since from
>the trenches. Fun + angst over time.
>
>I'm needing some advice on joins. Though I understand the basics, I'm
>having problems abstracting from instances where it's easy to think about
>discrete key values (. . . and studentid = 1234) to entire sets of users,
>with the joins doing their work.
>
>For example, currently I'm going nuts trying to return dates for which
>attendance has not been taken for students, but should have been. Students
>have active and inactive periods of enrollment in our schools, so we have a
>history table of when they were active and inactive -- as well as two more
>tables that layer other bounds on eligible dates (what range of dates fall
>within a given school's term? What of holidays and staff institute days?).
>I also have a populated calendar table, and a table where students are
>identified. Finally, there's a site history table which is a REAL pain in
>the butt for me to think about.
(snip CREATE TABLE statements)
>Dangitall, I know this can be done but I've beat my head against the wall.
>Due diligence has gotten me a headache and a hankerin' for whiskey, and I'm
>not much of a drinker. Is there anyone in the group for whom this kind of
>thing is a no-brainer?
Hi Scott,
Are you sure that the headache is not caused by the whiskey? Or by
banging your head against the wall? (Next time, leave more room between
you and the wall when you're head-banging. Or switch from heavy metal to
something more placid - Mozart, perhaps?)
Anyway, I don't think this is a no-brainer for anyone. The number of
tables involved and the way they are related make this a tough one. And
the absence of constraints in the DDL, of INSERT statements with sample
data and expected output didn't help either (hint, hint).
Here's my attempt. It's untested. If it doesn't work as expected, then
please read www.aspfaq.com/5006 and follow the instructions carefully.
SELECT s.StudentID, c.Date
FROM Student AS s
INNER JOIN StudentActivityHistory AS sah
ON sah.StudentID = s.StudentID
AND sah.Activity_Status = 'A'
INNER JOIN Calendar AS c
ON c.Date BETWEEN sah.StatusStartDate AND sah.StatusEndDate
INNER JOIN StudentSiteHistory AS ssh
ON ssh.StudentID = s.StudentID
AND c.Date BETWEEN ssh.SiteStartDate AND ssh.SiteEndDate
INNER JOIN SiteTerms AS st
ON st.SiteID = ssh.SiteID
AND c.Date BETWEEN st.StartOfTerm AND st.EndOfTerm
WHERE NOT EXISTS
(SELECT *
FROM DailyAttendance AS da
WHERE da.StudentID = s.StudentID
AND da.AttendanceDate = c.Date)
AND NOT EXISTS
(SELECT *
FROM SiteExceptionDays AS sed
WHERE sed.SiteID = ssh.SiteID
AND sed.SchoolDayStartTime >= c.Date
AND sed.SchoolDayStartTime < DATEADD(day, 1, c.Date)
AND sed.SchoolDayType = 'N')
(Note: You can also convert the NOT EXISTS subqueries to LEFT OUTER
JOINS. Use this generic approach:
FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE b.x = a.x)
is equivalent to
FROM a LEFT OUTER JOIN b ON b.x = a.x WHERE b.x IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|