在一个时间范围内产生时间岛.SQL Server

Question

请帮我，我有一个奇怪的SQL问题。

因此，如果你假设标准办公时间在9:00到17:00之间。

我有一个休息时间列表，我想把这些休息时间添加到我的工作日中，并在我有空的时候返回一组时间。

这个SQL显示了我的工作时间、休息时间以及我希望输出的内容：

DECLARE @WorkingHours TABLE
(StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)
insert @WorkingHours select '09:00',  '17:00', '2018-08-15', 0;
DECLARE @Breaks TABLE
(StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)
insert @Breaks select '11:30',  '12:30', '2018-08-15', 1;
insert @Breaks select '12:00',  '13:00', '2018-08-15', 1;
insert @Breaks select '15:00',  '16:00', '2018-08-15', 1;
insert @Breaks select '15:25',  '15:55', '2018-08-15', 1;
insert @Breaks select '09:50',  '10:05', '2018-08-15', 1;
insert @Breaks select '15:50',  '16:05', '2018-08-15', 1;
DECLARE @Output TABLE
(StartTime TIME, EndTime TIME, EventDate date)
insert @Output select '09:00',  '09:50', '2018-08-15';
insert @Output select '10:05',  '11:30', '2018-08-15';
insert @Output select '13:00',  '15:00', '2018-08-15';
insert @Output select '16:05',  '17:00', '2018-08-15';
SELECT * FROM @Output

这是我最接近的一次，但它不处理重叠的休息，也不处理休息中的休息。

DECLARE @Final TABLE
(StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)
INSERT INTO @Final
SELECT * FROM @WorkingHours
UNION
SELECT * FROM @Breaks
SELECT CASE WHEN t1.IsBreak = 0 THEN t1.StartTime  
ELSE t1.EndTime 
END AS StartTime , 
CASE WHEN LEAD(t1.EventDate) OVER 
( ORDER BY t1.EventDate, 
t1.[StartTime]
) = t1.EventDate THEN 
coalesce(Lead(t1.StartTime) OVER
( ORDER BY t1.EventDate, 
t1.[StartTime]), 
'17:00'
) 
ELSE '17:00' 
END AS EndTime, 
t1.EventDate 
FROM  @Final  t1 
INNER 
JOIN @Final t2 
ON t1.EventDate = t2.EventDate 
AND t2.IsBreak = 0 

任何人能提供的帮助都将不胜感激。

Answer 1

我不知道这是否是一个过度思考，但它应该处理的任何中断组合

DECLARE @WorkingHours TABLE
(StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)
insert @WorkingHours select '09:00',  '17:00', '2018-08-15', 0;
DECLARE @Breaks TABLE
(StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)
insert @Breaks select '11:30',  '12:30', '2018-08-15', 1;
insert @Breaks select '12:00',  '13:00', '2018-08-15', 1;
insert @Breaks select '15:00',  '16:00', '2018-08-15', 1;
insert @Breaks select '15:25',  '15:55', '2018-08-15', 1;
insert @Breaks select '09:50',  '10:05', '2018-08-15', 1;
insert @Breaks select '15:50',  '16:05', '2018-08-15', 1;
DECLARE @Output TABLE
(StartTime TIME, EndTime TIME, EventDate date)
insert @Output select '09:00',  '09:50', '2018-08-15';
insert @Output select '10:05',  '11:30', '2018-08-15';
insert @Output select '13:00',  '15:00', '2018-08-15';
insert @Output select '16:05',  '17:00', '2018-08-15';
SELECT * FROM @Output
DECLARE @DATE as date = '20180815';
;WITH cHours as (SELECT 0 H
UNION ALL 
SELECT H + 1 FROM cHours WHERE H < 23),
cMins as (SELECT 0 M
UNION ALL 
SELECT M + 1 FROM cMins WHERE M < 59),
cMinDay as (SELECT CAST(dateadd(minute,H*60 + M,0) as time) aTime FROM cHours CROSS JOIN cMins),
cActiveMins as (
SELECT aTime ,  
CASE WHEN 
EXISTS(SELECT 0 FROM @WorkingHours w WHERE w.StartTime <= a.aTime  and a.aTime < w.EndTime)
AND NOT EXISTS(SELECT 0 FROM @Breaks  b WHERE b.StartTime <= a.atime and a.atime < b.EndTime)
THEN 1 ELSE 0 
END WorkFlag 
FROM cMinDay a 
),
cIsland as (SELECT *,(row_number() OVER (ORDER BY atime)) -    (row_number() OVER (ORDER BY workflag, atime)) x FROM   cActiveMins)                                                                        
select workflag, MIN(atime),dateadd(minute,1,MAX(atime)) from cIsland GROUP BY workflag,x  having workflag=1;

Answer 2

可以通过使用StartTime < EndTime过滤上次查询的结果来获得所需的输出，例如cte：

WITH cte AS ( SELECT     CASE
WHEN t1.IsBreak = 0 THEN t1.StartTime
ELSE t1.EndTime
END   AS StartTime
, CASE WHEN LEAD(t1.EventDate) OVER ( ORDER BY t1.EventDate, t1.[StartTime]) = t1.EventDate 
THEN COALESCE(LEAD(t1.StartTime) OVER( ORDER BY t1.EventDate, t1.[StartTime]), '17:00') 
ELSE 
'17:00' 
--           (
--               SELECT MAX(EndTime)
--               FROM @Final
--               WHERE IsBreak = 0
--                     AND [@Final].EventDate = t1.EventDate
--           )
END AS EndTime
, t1.EventDate
FROM       @Final t1
INNER JOIN @Final t2
ON t1.EventDate = t2.EventDate
AND t2.IsBreak   = 0 )
SELECT *
FROM   cte
WHERE  StartTime < EndTime 

Answer 3

我最近解决了一个类似的问题。

将StartDate和EndDate解压缩到一列中。然后，StartDate或EndDate没有精确显示2次的任何地方都意味着你没有连续的数据，所以它要么是开始或结束时的开放间隔，要么是丢失的数据点(最外面的开放间隔之间的开放间隔(。

干杯。

; WITH dv AS (
SELECT *
FROM #dv
UNPIVOT ( DateDA FOR dATES IN (StartDate, EndDate)
), dv2 AS (
SELECT j1.datavalueid AS [j1_datavalueid],
j1.startdate AS [j1_startdate],
j1.EndDate AS [j1_EndDate],
j2.datavalueid AS [j2_datavalueid],
j2.startdate AS [j2_startdate],
j2.EndDate AS [j2_EndDate]
FROM
dv j1
FULL OUTER JOIN dv j2
ON  j1.StartDate = j2.EndDate
WHERE (j1.DataValueID IS NULL
OR j2.DataValueID IS NULL)
)
SELECT
COALESCE(j1_datavalueid, j2_datavalueid) AS datavalueid,
j2_endDate AS startdate,
j1_startdate AS enddate
FROM
dv2
ORDER BY COALESCE(j2_enddate, j1_enddate)