Calculating Consecutive Absences in Sql

Calculating Consecutive Absences in SQL

This should work for you. GROUP BY on ConsecDates to find who was absent more than X number of times.

select a.*, 
        (
            select min(b.absenceDate) from tblAbsences b where a.employeeId = b.employeeId 
            and b.absenceDate >= a.absenceDate
            and not exists ( 
                select 1 from tblabsences c where c.employeeId = b.employeeId and dateadd( dd, 1, b.absenceDate) = c.absenceDate  
            )
) ConsecDates
from dbo.tblAbsences a
order by a.AbsenceDate asc

SQL Server : finding consecutive absence counts for students over custom dates

Oh, you have both absences and presents in the table. You can use the difference of row_numbers() approach:

select studentid, min(date), max(date)
from (select a.*,
             row_number() over (partition by studentid order by date) as seqnum,
             row_number() over (partition by studentid, attendance order by date) as seqnum_a
      from attendance a
     ) a
where attendance = 'A'
group by studentid, (seqnum - seqnum_a)
having count(*) >= 3;

The difference of row numbers gets consecutive values that are the same. This is a little tricky to understand, but if you run the subquery, you should see how the difference is constant for consecutive absences or presents. You only care about absences, hence the where in the outer query.

SQL: Find group of rows for consecutive days absent

You can use this to find your absence ranges. In here I use a recursive CTE to number all days from a few years while at the same time record their week day. Then use another recursive CTE to join absence dates for the same student that are one day after another, considering weekends should be skipped (read the CASE WHEN on the join clause). At the end show each absence spree filtered by N successive days.

SET DATEFIRST 1 -- Monday = 1, Sunday = 7

;WITH Days AS
(
    -- Recursive anchor: hard-coded first date
    SELECT
        GeneratedDate = CONVERT(DATE, '2017-01-01')

    UNION ALL

    -- Recursive expression: all days until day X
    SELECT
        GeneratedDate = DATEADD(DAY, 1, D.GeneratedDate)
    FROM
        Days AS D
    WHERE
        DATEADD(DAY, 1, D.GeneratedDate) <= '2020-01-01'
),
NumberedDays AS
(
    SELECT
        GeneratedDate = D.GeneratedDate,
        DayOfWeek = DATEPART(WEEKDAY, D.GeneratedDate),
        DayNumber = ROW_NUMBER() OVER (ORDER BY D.GeneratedDate ASC)
    FROM
        Days AS D
),
AttendancesWithNumberedDays AS
(
    SELECT
        A.*,
        N.*
    FROM
        Attendance AS A
        INNER JOIN NumberedDays AS N ON A.Date = N.GeneratedDate
),
AbsenceSpree AS
(
    -- Recursive anchor: absence day with no previous absence, skipping weekends
    SELECT
        StartingAbsenceDate = A.Date,
        CurrentDateNumber = A.DayNumber,
        CurrentDateDayOfWeek = A.DayOfWeek,
        AbsenceDays = 1,
        StudentID = A.StudentID
    FROM
        AttendancesWithNumberedDays AS A
    WHERE
        NOT EXISTS (
            SELECT
                'no previous absence date'
            FROM
                AttendancesWithNumberedDays AS X
            WHERE
                X.StudentID = A.StudentID AND
                X.DayNumber = CASE A.DayOfWeek 
                    WHEN 1 THEN A.DayNumber - 3 -- When monday then friday (-3)
                    WHEN 7 THEN A.DayNumber - 2 -- When sunday then friday (-2)
                    ELSE A.DayNumber - 1 END)

    UNION ALL

    -- Recursive expression: find the next absence day, skipping weekends
    SELECT
        StartingAbsenceDate = S.StartingAbsenceDate,
        CurrentDateNumber = A.DayNumber,
        CurrentDateDayOfWeek = A.DayOfWeek,
        AbsenceDays = S.AbsenceDays + 1,
        StudentID = A.StudentID
    FROM
        AbsenceSpree AS S
        INNER JOIN AttendancesWithNumberedDays AS A ON
            S.StudentID = A.StudentID AND
            A.DayNumber = CASE S.CurrentDateDayOfWeek
                WHEN 5 THEN S.CurrentDateNumber + 3 -- When friday then monday (+3)
                WHEN 6 THEN S.CurrentDateNumber + 2 -- When saturday then monday (+2)
                ELSE S.CurrentDateNumber + 1 END
)
SELECT
    StudentID = A.StudentID,
    StartingAbsenceDate = A.StartingAbsenceDate,
    EndingAbsenceDate = MAX(N.GeneratedDate),
    AbsenceDays = MAX(A.AbsenceDays)
FROM
    AbsenceSpree AS A
    INNER JOIN NumberedDays AS N ON A.CurrentDateNumber = N.DayNumber
GROUP BY
    A.StudentID,
    A.StartingAbsenceDate
HAVING
    MAX(A.AbsenceDays) >= 3
OPTION
    (MAXRECURSION 5000)

If you want to list the original Attendance table rows, you can replace the last select:

SELECT
    StudentID = A.StudentID,
    StartingAbsenceDate = A.StartingAbsenceDate,
    EndingAbsenceDate = MAX(N.GeneratedDate),
    AbsenceDays = MAX(A.AbsenceDays)
FROM
    AbsenceSpree AS A
    INNER JOIN NumberedDays AS N ON A.CurrentDateNumber = N.DayNumber
GROUP BY
    A.StudentID,
    A.StartingAbsenceDate
HAVING
    MAX(A.AbsenceDays) >= 3

with this CTE + SELECT:

,
FilteredAbsenceSpree AS
(
    SELECT
        StudentID = A.StudentID,
        StartingAbsenceDate = A.StartingAbsenceDate,
        EndingAbsenceDate = MAX(N.GeneratedDate),
        AbsenceDays = MAX(A.AbsenceDays)
    FROM
        AbsenceSpree AS A
        INNER JOIN NumberedDays AS N ON A.CurrentDateNumber = N.DayNumber
    GROUP BY
        A.StudentID,
        A.StartingAbsenceDate
    HAVING
        MAX(A.AbsenceDays) >= 3
)
SELECT
    A.*
FROM
    Attendance AS A
    INNER JOIN FilteredAbsenceSpree AS F ON A.StudentID = F.StudentID
WHERE
    A.Date BETWEEN F.StartingAbsenceDate AND F.EndingAbsenceDate
OPTION
    (MAXRECURSION 5000)

SQL query that reports N or more consecutive absents from attendance table

I wasn't able to create an SQL query for this. So instead, I tried a PHP solution:

1. Select all rows from table, ordered by student, subject and date
2. Create a running counter for absents, initialized to 0
3. Iterate over each record:
   
   • If student and/or subject is different from previous row
     
     • Reset the counter to 0 (present) or 1 (absent)
   • Else, that is when student and subject are same
     
     • Set the counter to 0 (present) or plus 1 (absent)

I then realized that this logic can easily be implemented using MySQL variables, so:

SET @studentID = 0;
SET @subjectID = 0;
SET @absentRun = 0;

SELECT *,
CASE
    WHEN (@studentID  = studentID) AND (@subjectID  = subjectID) THEN @absentRun := IF(attendanceStatus = 1, 0, @absentRun + 1)
    WHEN (@studentID := studentID) AND (@subjectID := subjectID) THEN @absentRun := IF(attendanceStatus = 1, 0, 1)
END AS absentRun
FROM table4
ORDER BY studentID, subjectID, classDate

You can probably nest this query inside another query that selects records where absentRun >= 3.

SQL Fiddle

TSQL Counting number of Consecutive Absences in a row

Similar to the answer to your previous question, only this time it looks for a class that has been attended, instead of just limiting the search by a week.

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

Counting Consecutive days SQL Server

You can get the periods of absences using:

select name, min(date), max(date), count(*) as numdays, type
from (select a.*,
             row_number() over (partition by name, type order by date) as seqnum_ct
      from absence a
     ) a
group by name, type, dateadd(day, -seqnum_ct, date);

Here is a SQL Fiddle for this.

You can add having count(*) > 1 to get periods with one day or more. This seems useful. I don't understand what the ultimate output is. The description just doesn't make sense to me.

If you want the number of absences that are 2 or more days, then use this as a subquery/CTE:

select name, count(*), type
from (select name, min(date) as mindate, max(date) as maxdate, count(*) as numdays, type
      from (select a.*,
                   row_number() over (partition by name, type order by date) as seqnum_ct
            from absence a
           ) a
      group by name, type, dateadd(day, -seqnum_ct, date)
     ) b
where numdays > 1
group by name, type;

Finding consecutive 7 days absent employee from attendance table neglecting week off and holiday in sql server R2

Will this do?

CREATE TABLE Attendance(
    Emp_id      VARCHAR(10),
    Emp_name    VARCHAR(10),
    PDate       DATE,
    Status      VARCHAR(2)
)
INSERT INTO Attendance VALUES
('000002', 'Pramod', '2014-01-11', 'A'),
('000002', 'Pramod', '2014-01-12', 'WO'),
('000002', 'Pramod', '2014-01-13', 'A'),
('000002', 'Pramod', '2014-01-14', 'A'),
('000002', 'Pramod', '2014-01-15', 'H'),
('000002', 'Pramod', '2014-01-16', 'A'),
('000002', 'Pramod', '2014-01-17', 'A'),
('000002', 'Pramod', '2014-01-18', 'A'),
('000002', 'Pramod', '2014-01-19', 'A'),
('000002', 'Pramod', '2014-01-20', 'P'),
('000002', 'Pramod', '2014-01-21', 'A');

;WITH GroupedDates AS(
    SELECT
        *,
        DateGroup = DATEADD(DD, - ROW_NUMBER() OVER (PARTITION BY Emp_id ORDER BY PDate), PDate)
    FROM Attendance
    WHERE
        Status IN('A', 'WO', 'H')
)
SELECT 
    Emp_id,
    Emp_name,
    StartDate   =   MIN(PDate),
    EndDate     =   MAX(PDate),
    Days        =   DATEDIFF(DD, MIN(PDate), MAX(PDate)) + 1 
                    - SUM((CASE WHEN Status IN('WO', 'H') THEN 1 ELSE 0 END))
FROM GroupedDates
GROUP BY 
    Emp_id, Emp_name, DateGroup
HAVING 
    SUM(CASE WHEN Status = 'A' THEN 1 ELSE 0 END) >=7
ORDER BY 
    Emp_id, Emp_name, StartDate

DROP TABLE Attendance

Find consecutive days absent

I would try something like this.

WITH T AS
(SELECT empno, dateintime, @row:=@row+1 rownum FROM
attendance JOIN (SELECT @row:=0) init
ORDER BY empno, timeindate)

SELECT DISTINCT t1.empno
FROM T AS t1 INNER JOIN T AS t2
ON t1.empno = t2.empno AND t1.rownum = t2.rownum + 1
WHERE TIMESTAMPDIFF(DAY, t2.dateintime, t1.dateintime) >= 3

First query orders the table by datetime and enumerates rows. Then, you join it with itself, with a shift in row number, getting neighbor datetimes in one row. And then, you just look for entries with a needed difference.

I'm not sure it works, but it should work like that: first query:

empno:     | timeindate          | row

empno 1    | 2013-10-01 00:00:00 | 0
empno 1    | 2013-10-02 00:00:00 | 1
empno 1    | 2013-10-06 00:00:00 | 2
empno 2    | 2013-10-01 00:00:00 | 3
empno 2    | 2013-10-02 00:00:00 | 4
empno 2    | 2013-10-03 00:00:00 | 5
empno 2    | 2013-10-04 00:00:00 | 6
empno 2    | 2013-10-05 00:00:00 | 7
empno 2    | 2013-10-06 00:00:00 | 8

Second query without WHERE clause:

empno 1    | 2013-10-02 00:00:00 | 2013-10-02 00:00:00
empno 1    | 2013-10-02 00:00:00 | 2013-10-06 00:00:00
empno 2    | 2013-10-01 00:00:00 | 2013-10-02 00:00:00
empno 2    | 2013-10-02 00:00:00 | 2013-10-03 00:00:00
etc.

As you see, the WHERE will filter the second row from this result.

---

Related Topics