How do I...?
-
I need to determine how many days a student attended class in District schools during a given semester. The student may leave the District and then return. I need only days attended in our District.
I have a calendar table (TABLE A) that stores days of the week. This table indicates whether a given date is a weekend or a holiday (these are not counted). I have another table (TABLE B) that lists all the schools in the District. There is another table that stores absence dates (TABLE C). Another table has semester start / end dates (TABLE D). There is yet another table (TABLE E) that stores which site (District and Non-District) the student was at for a given period of time. (Start and End dates)
How would I calculate the number of days attended for only District sites during a given semester?
Note: a student is presumed to be present on a given date unless an absence is entered. Days present are not stored.
Thanks.
-
This sounds like a homework problem.
What have you tried?
Eddie Wuerch
MCM: SQL -
One would need to know how 'site' relates to 'school'. The DDL would help. A hint: you might want to try using a left (outer) join to exclude certain records.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
A student may be at a site and not in school. He may also be at a site and in school, but not a District school.
Haven't tried anything yet - was thinking about a cursor, but not sure how to form it.
-
Whenever you think about a cursor, slap yourself and tell yourself there must be a better way.
Post your table structure, some sample data and the result you want for that sample data, and you'll get the answer you're looking for in no time...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Don't use a cursor. The tables you mention don't provide enough information to achieve the result you want. Analysis:
- calendar:
- date,
- isweekend,
- isholiday
- semester:
- (schoolid?)
- start,
- end
- school:
- schoolid
these three are enough to relate each to a list of school attendance dates.
But you also need to relate students to this list of dates, presumably by relating student to school via site? This can't be achieved using the tables and columns you have described.
Once you have a list of dates showing when each student was expected to attend, you need to eliminate the dates they were absent. That's where the outer join comes in.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Say, for example, Student A was enrolled in a District school on Jan. 3. He attended that school until 2/18, at which time he was sent to Site B (a non-District site), He returned to a District school on 3/15 and stayed until 5/21.
The semester runs from 1/3 - 5/21. There were no absences. We need to check for holidays.
What I need to do is EXCLUDE the time from 2/18 - 3/14 and count all other days.
-
Your explanation makes school and site sound like effectively the same thing, but earlier you said they weren't. Post the DDL for your tables.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Post your table structure, some sample data and the result you want for that sample data. Here's an example (for a different question)...
--I want to get the product of 2 numbers in my table
--table structure
declare @mytable table (a int, b int)
--sample data
insert @mytable
select 1, 2
union all select 3, 4
union all select 5, 6
union all select 7, 8
--required results
a b product
-- -- -------
1 2 2
3 4 12
5 6 30
7 8 56
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
declare @StudentLocation (StudID int, SiteCode int, InDate datetime, OutDate datetime)
insert @StudentLocation
select 12345,1, '01/03/06','02/14/06'
union all select 12345,2,'02/15/06','03/14/06'
union all select 12345,5,'03/15/06', '05/21/06'
declare @DistrictSchool (SchoolID, SchoolName,SiteCode)
insert @DistrictSchool
select 111, 'School A', 1
union all select 112, 'School B' 5
declare @Calendar (CalDate datetime, IsWeekday, IsHoliday)
insert @Calendar
select '1/1/06', 0, 0
union all select '1/2/06', 1, 1
union all select '1/3/06', 1, 0
...
union all select '5/21/06', 0,0
declare @Semester (SemID int, StartDate datetime, EndDate datetime)
insert @Semester
select 1, '1/3/06', '5/22/06'
union all select 2, '5/25/'06', '12/20/06'
Assuming the student was present every available day, I need to know how many available days there were when the student was in a District school (SiteCode 1 and 5 in this example). Holidays and weekends must be excuded. Any absences can be deducted later.
-
Go on, you're nearly there. Looks like you don't need the school table if you know the sitecodes. So join the other three, so that the calander date is within the date ranges in the other two tables, then you're ready for your outer join to absences...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
But I need the School table to tell me if the site at which a student is located is a valid school site or not. Remember, the location of the student may not be a valid school.
-
If a site can have more than one school, you either don't have enough info to join your tables properly, or haven't specified some rules that apply to the data.
If, on the other hand, (valid) school is (0,1)
0,1) with site, you can just join that on to the query, using siteid. You use the restrictive inner join to limit records to those with a valid school. You will then only report 'scheduled attendance', let's call it, for valid schools. Once you have the joins in placce to make a recordset containing one row for each scheduled student-day, you can outer join to the absences so you can report scheduled v actual attendence.Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
A site may not have a school or it may be a non-District school. The sitecode in the LocationTable must be compared to the sitecode in the Schools table to see if it is valid District school.
Thanks for your help.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply