The Covid-19 pandemic drastically reshaped the global work culture. As offices shut down, remote work became the norm for millions. Employees adapted to virtual meetings, home desks, and flexible hours.
However, with the world returning to normal, companies are now mandating a return to office. Attendance is being monitored strictly, and multiple unplanned absences are being reviewed closely by HR and managers.
Here, we will demonstrate how to track consecutive employee absences using T-SQL. Below is a sample employee attendance table.
CREATE TABLE Employee_Daily_Attendance (
EmpID INT,
AttDate DATE,
Status VARCHAR(10) -- 'Present' or 'Absent'
);
-- Insert sample attendance records
INSERT INTO Employee_Daily_Attendance (EmpID, AttDate, Status)
VALUES
-- Employee 101
(101, '2025-07-01', 'Present'),
(101, '2025-07-02', 'Absent'),
(101, '2025-07-03', 'Absent'),
(101, '2025-07-04', 'Present'),
(101, '2025-07-05', 'Absent'),
-- Employee 102
(102, '2025-07-01', 'Absent'),
(102, '2025-07-02', 'Absent'),
(102, '2025-07-03', 'Absent'),
(102, '2025-07-04', 'Present'),
(102, '2025-07-05', 'Present'),
-- Employee 103
(103, '2025-07-01', 'Present'),
(103, '2025-07-02', 'Present'),
(103, '2025-07-03', 'Absent'),
(103, '2025-07-04', 'Absent'),
(103, '2025-07-05', 'Present'),
-- Employee 104
(104, '2025-07-01', 'Absent'),
(104, '2025-07-02', 'Present'),
(104, '2025-07-03', 'Absent'),
(104, '2025-07-04', 'Present'),
(104, '2025-07-05', 'Absent'),
-- Employee 105
(105, '2025-07-01', 'Present'),
(105, '2025-07-02', 'Present'),
(105, '2025-07-03', 'Present'),
(105, '2025-07-04', 'Present'),
(105, '2025-07-05', 'Present');
Steps to get Consecutive Absent(s) from Office
- Retrieve all employees who were absent at least once
--Retrieve all employees who were absent at least once
WITH AbsentOnly AS (
SELECT *
FROM dbo.Employee_Daily_Attendance
WHERE Status = 'Absent'
)
- Assign a rank to each absent day per employee in order of date
- Create a grouping key by subtracting the rank (e.g. 1 or 2 or 3 or..) from the date
- Consecutive absence dates will result in the same key value
-- Step 3: Subtract the rank from the date to create a grouping key
-- Consecutive absence dates will result in the same key value
DateDiffBasedGroup AS (
SELECT
EmpID,
AttDate,
Rnk,
DATEADD(DAY, -Rnk, AttDate) AS RankBasedKey
FROM RankedAbsences
)
select * from DateDiffBasedGroup
- Group by the calculated key to identify consecutive absence streaks
-- 🔹 Step 4: Group by the calculated key to identify consecutive absence streaks
-- Count the number of days in each streak, and get the start & end date
ConsecutiveGroups AS (
SELECT
EmpID,
COUNT(*) AS AbsentDays,
MIN(AttDate) AS StartDate,
MAX(AttDate) AS EndDate
FROM DateDiffBasedGroup
GROUP BY EmpID, RankBasedKey
)
SELECT * FROM ConsecutiveGroups
- Final output – Only show streaks with 2 or more consecutive absent days
— Step 5: Final output – Only show streaks with 2 or more consecutive absent days
SELECT *
FROM ConsecutiveGroups
WHERE AbsentDays >= 2;
- Below is the complete query to execute and see the result
-- Step 1: Filter only the days when employees were absent
WITH AbsentOnly AS (
SELECT *
FROM dbo.Employee_Daily_Attendance
WHERE Status = 'Absent'
),
-- Step 2: Assign a rank to each absent day per employee in order of date
-- This helps us identify the sequence of absence days
RankedAbsences AS (
SELECT
EmpID,
AttDate,
RANK() OVER (PARTITION BY EmpID ORDER BY AttDate) AS Rnk
FROM AbsentOnly
),
-- Step 3: Subtract the rank from the date to create a grouping key
-- Consecutive absence dates will result in the same key value
DateDiffBasedGroup AS (
SELECT
EmpID,
AttDate,
Rnk,
DATEADD(DAY, -Rnk, AttDate) AS RankBasedKey
FROM RankedAbsences
)
,
-- Step 4: Group by the calculated key to identify consecutive absence streaks
-- Count the number of days in each streak, and get the start & end date
ConsecutiveGroups AS (
SELECT
EmpID,
COUNT(*) AS AbsentDays,
MIN(AttDate) AS StartDate,
MAX(AttDate) AS EndDate
FROM DateDiffBasedGroup
GROUP BY EmpID, RankBasedKey
)
-- Step 5: Final output – Only show streaks with 2 or more consecutive absent days
SELECT *
FROM ConsecutiveGroups
WHERE AbsentDays >= 2;