持续时间重叠导致重复计算

Question

我正在使用SQL Server Management Studio 2008进行查询构建，使用Reporting Services 2008创建报告。

我已经尝试解决这个问题几周了，但一直没有头绪。希望有人能提供这个解决方案，因为我的思维已经混乱了。

我目前正在开发一个SQL查询，该查询将向Reporting Services报告提供数据。该报告的目的是显示我们所在县的各个地点的急救服务提供者可用性的百分比。其想法是每次在我们的20个地点中只有一个急救员提供服务。

这一切都很好运作，除了一个位置的急救员在每次提供服务的开始和结束时重叠覆盖。

重叠覆盖范例： | 位置 | 开始日期时间 | 结束日期时间 | | --------- | ------------------ | ------------------- | | Wick | 22/06/2015 09:00:00 | 22/06/2015 19:00:00 | | Wick | 22/06/2015 18:30:00 | 23/06/2015 09:00:00 | | Wick | 23/06/2015 09:00:00 | 23/06/2015 18:30:00 | | Wick | 23/06/2015 18:00:00 | 24/06/2015 09:00:00 |

在完美的世界里，他们设置服务的数据库不允许他们这样做，但它是外部开发的数据库，我们不能对其进行更改。 我们也不允许创建函数、存储过程、计数表等……

查询本身应返回每个位置提供急救服务的分钟数，然后按一天中的小时数进行分解。任何覆盖重叠都不应增加额外的服务，而应合并为一个服务。一次只能有一个人在工作，如果他们重叠，那么它应该只计算为一次服务。

范例输出：

+----------+---------------------+---------------------+----------+--------------+--------+-------+------+----------+
| 位置     |        开始时间     |         结束时间    | 时间差   | 可用性       | 星期   | 日期  | 小时 | 天数     |
+----------+---------------------+---------------------+----------+--------------+--------+-------+------+----------+
| WicK     | 2015年6月22日18:00:00| 2015年6月22日18:59:59|       59 |          100 | 星期一 |     1 |   18 |        0 |
| WicK     | 2015年6月22日18:30:00| 2015年6月22日18:59:59|       29 |           50 | 星期一 |     1 |   18 |        0 |
| WicK     | 2015年6月22日19:00:00| 2015年6月22日19:59:59|       59 |          100 | 星期一 |     1 |   19 |        0 |
+----------+---------------------+---------------------+----------+--------------+--------+-------+------+----------+

示例代码:

    DECLARE  
      @StartTime datetime,  
      @EndTime datetime, 
      @GivenDate datetime; 


 SET @GivenDate = '2015-06-22'; 
 SET @StartTime = @GivenDate + ' 00:00:00'; 
 SET @EndTime = '2015-06-23' + ' 23:59:59'; 

Declare @Sample Table
(
Location Varchar(50),
StartDate Datetime,
EndDate Datetime
)

Insert @Sample

Select
sta.location,
act.Start,
act.END

from emp,
con,
sta,
act

where 
emp.ID = con.ID
and con.location = sta.location
and SUBSTRING(sta.ident,3,2) in ('51','22')
and convert(varchar(10),act.start,111) between @GivenDate and @EndTime
and act.ACT= 18
group by sta.location,
act.Start,
act.END
order by 2

;WITH Yak (location, fromDt, toDt, maxDt,hourdiff) 
AS ( 
SELECT location, 
StartDate, 
/*check if the period of cover rolls onto the next hour */
    convert(datetime,convert(varchar(21),
    CONVERT(varchar(10),StartDate,111)+' '
    +convert(varchar(2),datepart(hour,StartDate))+':59'+':59'))
,
EndDate
,dateadd(hour,1,dateadd(hour, datediff(hour, 0, StartDate), 0))-StartDate
FROM @Sample

UNION ALL 

SELECT location, 
dateadd(second,1,toDt), 
dateadd(hour, 1, toDt),
maxDt,
hourdiff 
FROM Yak 
WHERE toDt < maxDt 
) ,

TAB1 (location, FROMDATE,TODATE1,TODATE) AS
(SELECT
location,
@StartTime,
convert(datetime,convert(varchar(21),
        CONVERT(varchar(10),@StartTime,120)+' '
        +convert(varchar(2),datepart(hour,@StartTime))+':59'+':59.999')),
@EndTime 

from @Sample

UNION ALL
SELECT 
location,
(DATEADD(hour, 1,(convert(datetime,convert(varchar(21),
        CONVERT(varchar(10),FROMDATE,120)+' '
        +convert(varchar(2),datepart(hour,FROMDATE))+':00'+':00.000')))))ToDate,
(DATEADD(hour, 1,(convert(datetime,convert(varchar(21),
        CONVERT(varchar(10),TODATE1,120)+' '
        +convert(varchar(2),datepart(hour,TODATE1))+':59'+':59.999'))))) Todate1,
TODATE
FROM TAB1 WHERE TODATE1 < TODATE
),
/*CTE Tab2 adds zero values to all possible hours between start and end dates */
TAB2 AS
(SELECT location, FROMDATE,
CASE WHEN TODATE1 > TODATE THEN TODATE ELSE TODATE1 END AS TODATE
FROM TAB1)

SELECT location, 
fromDt, 
/* Display MaxDT as start time if cover period goes into next dat */
CASE WHEN toDt > maxDt THEN maxDt ELSE toDt END AS toDt,
/* If the end date is on the next day find out the minutes between the start date and the end of the day or find out the minutes between the next day and the end date */
Case When ToDt > Maxdt then datediff(mi,fromDt,maxDt) else datediff(mi,FromDt,ToDt) end as TimeDiff,
Case When ToDt > Maxdt then round(datediff(S,fromDt,maxDt)/3600.0*100,0) else round(datediff(S,FromDt,ToDt)/3600.0*100.0,0) end as Availability,
/*Display the name of the day of the week*/
CASE WHEN toDt > maxDt THEN datename(dw,maxDt) ELSE datename(dw,fromDt) END AS DayN,
CASE WHEN toDt > maxDt THEN case when datepart(dw,maxDt)-1 = 0 then 7 else datepart(dw,maxDt)-1 end  ELSE case when datepart(dw,fromDt)-1 = 0 then 7 else  datepart(dw,fromDt)-1 END  end AS DayNo
,DATEPART(hour, fromDt) as Hour,
'0' as DayCount
FROM Yak 
where Case When ToDt > Maxdt then datediff(mi,fromDt,maxDt) else datediff(mi,FromDt,ToDt) end <> 0

group by location,fromDt,maxDt,toDt

Union all

SELECT
tab2.location, 
convert(varchar(19),Tab2.FROMDATE,120),
convert(varchar(19),Tab2.TODATE,120),
'0',
'0',
datename(dw,FromDate) DayN,
case when datepart(dw,FromDate)-1 = 0 then 7 else datepart(dw,FromDate)-1 end AS DayNo,
DATEPART(hour, fromDate) as Hour,
COUNT(distinct datename(dw,fromDate))
FROM TAB2

Where datediff(MINUTE,convert(varchar(19),Tab2.FROMDATE,120),convert(varchar(19),Tab2.TODATE,120)) > 0

group by location, TODATE, FROMDATE 

Order by 2

option (maxrecursion 0)

我尝试了以下论坛帖子，但在我的情况下它们都没有起作用： http://forums.teradata.com/forum/general/need-help-merging-consecutive-and-overlapping-date-spans 检查时间范围重叠，看门人问题[SQL] https://dev59.com/fUfRa4cB1Zd3GeqP8FJ4 计算实际停机时间，忽略日期/时间重叠 https://dev59.com/Kobca4cB1Zd3GeqPUE83 抱歉内容有点冗长，但我想尽可能提供详细信息。非常感谢您的帮助。谢谢。

- FatherBloopy

---

你的示例输出并不太有意义，它充满了重叠。我认为想法是要消除重叠。 - Bulat

如果结束时间与开始时间重叠，也许可以忽略结束时间？ - molleyc

Bulat - 这是当前数据的输出状态。目前我还没有找到消除重叠的方法。 - FatherBloopy

MolleyC - 目前我无法找出如何运行测试以查找重叠。理想情况下，如果发现时间重叠，应该采取最早的开始日期和最晚的结束日期。 - FatherBloopy

Answer 1

所以我想出的解决方案使用了临时表，你可以轻松地将其更改为CTE，这样就可以避免使用存储过程。

我尝试使用窗口函数查找重叠记录并获取最小和最大时间，问题在于当你有重叠链接时，例如09:00 - 09:10，09:05 - 09:15，09:11 - 09:20，所有从09:00到09:20的分钟都被覆盖了，但几乎不可能告诉09:00 - 09:10与09:11 - 09:20相关联，除非递归遍历结果直到到达链的底部。（希望这有意义）。

因此，我将所有日期范围扩展到开始日期和结束日期之间的每一分钟，然后您可以使用ROW_NUMBER()窗口函数捕获任何重复项，反过来，您可以使用它来查看有多少不同的人覆盖了相同的分钟。

CREATE TABLE dbo.dates
(
Location VARCHAR(64),
StartDate DATETIME,
EndDate DATETIME
);

INSERT INTO dbo.dates VALUES
('Wick','20150622 09:00:00','20150622 19:00:00'),
('Wick','20150622 18:30:00','20150624 09:00:00'),
('Wick','20150623 09:00:00','20150623 18:30:00'),
('Wick','20150623 18:00:00','20150624 09:00:00'),
('Wick','20150630 09:00:00','20150630 09:30:00'),
('Wick','20150630 09:00:00','20150630 09:45:00'),
('Wick','20150630 09:10:00','20150630 09:25:00'),
('Wick','20150630 09:35:00','20150630 09:55:00'),
('Wick','20150630 09:57:00','20150630 10:10:00');

SELECT ROW_NUMBER() OVER (PARTITION BY Location ORDER BY StartDate) [Id],
Location,
StartDate,
EndDate
INTO dbo.overlaps
FROM dbo.dates;

SELECT TOP 10000 N=IDENTITY(INT, 1, 1)
INTO dbo.Num
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;

SELECT 0 [N] INTO dbo.Numbers;

INSERT INTO dbo.Numbers SELECT * FROM dbo.Num;

SELECT  [Location]      = raw.Location,
        [WorkedDate]    = CAST([MinuteWorked] AS DATE),
        [DayN]          = DATENAME(WEEKDAY, [MinuteWorked]),
        [DayNo]         = DATEPART(WEEKDAY, [MinuteWorked]) -1,
        [Hour]          = DATEPART(HOUR, [MinuteWorked]),
        [MinutesWorked] = SUM(IIF(raw.[Minutes] = 1, 1, 0)),
        [MaxWorkers]    = MAX(raw.[Minutes])
FROM
(
SELECT
  o.Location,
  DATEADD(MINUTE, n.N, StartDate) [MinuteWorked],
  ROW_NUMBER() OVER (PARTITION BY o.Location, DATEADD(MINUTE, n.N, StartDate) ORDER BY DATEADD(MINUTE, n.N, StartDate)) [Minutes]
FROM dbo.overlaps o
INNER JOIN dbo.Numbers n ON n.N < DATEDIFF(MINUTE, StartDate, EndDate)
) raw
GROUP BY
    raw.Location,
    CAST([MinuteWorked] AS DATE),
    DATENAME(WEEKDAY, [MinuteWorked]),
    DATEPART(WEEKDAY, [MinuteWorked]) - 1,
    DATEPART(HOUR, [MinuteWorked])

以下是部分结果：

Location    WorkedDate  DayN        DayNo   Hour    MinutesWorked   MaxWorkers
Wick        2015-06-24  Wednesday   3       8       60              2
Wick        2015-06-30  Tuesday     2       9       58              3
Wick        2015-06-30  Tuesday     2       10      10              1

这里是代码片段

- Jim Jimson