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

Question

我使用 SQL Server Management Studio 2008 来创建查询。用于创建报告的 Reporting Services 2008。

几周来我一直在尝试解决这个问题，但遇到了困难。我希望有人能够想出解决方案，因为现在我的大脑已经变得一团糟。

我目前正在开发一个 SQL 查询，它将向 Reporting Services 报告提供数据。该报告的目的是显示我们所在县周围地点的急救人员可用百分比。我们的想法是，在我们的 20 个地点中，每个地点一次应该只有一名急救人员提供掩护。

除了某个地点的急救人员在每个掩护周期开始和结束时重叠掩护外，一切都运行良好。

封面重叠示例:

| Location |     start_date      |      end_date       |+----------+---------------------+---------------------+| 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 |+----------+---------------------+---------------------+

In a perfect world the database that they set their cover in wouldn’t allow them to do this but it’s an externally developed database that doesn’t allow us to make changes like that to it. We also aren’t allowed to create functions, stored procedures, tally tables etc…

The query itself should return the number of minutes that each location has had first aid cover for, then broken down into hours of the day. Any overlap in cover shouldn’t end up adding additional cover and should be merged. One person can be on at a time, if they overlap then it should only count as one person lot of cover.

Example Output:

+----------+---------------------+---------------------+----------+--------------+--------+-------+------+----------+| Location |       fromDt        |        toDt         | TimeDiff | Availability |  DayN  | DayNo | Hour | DayCount |+----------+---------------------+---------------------+----------+--------------+--------+-------+------+----------+| WicK     | 22/06/2015 18:00:00 | 22/06/2015 18:59:59 |       59 |          100 | Monday |     1 |   18 |        0 || WicK     | 22/06/2015 18:30:00 | 22/06/2015 18:59:59 |       29 |           50 | Monday |     1 |   18 |        0 || WicK     | 22/06/2015 19:00:00 | 22/06/2015 19:59:59 |       59 |          100 | Monday |     1 |   19 |        0 |+----------+---------------------+---------------------+----------+--------------+--------+-------+------+----------+

Example Code:

    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

Checking for time range overlap, the watchman problem [SQL]

Calculate Actual Downtime ignoring overlap in dates/times

很抱歉这么长，但我想我会尽力为您提供尽可能详细的信息。任何帮助将不胜感激。谢谢。

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 相关，无需递归结果，直到到达链的底部。 (希望这是有道理的)。

因此，我将所有日期范围分解为 StartDate 和 EndDate 之间的每一分钟，然后您可以使用 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

Here's fiddle