TSQL计算连续缺勤的次数

Question

问题：* 我正在尝试计算每个学生在特定课程中连续缺勤的次数。但是，如果该学生确实上课了一天，则需要重新开始计数。

例如，如果班级MATH1234在周一和周五上课，学生001234错过了第一周的周一、周五和第二周的周一，但参加了第二周的周五，然后错过了周一和周五第三周的星期五，他们该课程的连续缺席计数为：（

这是我的课程表的缩减版本）

上课日周缺席连续 Abs
MATH1234 星期一 1 1 1
MATH1234 星期五 1 1 2
MATH1234 周一 2 1 3
MATH1234 星期五 2 0 0
MATH1234 周一 3 1 1
MATH1234 周五 3 1 2

我有一个名为课程的表，其中包含所有学生及其注册的课程以及他们是否缺席任何课程的运行列表：

课程([学生 ID ]、[班级编号]、[行号]、[学期间]、[年份]、[学期]、[周]、[日期间]、[班级日期]、[缺席]、[原因代码]、[连续Abs]）

鉴于上表，我目前正在做的是更新 Lessons 表，更改 ConsecutiveAbs 的值，如下所示：

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

但这忽略了学生实际参加的课程，只是继续计数:(

上课日周缺席连续 Abs

MATH1234 星期一 1 1 1
MATH1234 星期五 1 1 2
MATH1234 周一 2 1 3
MATH1234 星期五 2 0 4
MATH1234 周一 3 1 5
MATH1234 周五 3 1 6

有什么想法吗？

Answer 1

与您上一个问题的答案类似，只是这一次它会查找已参加过的课程，而不仅仅是将搜索限制为一周。

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

Answer 2

根据您的情况，这可能没有用，但它可能会帮助您找到解决方案

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

Answer 3

像这样的东西吗？

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

编辑：
添加您的更新。

编辑2：添加了更新

Answer 4

不确定您是否想坚持子查询的想法。这种问题类型 -IMO - 使用游标可以更好地解决（更快，更简单）。如果您选择走这条路线，那么 SQL 将如下所示。

所以不允许我将代码粘贴到答案中。所以我把代码片段粘贴在这里。 http://pastebin.com/ybesdX2G

这是向您展示如何使用光标的文章的参考。 http://msdn.microsoft.com/en-us/library/ms180169.aspx编辑

：
游标方法一次只处理一行，并记住最后一行是否缺失/存在。因此正确排序数据很重要。

请注意，代码片段中没有 ORDER BY。我根据您在问题中提供的数据示例测试了代码片段。您的数据已预先排序。所以，它就像一个魅力。

在你的数据库中，如果你的数据没有以预先排序的方式存储（我怀疑是这样），你将需要在第 8 行添加一个 ORDER BY，以将数据排序到序列中。

希望这有帮助。