TSQL统计时间段内连续缺勤次数

本文介绍了TSQL统计时间段内连续缺勤次数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

问题:我正在尝试计算每个学生在一周内某个特定班级的连续缺勤次数.

例如如果课程 MATH1234 在 Day 1 Period 4 和 Day 4 Period 3 有课程，而学生 0012345 缺席了 第 1 天第 4 期和第 4 天第 3 期 在第 1 周，就好像该学生缺席了 第 4 天第 3 期em>第 1 周和第 1 天第 4 期第 2 周.

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

课时([学号]、[班号]、[行号]、[学年]、[年]、[学期]、[周]、[日期间]、[ClassDate], [IsAbsent], [ReasonCode], [ConsecutiveAbs])

我需要计算学生在一周内连续缺勤的每个班级的连续缺勤次数(参见上面的解释).

鉴于:

学生 ID 班级编号 行号 学年 年 学期 周 日 期间 ClassDate IsAbsent ReasonCode ConsecutiveAbs001234 1CVASX11 1 1 2011 1 3 1 2011-02-14 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 4 1 2011-02-21 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 4 2 2011-02-23 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 5 1 2011-02-28 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 5 2 2011-03-02 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 6 1 2011-03-07 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 6 2 2011-03-09 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 7 2 2011-03-16 00:00:00.000 1 U 0001234 1CVASX11 1 1 2011 1 9 1 2011-03-28 00:00:00.000 1 U 0001234 1CVASX61 6 1 2011 1 9 2 2011-03-28 00:00:00.000 1 U 0

在 ClassDate 28/3 的班级编号 1CVASX11 的学生 001234 的连续 Abs 将为 1，因为该日期之前的缺勤是一周多前的 16/3.同样，ClassDate 9/3 上的 ConsecutiveAbs 将为 2，因为该学生也在一周内的 7/3 缺勤.

我目前正在做的是更新课程表，像这样改变 ConsecutiveAbs 的值:

更新课程SET 连续绝对值 =(SELECT ISNULL(SUM(CAST(IsAbsent AS numeric)), 0)来自 L3 的课程L3.IsAbsent = 1AND L1.IsAbsent <>0AND L3.[学生证] = L1.[学生证]AND L3.[班级编号] = L1.[班级编号]AND L3.[行号] = L1.[行号]AND L3.[年] = L1.[年]AND L3.[ClassDate] <= L1.[ClassDate]AND (L3.[ClassDate] > (SELECT MAX(L2.ClassDate)来自 L2 课程L2.IsAbsent = 0AND L2.[学生证] = L1.[学生证]AND L2.[班级编号] = L1.[班级编号]AND L2.[行号] = L1.[行号]AND L2.[年份] = L1.[年份]AND L2.ClassDate <L1.[上课日期]) OR (SELECT MAX(L2.ClassDate)来自 L2 课程L2.IsAbsent = 0AND L2.[学生证] = L1.[学生证]AND L2.[班级编号] = L1.[班级编号]AND L2.[行号] = L1.[行号]AND L2.[年份] = L1.[年份]AND L2.ClassDate <L1.[上课日期]) 一片空白))来自 L1 的课程

但这给了我这个:

001234 1CVASX11 1 1 2011 1 3 1 2011-02-14 00:00:00.000 1 U 1001234 1CVASX11 1 1 2011 1 4 1 2011-02-21 00:00:00.000 1 U 2001234 1CVASX11 1 1 2011 1 4 2 2011-02-23 00:00:00.000 1 U 3001234 1CVASX11 1 1 2011 1 5 1 2011-02-28 00:00:00.000 1 U 4001234 1CVASX11 1 1 2011 1 5 2 2011-03-02 00:00:00.000 1 U 5001234 1CVASX11 1 1 2011 1 6 1 2011-03-07 00:00:00.000 1 U 6001234 1CVASX11 1 1 2011 1 6 2 2011-03-09 00:00:00.000 1 U 7001234 1CVASX11 1 1 2011 1 7 2 2011-03-16 00:00:00.000 1 U 8001234 1CVASX11 1 1 2011 1 9 1 2011-03-28 00:00:00.000 1 U 9001234 1CVASX61 6 1 2011 1 9 2 2011-03-28 00:00:00.000 1 U 9

我需要在那里设置一个时间段，这样它只会在 ClassDate 的一周内加起来.有人知道吗?

解决方案

我认为您缺少的一件是将计数限制为仅包括缺勤，即缺勤时间少于原始缺勤时间的 7 天.

以下查询包含该条件，我相信它会提供您正在寻找的结果:

更新缺课SET 连续绝对值 = (选择ISNULL(SUM(CAST(IsAbsent AS numeric)), 0)从正在运行的课程TotalAbsent在哪里RunningTotalAbsent.IsAbsent = 1AND LessonsAbsent.[Student ID] = RunningTotalAbsent.[Student ID]AND LessonsAbsent.[班级编号] = RunningTotalAbsent.[班级编号]AND LessonsAbsent.[行号] = RunningTotalAbsent.[行号]AND LessonsAbsent.[Year] = RunningTotalAbsent.[Year]AND LessonsAbsent.ClassDate >= RunningTotalAbsent.ClassDate-- 如果缺席发生在 7 天内，则仅计为连续缺席.AND DATEDIFF(DAY, RunningTotalAbsent.ClassDate, LessonsAbsent.ClassDate) <7)来自 课程 课程缺席WHERE LessonsAbsent.IsAbsent = 1

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