Skip to content
Advertisement

Grouping shift data by 7-day windows in SQL Server 2012

What I want to do is to calculate the number of shifts and hours worked by each employee in any given 7-day period. In order to achieve this, I need to identify and group ‘islands’ of shifts. Note that this 7-day period is not tied to a calendar week and the beginning and ending of this 7-day period would vary from employee to employee. This is sets it apart from other similar questions asked her in the past.

I have a table like this:

I want group and summarise the data above like this:

Note that the first grouping for employee 12345 starts on 06-07-20 and ends on 11-07-20 because these shifts fall within the 06-07-2013-07-20 7-day window.

The next day 7-day window is from 22-07-20 to 28-07-20, which means that the start date for the 7-day window has to be dynamic and based on the data i.e. not constant which makes this a complex task.

Also note that an employee may work multiple shifts in a day and that the shifts may not be consecutive.

I was playing around with using DATEDIFF() with LAG() and LEAD() but was unable to get to where I want. Any help would be appreciated.

Advertisement

Answer

I think you need a recursive CTE gor this. The idea is to enumerate the shifts of each person, and then iteratively walk the dataset, while keeping track of the first date of the period – when there is more than 7 days between the start of a period and the current date, the start date resets, and a new group starts.

This assumes that:

  • dates do not span over multiple days, as shown in your sample data

  • dates are stored as date datatype, and times as time

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement