Re-Structure data for reporting

1

Below is my data for a sickness report. I am trying to do two things so that I can report on the sickness periods and duration.

  1. The Row Number column should start from 1 again on date/row 1/9/2021

  2. I have tried several Windows Functions but the data is not structured correctly

  3. The Sickness Period column should be grouped and say 2 on date/row 1/9/2021

Any ideas what I could do to re-structure the data to work with Windows Functions and to group in Sickness Periods?

image
image817×331 115 KB

2

Hi, welcome!

Could you provide CREATE TABLE and INSERT statements rather than just a picture of the data?

That would get you much better answers to your q, since it's vastly easier to develop SQL if you can run it / test it while writing, but most of us volunteers here don't have time to type in all that data ourselves.

2 Likes
3

Test data with dates in ISO format. Please provide in future.

CREATE TABLE #t
(
	[Name] varchar(30) NULL
	,Date_of_Absence date NULL
);
INSERT INTO #t
VALUES ('Mr Robert W', '20210102')
	,('Mr Robert W', '20210103')
	,('Mr Robert W', '20210104')
	,('Mr Robert W', '20210109')
	,('Mr Robert W', '20210110')
	,('Mr Robert W', '20210111')
	,('Mr Robert W', '20210112');

Try something like:

WITH Gaps
AS
(
	SELECT [Name], Date_of_Absence
		,CASE
			WHEN LAG(Date_of_Absence) OVER (PARTITION BY [Name] ORDER BY Date_of_Absence)
				= DATEADD(day, -1, Date_of_Absence)
			THEN 0
			ELSE 1
		END AS Gap
	FROM #t
)
,Grps
AS
(
	SELECT [Name], Date_of_Absence
		,SUM(Gap) OVER (PARTITION BY [Name] ORDER BY Date_of_Absence) AS Grp
	FROM Gaps
)
SELECT [Name]
	,MIN(Date_of_Absence) AS AbsenceStart
	,MAX(Date_of_Absence) AS AbsenceEnd
FROM Grps
GROUP BY [Name], Grp;
1 Like
4

You'll want to make sure the table has an index with leading keys of ( Name, Date_of_Absence ), in that order.

;WITH groups AS (
    SELECT *
    FROM #t t_curr
    WHERE NOT EXISTS(SELECT 1 FROM #t t_prev WHERE t_prev.Name = t_curr.Name AND 
        t_prev.Date_of_Absence = DATEADD(DAY, -1, t_curr.Date_of_Absence))
)
SELECT t.Date_of_Absence, DATENAME(WEEKDAY, t.Date_of_Absence) AS Day, 
    t.Name, 1 AS Count, 
    ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY t.Date_of_Absence) AS Running_Total, g.group_number AS Sickness_Period,
    ROW_NUMBER() OVER(PARTITION BY g.group_number ORDER BY t.Date_of_Absence) AS Row_Number
FROM #t t
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date_of_Absence) AS group_number,
        ISNULL(LEAD(Date_of_Absence, 1) OVER(PARTITION BY Name ORDER BY Date_of_Absence), '20790101') AS next_date_of_absence
    FROM groups
) AS g ON g.Name = t.Name AND t.Date_of_Absence >= g.Date_of_Absence AND t.Date_of_Absence < g.next_date_of_absence
1 Like
5

Thanks everyone for the great responses! :grinning: