Our HR system specifies employee assignments, which can be concurrent. Our rostering system only allows one summary assignment for a person. Therefore I need to pre-process the HR records, so rostering can determine the number of shifts a worker is expected to work on a given day.
Looking just at worker A who has two assignments, the first is for a quarter shift and the second for a half shift, but overlapping in the middle where they work .75 shifts.
Person StartDate EndDate Shifts
A 01/01/21 04/01/21 .25
A 03/01/21 06/01/21 .5
01---02---03---04---05---06---07
Rec 1 |------------------|
Rec 2 | |===================|
Total | 0.25 | 0.75 | 0.5 |
Required output.
Person StartDate EndDate ShiftCount A 01/01/21 02/01/21 0.25 A 03/01/21 04/01/21 0.75 A 05/01/21 06/01/21 0.5
Given this data, how do we sum and segment the data? I found an exact question for MySQL but the version was too early and code was suggested. I also found a Postgres solution but we don’t have ranges.
select * from (
values
('A','01/01/21','04/01/21',0.25),
('A','03/01/21','05/01/21',0.5)
) AS Data (Person,StartDate,EndDate,Shifts);
Advertisement
Answer
It looks like a Gaps-and-Islands to me.
If it helps, cte1 is used to expand the date ranges via an ad-hoc tally table. Then cte2 is used to create the Gaps-and-Islands. The final result is then a small matter of aggregation.
Example
Set Dateformat DMY
Declare @YourTable table (Person varchar(50),StartDate Date,EndDate date,Shifts decimal(10,2))
Insert Into @YourTable values
('A','01/01/21','04/01/21',0.25)
,('A','03/01/21','05/01/21',0.5)
;with cte1 as (
Select [Person]
,[d] = dateadd(DAY,N,StartDate)
,Shifts = sum(Shifts)
From @YourTable A
Join (
Select Top 1000 N=-1+Row_Number() Over (Order By (Select Null))
From master..spt_values n1,master..spt_values n2
) B on N <= datediff(DAY,[StartDate],[EndDate])
Group By Person,dateadd(DAY,N,StartDate)
), cte2 as (
Select *
,Grp = datediff(day,'1900-01-01',d)-row_number() over (partition by Person,Shifts Order by d)
From cte1
)
Select Person
,StartDate = min(d)
,EndDate = max(d)
,Shifts = max(Shifts)
From cte2
Group By Person,Grp
Returns
Person StartDate EndDate Shifts A 2021-01-01 2021-01-02 0.25 A 2021-01-03 2021-01-04 0.75 A 2021-01-05 2021-01-05 0.50