I was wondering if anyone could help me with a very big problem
I have attached a spreadsheet which shows how a student record is stored in my database.
Currently I run a sp which populates a table to show all the courses that a student is on for each week of the academic year. This table is called all students. I want to query the table all students and report back all the students who have missed five consec days of classes (5 consec days is when the class took place eg could be thurs, fri, mon, tues, thursday). A student can have more than one class per day.
A student is absent by the code 'O'. A student can only be included in the 5 consec days off is the absent_code = '0'. If a register is not marked then the field will be null this cannot contribute to the five consec days absent. If a student is off 3 consec days then one the fourth day the register hasnt been marked then the program needs to forget this info, look for the next absent code = '0' and start again.
NOTE: students can have more than one class per day so when checking the first day of absence we need to check that its the first class of the day that they are absent and when checking the 5th day of absence we need to check that it is the last class of the day that they are also absent
I have attached a spreadsheet to explain further
In spreadsheet in row 7 this student is absent but because the register has not been marked for her other two classes that day we cannot assume that the student was absent this day, we then go onto line 8. Student is absent and its her first class that day so we can store this info we then check all the rest of her absences which just happens to be 5 full consec days so we then store all this information which is then outputted to a crystal report. I would like to show in this crystal report all the modules (classes) that this student has missed and how many consec days they were absent and when was the date of the first full day of absence
I would to do this in a sp as there are nearly 2 million records. I know what I am asking is difficult and if I could assign more points I would. I am sure there must be an expert out there that can do this please as I am at my wits end
Thanks in advance
I am using sql server 2005