User-1225738063 posted
Don't need a loop, but it'll be a pretty complex statement. The approach I would take also depends on how peformant you really need the query to run. The faster you need it to run, the more complex the query will be.
This would be my first approach to the problem:
First break the problem into steps. Assuming tables:
Table h (date datetime) -- "Holiday table"
Table a (studentid int,date datetime) -- "Absentee table"
1) We we need a resultset that contains the dates each person did not attend class.
2) Then we need a resultset consisting of each consecutive range of dates missed.
3) We then need to calculate how many non-weekend, non-holiday days is in each range.
4) We need to see if we have any range that is >=6.
Step 1
SELECT
studentid,date
FROM
a
UNION
SELECT
studentid,h.date
FROM (SELECT
distinct studentid
FROM a) z
CROSS JOIN h
Step 2
SELECT
studentid,date AS Start,(SELECT
TOP 1 date
FROM
(SELECT studentid,date
FROM
a
UNION
SELECT
studentid,h.date
FROM
(SELECT
distinct studentid
FROM a) z
CROSS
JOIN h) t2
WHERE
CASE
WHEN
DATEPART(dw,date)=6
THEN
DATEADD(d,3,date)
ELSE
DATEADD(d,1,date)
END
NOT IN
(SELECT date
FROM
a WHERE studentid=t2.studentid
UNION
SELECT
date FROM h)
AND t2.studentid=t1.studentid
AND t2.date>=t1.date
ORDER
BY date
ASC) AS Finish
FROM
(SELECT studentid,date
FROM
a
UNION
SELECT
studentid,h.date
FROM
(SELECT
distinct studentid
FROM a) z
CROSS
JOIN h) t1
WHERE
CASE
WHEN
DATEPART(dw,date)=2
THEN
DATEADD(d,-3,date)
ELSE
DATEADD(d,-1,date)
END
NOT IN
(SELECT date
FROM
a WHERE studentid=t1.studentid
UNION
SELECT
date FROM h)
STEP 3
SELECT
studentid,Start,Finish,datediff(d,Start,Finish)+1
AS ConsecutiveDays,datediff(week,Start,Finish)*2
AS WeekendDays,(SELECT
COUNT(*)
FROM h
WHERE date>=Start
and date<=Finish)
AS Holidays
FROM
(
SELECT
studentid,date
AS Start,(SELECT
TOP 1 date
FROM
(SELECT studentid,date
FROM
a
UNION
SELECT
studentid,h.date
FROM
(SELECT
distinct studentid
FROM a) z
CROSS
JOIN h) t2
WHERE
CASE
WHEN
DATEPART(dw,date)=6
THEN
DATEADD(d,3,date)
ELSE
DATEADD(d,1,date)
END
NOT IN
(SELECT date
FROM
a WHERE studentid=t2.studentid
UNION
SELECT
date FROM h)
AND t2.studentid=t1.studentid
AND t2.date>=t1.date
ORDER
BY date
ASC)
AS Finish
FROM
(SELECT studentid,date
FROM
a
UNION
SELECT
studentid,h.date
FROM
(SELECT
distinct studentid
FROM a) z
CROSS
JOIN h) t1
WHERE
CASE
WHEN
DATEPART(dw,date)=2
THEN
DATEADD(d,-3,date)
ELSE
DATEADD(d,-1,date)
END
NOT IN
(SELECT date
FROM
a WHERE studentid=t1.studentid
UNION
SELECT
date FROM h)
)
t3
STEP 4
SELECT
studentid,Start,Finish,datediff(d,Start,Finish)+1
AS ConsecutiveDays,datediff(week,Start,Finish)*2
AS WeekendDays,(SELECT
COUNT(*)
FROM h
WHERE date>=Start
and date<=Finish)
AS Holidays,
datediff
(d,Start,Finish)+1-datediff(week,Start,Finish)*2-(SELECT
COUNT(*)
FROM h
WHERE date>=Start
and date<=Finish)
AS ConsecutiveClassesMissed
FROM
(
SELECT
studentid,date
AS Start,(SELECT
TOP 1 date
FROM
(SELECT studentid,date
FROM
a
UNION
SELECT
studentid,h.date
FROM
(SELECT
distinct studentid
FROM a) z
CROSS
JOIN h) t2
WHERE
CASE
WHEN
DATEPART(dw,date)=6
THEN
DATEADD(d,3,date)
ELSE
DATEADD(d,1,date)
END
NOT IN
(SELECT date
FROM
a WHERE studentid=t2.studentid
UNION
SELECT
date FROM h)
AND t2.studentid=t1.studentid
AND t2.date>=t1.date
ORDER
BY date
ASC)
AS Finish
FROM
(SELECT studentid,date
FROM
a
UNION
SELECT
studentid,h.date
FROM
(SELECT
distinct studentid
FROM a) z
CROSS
JOIN h) t1
WHERE
CASE
WHEN
DATEPART(dw,date)=2
THEN
DATEADD(d,-3,date)
ELSE
DATEADD(d,-1,date)
END
NOT IN
(SELECT date
FROM
a WHERE studentid=t1.studentid
UNION
SELECT
date FROM h)
)
t3
WHERE
datediff(d,Start,Finish)+1-datediff(week,Start,Finish)*2-(SELECT
COUNT(*)
FROM h
WHERE date>=Start
and date<=Finish)>=6
I will leave the rest to you. That will give you a resultset that you should be able to work with from there. It'll list each "break" the students have taken that is longer than 6 missed days. You can remove all the
fields except studentid and make it a "SELECT DISTINCT studentid" if you just want a list of students that have missed 6 or more consecutive classes. This also assumes the following:
The holiday table does not list holidays that are also on a weekend. If it DOES list them, then the code will no longer work. You'll need to remove them either through a view, or replacing each instance of the table h with
a subquery that removes holidays that are on the weekend (WHERE datepart(dw,date)=7 or datepart(dw,date)=1). Both date fields (in the a and h tables) are dates ONLY with no time component. If times are also mentioned then you'll need to strip the
time portion either through a view, or replacing date with DATEADD(d,DATEDIFF(d,0,date),0).
