TSQL统计时间段内连续缺勤次数 [英] TSQL Counting number of Consecutive Absence in a row within time period
问题描述
问题:我正在尝试计算每个学生在一周内某个特定班级的连续缺勤次数.
例如如果课程 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 = 1Problem: 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
这篇关于TSQL统计时间段内连续缺勤次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
