TSQL Counting number of Consecutive Absences in a row

Problem:* I'm trying to calculate the number of consecutive absence each student have for a particular class. However, the count need to be restarted if that student did attend class for one day.

e.g. If class MATH1234 has classe开发者_Python百科s on Monday and Friday and student 001234 missed Monday, Friday for Week 1 and Monday for Week 2 but attended Friday for Week 2, then missed Monday and Friday for Week 3, their consecutive absence count for that class would be:

(this is a cut down version of my Lessons table)

Class Day Week    IsAbsent    ConsecutiveAbs
MATH1234  Mon 1   1       1
MATH1234  Fri 1   1       2
MATH1234  Mon 2   1       3
MATH1234  Fri 2   0       0
MATH1234  Mon 3   1       1
MATH1234  Fri 3   1       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])

Given the table above, 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 disregard the classes where the student actually attended the class and just kept counting :(

Class Day Week    IsAbsent    ConsecutiveAbs

MATH1234  Mon 1   1       1
MATH1234  Fri 1   1       2
MATH1234  Mon 2   1       3
MATH1234  Fri 2   0       4
MATH1234  Mon 3   1       5
MATH1234  Fri 3   1       6

Any idea?

Similar to the answer to your previous question, only this time it looks for a class that has been attended, instead of just limiting the search by a week.

UPDATE allLessons
SET ConsecutiveAbs = results.ConsecutiveAbs
FROM 
    Lessons allLessons JOIN
(
    SELECT 
        LessonsAbsent.[Student ID],
        LessonsAbsent.[Class Number],
        LessonsAbsent.[Line Number],
        LessonsAbsent.[Year],
        LessonsAbsent.ClassDate,
        ISNULL(SUM(CAST(IsAbsent AS numeric)), 0) AS ConsecutiveAbs
    FROM 
        Lessons LessonsAbsent JOIN
        Lessons RunningTotalAbsent ON 
            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 include this date in the running total only if the student has not attended a class in-between the absences.
            AND NOT EXISTS (
                SELECT *
                FROM Lessons notAbsent
                WHERE 
                    LessonsAbsent.[Student ID] = notAbsent.[Student ID]
                    AND LessonsAbsent.[Class Number] = notAbsent.[Class Number]
                    AND LessonsAbsent.[Line Number] = notAbsent.[Line Number]
                    AND LessonsAbsent.[Year] = notAbsent.[Year]
                    AND notAbsent.IsAbsent = 0
                    AND notAbsent.ClassDate <= LessonsAbsent.ClassDate
                HAVING MAX(ClassDate) > RunningTotalAbsent.ClassDate
        )
    WHERE LessonsAbsent.IsAbsent = 1   
    GROUP BY
        LessonsAbsent.[Student ID],
        LessonsAbsent.[Class Number],
        LessonsAbsent.[Line Number],
        LessonsAbsent.[Year],
        LessonsAbsent.ClassDate
) results ON
    results.[Student ID] = allLessons.[Student ID]
    AND results.[Class Number] = allLessons.[Class Number]
    AND results.[Line Number] = allLessons.[Line Number]
    AND results.[Year] = allLessons.[Year]
    AND results.ClassDate = allLessons.ClassDate

This may not be useful depending on your scenario but it may help you get to the solution

select * 
into #orderedlessons
from Lessons
order by [Student ID], [Class Number], [Line Number], [Year], [ClassDate]

declare @tot int
set @tot=0
update #orderedlessons
set @tot = ConsecutiveAbs = Case when IsAbsent=0 then 0 else @tot+1 END;

update lessons
set lessons.ConsecutiveAbs = ordered.ConsecutiveAbs
from lessons inner join  #orderedlessons ordered on
lessons.[Student ID] = ordered.[Student ID]
and lessons.[Class Number] = ordered.[Class Number]
and lessons.[Line Number] = ordered.[Line Number]
and lessons.[Year] = ordered.[Year]
and lessons.ClassDate = ordered.[ClassDate]

drop table #orderedlessons

Something like this?

update L2
set L2.ConsecutiveAbs = 
        case 
        when L2.IsAbsent = 0 then 0
        else (  select  TOP 1 L1.ConsecutiveAbs 
                from    Lessons L1
                where   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]
                ORDER BY L1.ClassDate desc
              )
        end as ConsecutiveAbs
from    Lessons L2

Edit: Add your update around.

Edit2: Added the update

Not sure if you are looking to stick to the subquery idea. This problem type -IMO -is better solved (quicker, less complex) using cursors. Here is what the SQL would look like if you choose to go that route.

So didn't let me to paste the code in the answer. So I've pasted the code snippet here. http://pastebin.com/ybesdX2G

Here's a reference to the article showing you how to do cursors. http://msdn.microsoft.com/en-us/library/ms180169.aspx

Edit: The cursor approach works on one row at a time and remembers whether the last row was absent/present. So sorting the data correctly is important.

Notice that there is no ORDER BY in the code snippet. I tested the code snippet against data sample you had provided in the question. Your data was pre-sorted. So, it worked like a charm.

In your database if your data isn't stored in a pre-sorted fashion(which I doubt it is) you will want to add an ORDER BY to line #8, to sort the data into the sequence.

Hope this helps.