Overlap duration causing double counting

Question

Overlap duration causing double counting

I am using SQL Server Management Studio 2008 to create queries. Reporting Services 2008 for creating reports.

I'm trying to figure this out after a couple of weeks and I hit a brick wall. I hope someone can come up with a solution, as my brain is now a mess.

I am currently developing a SQL query that will feed data to a Reporting Services report. The purpose of the report is to show the percentage of accessibility for first aid providers in the locations around the county in which we are located. The idea is that in each of our 20 locations there should only be one first helper providing cover.

All of this works great because the first mates in one place cover their coverage at the beginning and at the end of each period of coverage.

Lid overlap example:

| 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 an ideal world, the database they install their skin into would not allow them to do this, but its external database, which prevents us from making such changes to it. We can also create functions, stored procedures, table tables, etc.

The request itself should indicate the number of minutes that the first first aid spot was marked and then broken down by hours of the day. Any overlap in the cover must not end with the addition of an extra cover and must be merged. One person can be turned on at the same time, if it overlaps, then it should only account for one person.

Result:

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

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