What I did below is probably overkill to solve your situation, but this one should be pretty universal as it allows you to to have skifts starting after midnight. To do this, I allowed myself to add a new column to the daypart table in order to track in which order the shifts come.
If you don't like this change, you can change the below query, where you change all references to dayshiftorder to ex. daypartid or starthour,startminute.
The daypart table now looks like this:
CREATE TABLE #DayPart (DayPartID INT, [Name] NVARCHAR(20), StartHour INT, StartMinute INT, DayShiftOrder INT)
and your entries should look like this:
INSERT INTO #DayPart
VALUES (1,'Day', 8,0,1), (2,'Evening', 17,0,2), (3,'Overnight', 23,0,3)
Now to the query:
declare @dt_start date=cast('2019-01-01' as date);
declare @dt_end date=cast('2019-01-01' as date);
with cte_daypart
as (select row_number() over(order by starthour,startminute) as rn
,daypartid
,[name]
,dateadd(minute,starthour*60+startminute,cast('00:00' as time)) as starttime
,starthour*60+startminute as mm
from #daypart
)
,cte_workday
as (select dt_start
,dt_end
from (select dateadd(minute
,a.mm
+(row_number() over(order by (select null))-1)*24*60
,cast(@dt_start as datetime)
)
as dt_start
,dateadd(minute
,a.mm
+(row_number() over(order by (select null)))*24*60
,cast(@dt_start as datetime)
)
as dt_end
from cte_daypart as a
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as b1(n)
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as b2(n)
) as a
where dt_end<=dateadd(day,2,@dt_end)
)
,cte_daypart1
as (select row_number() over(order by dayshiftorder) as rn
,daypartid
,[name]
,starthour*60+startminute as minstart
from #daypart
)
,cte_daypart2
as (select a.rn
,a.daypartid
,a.[name]
,a.minstart+case when a.minstart<c.minstart then 24*60 else 0 end as minstart
,isnull(b.minstart+case when b.minstart<c.minstart then 24*60 else 0 end,c.minstart+24*60)
-(a.minstart+case when a.minstart<c.minstart then 24*60 else 0 end) as minlength
from cte_daypart1 as a
left outer join cte_daypart1 as b
on b.rn=a.rn+1
inner join cte_daypart1 as c
on c.rn=1
)
,cte_daypart3
as (select top(1) with ties
a.daypartid
,a.[name]
,dateadd(minute
,a.minstart
+(row_number() over(partition by a.daypartid
order by (select null)
)
-1
)*24*60
,cast(@dt_start as datetime)
)
as dt_start
,dateadd(minute
,a.minstart
+a.minlength
+(row_number() over(partition by a.daypartid
order by (select null)
)
-1
)*24*60
,cast(@dt_start as datetime)
)
as dt_end
from cte_daypart2 as a
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as b1(n)
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as b2(n)
order by sign(row_number() over(partition by a.rn
order by (select null)
)
-datediff(day,@dt_start,@dt_end)
-2
)
)
,cte_shifts
as (select employeenumber
,dateadd(minute,inhour*60+inminute,dateofbusiness) as dt_start
,dateadd(minute,inhour*60+inminute+totalminutes,dateofbusiness) as dt_end
from #shifts
where dateofbusiness>=@dt_start
and dateofbusiness<dateadd(day,2,@dt_end)
)
select b.employeenumber
,a.daypartid
,a.[name]
,case when a.dt_start<b.dt_start then b.dt_start else a.dt_start end as dt_start
,case when a.dt_end<b.dt_end then a.dt_end else b.dt_end end as dt_end
,dateadd(minute
,datediff(minute
,case when a.dt_start<b.dt_start then b.dt_start else a.dt_start end
,case when a.dt_end<b.dt_end then a.dt_end else b.dt_end end
)
,cast('00:00:00' as time)
)
as totalhours
,datediff(minute
,case when a.dt_start<b.dt_start then b.dt_start else a.dt_start end
,case when a.dt_end<b.dt_end then a.dt_end else b.dt_end end
)
as totalminutes
from cte_daypart3 as a
inner join cte_shifts as b
on b.dt_end>=a.dt_start
and b.dt_start<=a.dt_end
order by b.employeenumber
,a.dt_start
;
