Sum and segment overlapping date ranges

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

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