I am working on identifying students that have 3 or more consecutive absences on a custom calendar (already created) for the school year.
I have a temp table for the custom calendar here:
SELECT
cal.CAL_DATE
, ROW_NUMBER() OVER (PARTITION BY cal.SCHOOL_YEAR_GU ORDER BY cal.CAL_DATE asc) rn
FROM Rev.EPC_SCH_ATT_CAL cal
JOIN rev.REV_ORGANIZATION_YEAR oy ON cal.SCHOOL_YEAR_GU = oy.ORGANIZATION_YEAR_GU
JOIN rev.REV_YEAR yr ON oy.YEAR_GU = yr.YEAR_GU
WHERE cal.ROTATION = 'I'
AND yr.YEAR_GU = (SELECT YEAR_GU FROM rev.SIF_22_Common_CurrentYearGU) e
I have a second query that shows the student absence dates:
SELECT
org.ORGANIZATION_NAME AS School
, stu.SIS_NUMBER AS PermID
, per.LAST_NAME + ', ' + per.FIRST_NAME AS Name
, grd.VALUE_DESCRIPTION AS Grade
, FORMAT (att.ABS_DATE, 'MM/dd/yyyy') AS 'AbsDate'
, abscd.ABBREVIATION
FROM rev.EPC_STU stu
JOIN rev.REV_PERSON per ON stu.STUDENT_GU = per.PERSON_GU
......
WHERE
ssy.STATUS IS NULL
AND yr.YEAR_GU = (SELECT YEAR_GU FROM rev.SIF_22_Common_CurrentYearGU)
The intent was to join the row number assigned to the Cal Date, and join that on the student absent date,
then use the ‘Island’ Technique to identify 3+ absences.
I can’t seem to find a way to complete this task from other questions I have read.
I tried joining the temp table (that identifies cal dates and row number) with the query that shows students’ absences to assign the row number to the absent date, to then use the island technique to look for 3 consecutive numbers per student absences, but I can’t get that to work with errors.
Jesse is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Assuming your calendar and absences queries as the starting point. (Also assuming that PermID represent a student id number.) You just need to join on the common dates and then look for gaps in the sequence to find where a run of dates begins. You appear to have intended that school days will be numbered consecutively so that absences across weekends and holidays will be treated part of the same run of dates:
with calendar as (
SELECT
cal.CAL_DATE
, ROW_NUMBER() OVER (PARTITION BY cal.SCHOOL_YEAR_GU
ORDER BY cal.CAL_DATE asc) rn
FROM Rev.EPC_SCH_ATT_CAL cal
JOIN rev.REV_ORGANIZATION_YEAR oy ON cal.SCHOOL_YEAR_GU = oy.ORGANIZATION_YEAR_GU
JOIN rev.REV_YEAR yr ON oy.YEAR_GU = yr.YEAR_GU
WHERE cal.ROTATION = 'I'
AND yr.YEAR_GU = (SELECT YEAR_GU FROM rev.SIF_22_Common_CurrentYearGU) e
), absences as (
SELECT
org.ORGANIZATION_NAME AS School
, stu.SIS_NUMBER AS PermID
, per.LAST_NAME + ', ' + per.FIRST_NAME AS Name
, grd.VALUE_DESCRIPTION AS Grade
, att.ABS_DATE
, abscd.ABBREVIATION
FROM rev.EPC_STU stu
JOIN rev.REV_PERSON per ON stu.STUDENT_GU = per.PERSON_GU
WHERE ssy.STATUS IS NULL
AND yr.YEAR_GU = (SELECT YEAR_GU FROM rev.SIF_22_Common_CurrentYearGU)
), flagged as (
select *
, case when rn - lag(rn) over (partition by PermID order by rn) = 1
then 0 else 1 end as isDateGap
from absences a inner join calendar c
on c.CAL_DATE = a.ABS_DATE
), grouped as (
select *
, sum(isDateGap) over (partition by PermID order by rn) as grp
from flagged
)
select PermID, count(*) as num_consecutive_absence,
min(ABS_DATE) as first_absence, max(ABS_DATE) as last_absence
from grouped
group by PermID, grp
having count(*) >= 3;