SQLTeam.com | Weblogs | Forums

Re-Structure data for reporting

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?

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.


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

	[Name] varchar(30) NULL
	,Date_of_Absence date NULL
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:

	SELECT [Name], Date_of_Absence
			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
	SELECT [Name], Date_of_Absence
		,SUM(Gap) OVER (PARTITION BY [Name] ORDER BY Date_of_Absence) AS Grp
	FROM Gaps
	,MIN(Date_of_Absence) AS AbsenceStart
	,MAX(Date_of_Absence) AS AbsenceEnd
GROUP BY [Name], Grp;
1 Like

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
    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

Thanks everyone for the great responses! :grinning: