Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matkwan
Starting Member
36 Posts |
 Posted - 2004-10-15 : 22:28:50
|
| Hi, I am developing a School Attendance System. I have a table called tblABSENCE which stores student absences Eg,========================StudentID, Period, Date========================mkwan , 1 ,1/1/2000mkwan , 2 ,2/2/2000ssoh , 3 ,2/2/2000mclapsis , 1 ,3/2/2000mclapsis , 6 ,3/2/2000Note: * It only stores the Absence records, we do not create records if they are present.* There are 6 periods in a school day.I want to create a Select statement to give me a list of students who have attended 1 or more class earlier and then left 1 or more classes later during the school day. (We consider that as an unusual event, we need to call their parents right away)For example, using the table above, it should only return me ssoh and mclapsis, not mkwan, because:* mkwan missed period 1 and 2, but then he turned up to all the remaining classes, so there is nothing unusual about that. He was just late to morning classes.* ssoh should show up, because he attended period 1, 2, 4, 5, 6. But not period 3, therefore we need to do an urgent follow up.* mclapsis should show up, because he attended period 2, 3, 4, 5. But not period 1 and 6, therefore we need to an urgent follow up.Basically, the formula is to show the students who were present in classes earlier and then absence in 1 or more classes later. Is it possible to do this ? Please HelpThanks in AdvanceMatt |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-10-16 : 00:08:25
|
| See nigel's method of detecting gaps.http://www.nigelrivett.net/FindGapsInSequence.html |
 |
|
|
|
|
|
|
|
Need Select Statement Help (Tricky One)