我有一个任务列表，
对于每个任务，我都有一个任务ID，startTime和StopTime作为1-1-1970的毫秒和一个用户列表(#Tasks)。
我需要计算每个用户在任务上花费的时间，按白天/晚上时间、周或周末、常规/加班时间划分，考虑到从晚上 10：00 到早上 06：00 的夜间时间。
当然有更好的解决方案，但到目前为止我得到了这个：
IF OBJECT_ID('tempdb..#Tasks') IS NULL
BEGIN
create table #Tasks
(
TaskID nvarchar(50),
DateStart bigint,
DateStop bigint,
Staff nvarchar(100)
)
insert into #Tasks values
('C001',1554181200000,1554190200000,'john,jack'),
('C002',1554202800000,1554212700000,'tom,john'),
('C003',1554228000000,1554246900000,'john,franck'),
('C004',1554613200000,1554626700000,'john')
END
GO
declare
@UserName nvarchar(50)='john',
@DateFrom datetime='2019-04-01',
@DateTo datetime='2019-04-30',
@nStart time='06:00:00',
@nStop time='22:00:00'
select
startday as [Day],
sum([WeekDay]) as [WeekDay],
sum([WeekNight]) as [WeekNight],
sum([WeekendDay]) as [WeekendDay],
sum([WeekendNight]) as [WeekendNight],
sum([TotalMinutes]) as [TotalMinutes],
0 WeekDayOverTime,
0 WeekNightOvertime,
0 WeekendDayOvertime,
0 WeekendNightOvertime,
[UserName]
,timeframe
from
(
select
iif(isWeekend=1,NightMinutes,0) WeekendNight,
iif(isWeekend=0,NightMinutes,0) WeekNight,
iif(isWeekend=1,DayMinutes,0) WeekendDay,
iif(isWeekend=0,DayMinutes,0) [WeekDay],
TotalMinutes,
username,
startday,
timeframe
from
(
select
iif(Before6>0,Before6,0)+ iif(After22>0,After22,0) NightMinutes,
TotalMinutes-iif(Before6>0,Before6,0)- iif(After22>0,After22,0) DayMinutes,
TotalMinutes,
startday,
isWeekend,
UserName,
timeframe
from
(
Select
(t.datestop-t.datestart)/60000 TotalMinutes,
datediff(n,convert(time,DATEADD(SECOND,t.DateStart/1000,'1970-01-01')),@nStart) Before6,
datediff(n,@nStop,convert(time,DATEADD(SECOND,t.DateStop/1000,'1970-01-01'))) After22,
iif((((DATEPART(DW, convert(datetime,DATEADD(SECOND,t.DateStart/1000,'1970-01-01'))) - 1 ) + @@DATEFIRST ) % 7) IN (0,6),1,0) isWeekend,
convert(varchar(10),DATEADD(SECOND,t.DateStart/1000,'1970-01-01'),126) startday,
STUFF(( SELECT distinct ' ' + convert(varchar(5),DATEADD(SECOND,t.DateStart/1000,'1970-01-01'),108)+'-'+convert(varchar(5),DATEADD(SECOND,t.DateStop/1000,'1970-01-01'),108) AS [text()]
FROM #Tasks tt
--WHERE tt.taskID=t.TaskID
FOR XML PATH('') ), 1, 1, '' ) AS [timeframe],
@UserName UserName
FROM #Tasks t
WHERE t.Staff like '%'+@UserName+'%'
and DATEADD(SECOND,t.DateStart/1000,'1970-01-01') between @DateFrom and @DateTo
) z
) zz
) zzz
group by startday,username,timeframe
order by startday
我现在需要：1)按天对结果进行分组，总结工作日，工作日，周末，
周末晚上和总分钟数，并串联时间范围，例如在4月2日"05：00-07：30|11：00-13：45|18：00-23：00"2)不总结12：
00和12：30之间的时间(如果适用)，因为是午餐时间
3)考虑到每天8小时后必须计算为加班， 我必须在时间和加班之间分配总分钟数，但取决于加班是白天还是晚上还是周末
4)最终使用假日表
换句话说，我们应该有这个：
Day TotalMinutes WeekDay WeekNight WeekendDay WeekendNight WeekDayOverTime WeekNightOvertime WeekendDayOvertime WeekendNightOvertime UserName timeframe
02/04/2019 630 420 60 0 0 45 75 0 0 john 05:00-07:30|11:00-13:45|18:00-23:00
07/04/2019 225 0 0 165 60 0 0 0 0 john 05:00-08:45
因为(4 月 2 日)我们有：第一个任务：
60 分钟的正常夜间
时间 90 分钟的常规白天时间
第二项任务：
正常白天165分钟，但由于午餐时间，只能算135
分钟 第三个任务：240 白天 75 夜间但由于任务 1 和 2 我们总结了 285 分钟，所以只有第三个任务的前 185 分钟是正常白天时间：
其余 45 个是白天
加班时间，夜间时间的以下 75 分钟实际上是夜间
加班时间
在这种方法中，第一个 CTE(正确日期)获取开始日期和停止日期时间，然后您无需在查询中重复该公式。
第二个 CTE(splittedMinutes)是获取与当前方法相同的数据，除了第一个 CROSS APPLY，它将时间帧与午餐时间分开。第二个 CROSS APPLY 获取分钟数和 isWeekend 值。
在第三个 CTE(合格分钟)中，我使用窗口分区来获取累积的分钟数并在适用时生成加班时间。
最后，我使用选择性 SUM 将工作日和周末分开
。;with properDates AS (
SELECT TaskID, DATEADD(SECOND,t.DateStart/1000,'1970-01-01') as DateStart,DATEADD(SECOND,t.DateStop/1000,'1970-01-01') as DateStop, Staff
FROM #Tasks t
WHERE Staff LIKE '%' + @UserName + '%'
), splittedMinutes AS (
select
CAST(p.DateStart AS DATE) as [Day],
TotalMinutes,
SUM(TotalMinutes) OVER (PARTITION BY CAST(p.DateStart AS DATE) ORDER BY b.start) AS cumulate,
TotalMinutes - EarlyMinutes - LateMinutes as DayTime,
EarlyMinutes + LateMinutes as NightTime,
isWeekend,
CONVERT(VARCHAR(5),b.Start,108) + '-' + CONVERT(VARCHAR(5),b.Stop,108) as [timeframe]
from properdates p
cross apply (
select CAST(p.DateStart As TIME) AS Start, @bStart as Stop WHERE CAST(p.DateStart AS TIME) < @bStart and CAST(p.DateStop AS TIME) > @bStart
union
select @bStop as Start, CAST(DateStop AS TIME) AS Stop WHERE CAST(p.DateStop AS TIME) > @bStop and CAST(p.DateStart AS TIME) < @bStop
union
select CAST(p.DateStart AS TIME) AS Start, CAST(p.DateStop AS TIME) AS Stop WHERE NOT (CAST(p.DateStart AS TIME) < @bStart and CAST(p.DateStop AS TIME) > @bStart) AND NOT (CAST(p.DateStop AS TIME) > @bStop and CAST(p.DateStart AS TIME) < @bStop)
) b
cross apply (
select
DATEDIFF(Minute, b.Start, b.Stop) as TotalMinutes,
(DATEDIFF(Minute, CAST(b.Start AS TIME), @nStart) + ABS(DATEDIFF(Minute, CAST(b.Start AS TIME), @nStart))) / 2 as EarlyMinutes,
(DATEDIFF(Minute, @nStop, CAST(b.Stop AS TIME)) + ABS(DATEDIFF(Minute, @nStop, CAST(b.Stop AS TIME)))) / 2 as LateMinutes,
CASE WHEN DATEPART(DW, p.DateStart) IN (1,7) THEN 1 ELSE 0 END AS isWeekend
) c
), qualifiedMinutes As (
SELECT Day, TotalMinutes, RegularDay, RegularNight, OvertimeDay, OvertimeNight, isWeekend, timeframe
FROM splittedMinutes
OUTER APPLY (
SELECT RegularDay = CASE WHEN cumulate <= @maxTime THEN DayTime WHEN DayTime - (cumulate - TotalMinutes - @maxTime) > 0 THEN ABS(cumulate - TotalMinutes - @maxTime) ELSE 0 END
) RD
OUTER APPLY (
SELECT OvertimeDay = DayTime - RegularDay
) OWD
OUTER APPLY (
SELECT RegularNight = CASE WHEN cumulate <= @maxTime THEN NightTime WHEN (cumulate - TotalMinutes - @maxTime + RegularDay) < 0 THEN NightTime + (cumulate - TotalMinutes - @maxTime + RegularDay) ELSE 0 END
) RWN
OUTER APPLY (
SELECT OvertimeNight = NightTime - RegularNight
) OWN
)
SELECT
Day,
@UserName and UserName,
SUM(TotalMinutes) AS TotalMinutes,
SUM(CASE WHEN isWeekend = 0 THEN RegularDay ELSE 0 END) AS WeekDay,
SUM(CASE WHEN isWeekend = 0 THEN RegularNight ELSE 0 END) AS WeekNight,
SUM(CASE WHEN isWeekend = 1 THEN RegularDay ELSE 0 END) AS WeekendDay,
SUM(CASE WHEN isWeekend = 1 THEN RegularNight ELSE 0 END) AS WeekendNight,
SUM(CASE WHEN isWeekend = 0 THEN OvertimeDay ELSE 0 END) AS WeekDayOverTime,
SUM(CASE WHEN isWeekend = 0 THEN OvertimeNight ELSE 0 END) AS WeekNightOvertime,
SUM(CASE WHEN isWeekend = 1 THEN OvertimeDay ELSE 0 END) AS WeekendDayOverTime,
SUM(CASE WHEN isWeekend = 1 THEN OvertimeNight ELSE 0 END) AS WeekendNightOvertime,
STUFF((SELECT '|' + timeframe FROM qualifiedMinutes tt WHERE tt.Day = q.Day ORDER BY timeframe FOR XML PATH('') ), 1, 1, '' ) AS [timeframe]
FROM qualifiedMinutes q
GROUP BY Day