sql-server - SQL Server 2014 - 按日/夜时间、周/周末、常规/加班时间汇总工作时间组
问题描述
我有一个任务列表,
每个任务都有一个 TaskID、startTime 和 StopTime,从 1970 年 1 月 1 日开始以毫秒为单位,还有一个用户列表 (#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) 按天对结果进行分组,总结 WeekDay、WeekNight、WeekendDay、WeekendNight 和 TotalMinutes,并连接时间范围,例如在 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 DayTime
75 NightTime
但是由于任务 1 和 2 加起来是 285 分钟,因此只有第三个任务的前 185 分钟是常规白天:剩下的 45 分钟是加班时间,而后面的 75 分钟的夜间实际上是加班时间
解决方案
在这种方法中,第一个 CTE (properDates) 获取开始和停止日期时间,然后您不需要在查询中重复该公式。
第二个 CTE(splittedMinutes) 是获取与当前方法相同的数据,但第一个 CROSS APPLY 除外,它是拆分与午餐时间交叉的时间范围。第二个 CROSS APPLY 获取分钟数和 isWeekend 值。
在第三个 CTE(qualifiedMinutes) 中,我使用窗口分区来获取累积的分钟数并在应用时生成加班。
最后,我使用选择性 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
推荐阅读
- spring-data-neo4j - 对 Neo4j 4.0 版的可分页支持
- python - 是否可以加快 numpy 中非常大的数组的计算速度?
- uber-api - 如何获取服务器令牌以访问 Uber 的 API?
- html - 自动高度不适用于 HTML 中基于文本的 div
- android - Flutter MultiBlocProvider 性能
- javascript - webpack 和两个不同的来源网站
- google-apps-script - Apps 脚本 getEventById() 返回 null
- sql - 如何显示两个相反表的数据?
- rust - 为什么没有尾随分号的 match 语句会导致错误?
- excel - 将文本文件解析为列的选项