TSQL Counting number of Consecutive Absence in a row within time period

Problem: I'm trying to calculate the number of consecutive absence each student have for a particular class within a week period.

e.g. If class MATH1234 has classes on Day 1 Period 4 and Day 4 Period 3, and student 0012345 was absent for Day 1 Period 4 and Day 4 Period 3 in Week 1, it is the same as if that student was absent for Day 4 Period 3 in Week 1 and Day 1 Period 4 in Week 2.

I have a table called Lessons that contain a running list of all the students and the classes they are enrolled in and whether they were absent for any classes:

Lessons([Student ID], [Class Number], [Line Number], [Academic Period], [Year], [Term], [Week], [Day Period], [ClassDate], [IsAbsent], [ReasonCode], [ConsecutiveAbs])

I need to calculate the number of Consecutive Absence a student have for each class that they are enrolled in where the consecutive abs are within a period of one week (see explanation above).

Given that:

Student ID    Class Number    Line Number Academic Period Year    Term    Week    Day Period  ClassDate               IsAbsent    ReasonCode  ConsecutiveAbs
001234        1CVASX11        1           1               2011    1       3       1       2011-02-14 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1       4       1       2011-02-21 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1  开发者_高级运维     4       2       2011-02-23 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1       5       1       2011-02-28 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1       5       2       2011-03-02 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1       6       1       2011-03-07 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1       6       2       2011-03-09 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1       7       2       2011-03-16 00:00:00.000     1           U           0
001234        1CVASX11        1           1               2011    1       9       1       2011-03-28 00:00:00.000     1           U           0
001234        1CVASX61        6           1               2011    1       9       2       2011-03-28 00:00:00.000     1           U           0

The ConsecutiveAbs for student 001234 for Class Number 1CVASX11 on ClassDate 28/3 would be 1 as that previous absence to that date was the 16/3 which is more than a week ago. Likewise, the ConsecutiveAbs on ClassDate 9/3 would be 2 as that student was also absent on the 7/3 which is within the time period of one week.

What I am currently doing is Updating the Lessons table change the value of ConsecutiveAbs like this:

UPDATE Lessons
SET ConsecutiveAbs = 
(SELECT ISNULL(SUM(CAST(IsAbsent AS numeric)), 0)
 FROM Lessons AS L3
 WHERE L3.IsAbsent = 1
 AND L1.IsAbsent <> 0
 AND L3.[Student ID] = L1.[Student ID]
 AND L3.[Class Number] = L1.[Class Number]
 AND L3.[Line Number] = L1.[Line Number]
 AND L3.[Year] = L1.[Year]
 AND L3.[ClassDate] <= L1.[ClassDate]
 AND (L3.[ClassDate] > (SELECT MAX(L2.ClassDate)
      FROM Lessons AS L2
      WHERE L2.IsAbsent = 0
      AND L2.[Student ID] = L1.[Student ID]
      AND L2.[Class Number] = L1.[Class Number]
      AND L2.[Line Number] = L1.[Line Number]
      AND L2.[Year] = L1.[Year]
      AND L2.ClassDate < L1.[ClassDate]
 ) OR (SELECT MAX(L2.ClassDate)
       FROM Lessons AS L2
       WHERE L2.IsAbsent = 0
       AND L2.[Student ID] = L1.[Student ID]
       AND L2.[Class Number] = L1.[Class Number]
       AND L2.[Line Number] = L1.[Line Number]
       AND L2.[Year] = L1.[Year]
       AND L2.ClassDate < L1.[ClassDate]
 ) IS NULL))
 FROM Lessons AS L1

But that give me this:

001234  1CVASX11    1   1   2011    1   3   1   2011-02-14 00:00:00.000 1   U   1
001234  1CVASX11    1   1   2011    1   4   1   2011-02-21 00:00:00.000 1   U   2
001234  1CVASX11    1   1   2011    1   4   2   2011-02-23 00:00:00.000 1   U   3
001234  1CVASX11    1   1   2011    1   5   1   2011-02-28 00:00:00.000 1   U   4
001234  1CVASX11    1   1   2011    1   5   2   2011-03-02 00:00:00.000 1   U   5
001234  1CVASX11    1   1   2011    1   6   1   2011-03-07 00:00:00.000 1   U   6
001234  1CVASX11    1   1   2011    1   6   2   2011-03-09 00:00:00.000 1   U   7
001234  1CVASX11    1   1   2011    1   7   2   2011-03-16 00:00:00.000 1   U   8
001234  1CVASX11    1   1   2011    1   9   1   2011-03-28 00:00:00.000 1   U   9
001234  1CVASX61    6   1   2011    1   9   2   2011-03-28 00:00:00.000 1   U   9

I need to set a time period in there so it only add up within a week of the ClassDate. Anyone have any idea?

I think the one piece you're missing is limiting the count to only include an absence is if it is under 7 days of the original absence.

The following query includes that criteria, and I believe it gives they results you're looking for:

UPDATE LessonsAbsent
SET ConsecutiveAbs = (
    SELECT 
        ISNULL(SUM(CAST(IsAbsent AS numeric)), 0)
    FROM 
        Lessons RunningTotalAbsent 
    WHERE
        RunningTotalAbsent.IsAbsent = 1
        AND LessonsAbsent.[Student ID] = RunningTotalAbsent.[Student ID]
        AND LessonsAbsent.[Class Number] = RunningTotalAbsent.[Class Number]
        AND LessonsAbsent.[Line Number] = RunningTotalAbsent.[Line Number]
        AND LessonsAbsent.[Year] = RunningTotalAbsent.[Year]
        AND LessonsAbsent.ClassDate >= RunningTotalAbsent.ClassDate

        -- Only count as consecutive if the absence happened within under 7 days.
        AND DATEDIFF(DAY, RunningTotalAbsent.ClassDate, LessonsAbsent.ClassDate) < 7
    )
FROM Lessons LessonsAbsent 
WHERE LessonsAbsent.IsAbsent = 1