Duration overlap causing double counting

Question

I'm using SQL Server Management Studio 2008 for query creation. Reporting Services 2008 for report creation.

I have been trying to work this out over a couple of weeks and I have hit a brick wall. I’m hoping someone will be able to come up with the solution as right now my brain has turned to mush.

I am currently developing an SQL query that will be feeding data through to a Reporting Services report. The aim of the report is to show the percentage of availability for first aid providers within locations around the county we are based in. The idea is that there should be only one first aider providing cover at a time at each of our 20 locations.

This has all been working fine apart from the first aiders at one location have been overlapping their cover at the start and end of each period of cover.

Example of cover overlap:

| 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)

I have tried the following forum entries but they haven't worked in my case: 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

Sorry for being so lengthy but I thought I would try to give you as much detail as possible. Any help will be really appreciated. Thank you.

Your example output does not make much sense, it is full of overlaps. I though idea was to remove overlaps. — Bulat, Aug 07 '15 at 10:14
If an End Time overlaps a Start Time, perhaps ignore the End Time? — molleyc, Aug 07 '15 at 15:17
Bulat - That is the current output of data as it stands. I haven't found a way to remove overlaps at this current time. — FatherBloopy, Aug 10 '15 at 07:18
MolleyC - At the moment I can't work out how to run a test to find the overlaps. Ideally it would be something like if a time overlap is found, take the earliest start date and the latest end date. — FatherBloopy, Aug 10 '15 at 07:19

score 0 · Answer 1 · answered Sep 29 '18 at 15:50

So the solution I came up with uses temp tables, which you can easily change to be CTEs so you can avoid using a stored procedure.

I tried working with window functions to find overlapping records and get the min and max times, the issue is where you have overlap chaining e.g. 09:00 - 09:10, 09:05 - 09:15, 09:11 - 09:20, so all minutes from 09:00 to 09:20 are covered, but it's almost impossible to tell that 09:00 - 09:10 is related to 09:11 - 09:20 without recursing through the results until you get to the bottom of the chain. (Hopefully that makes sense).

So I exploded out all of the date ranges into every minute between the StartDate and EndDate, then you can use the ROW_NUMBER() window function to catch any duplicates, which in turn you can use to see how many different people covered the same minute.

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])

Here's a subset of the results:

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 the fiddle

Duration overlap causing double counting

1 Answers1