Posted by Scott Marquardt on 10/01/58 11:28

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.


CREATE TABLE Student (
StudentID int IDENTITY(1,1) NOT NULL,
CurrentStatus varchar(2) NOT NULL)

CREATE TABLE Calendar (
Dateid int NOT NULL,
Date datetime NULL,
Workday bit NULL )

CREATE TABLE DailyAttendance (
StudentID int NOT NULL,
AttendanceDate datetime NOT NULL,
SiteID varchar(6) NOT NULL,
Attend_Status varchar(2) NOT NULL)

(the last field is, e.g., present or absent)

CREATE TABLE StudentActivityHistory (
StudentID int NOT NULL,
StatusStartDate datetime NOT NULL,
StatusEndDate datetime NULL,
Activity_Status varchar(2) NULL,
StudentStatusHistoryID int IDENTITY(1,1) NOT NULL)

(the activity_status is either A or I; the important records in this table
are the 'A' records. A student's most recent status record always has an
end date of '12/31/9999 12:00:00 AM', whether that's an A or I record. No
dates not between start/end dates of students' A records would need
attendance taken. students may have many periods of activity -- A records
-- as well as many inactive periods.)

CREATE TABLE SiteTerms (
SiteID varchar(6) NOT NULL,
Term varchar(3) NOT NULL,
StartOfTerm datetime NOT NULL,
Quarter varchar(2) NOT NULL,
SchoolYear varchar(9) NOT NULL,
EndOfTerm datetime NOT NULL)

(different schools vary their term start and end dates. No dates not
between term start and end dates would need attendance taken by students
assigned to and active in that school during that period.)

CREATE TABLE SiteExceptionDays (
SiteID varchar(6) NOT NULL,
SchoolDayStartTime datetime NOT NULL,
SchoolDayEndTime datetime NOT NULL,
SchoolDayType varchar(2) NOT NULL)

(there are two kinds of days -- partial attendance, and no attendance. In
short, if the type of day is "N" no attendance needs to be taken for
students assigned to that school and active on that day)

CREATE TABLE StudentSiteHistory (
StudentID int NOT NULL,
SiteStartDate datetime NOT NULL,
SiteID varchar(6) NOT NULL,
SiteEndDate datetime NULL,
StudentSiteHistoryID int IDENTITY(1,1) NOT NULL)

(Pain. The attendance table tells which site a student was assigned when
attendance was taken. To find which school a student was a assigned to on
days attendance was NOT taken, this table's implicated 'cause it's the only
way of connecting everything else together)

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? I'd just as soon get some tips on how to approach
this kind of thing, and figure it out myself with some guidance.

Any takers? Gotta run, dang I'm late for something.

TIA

--

Scott

[Back to original message]