I am trying to calculate the number of hours of operation per week for each facility in a region. The part I am struggling with is that there are multiple programs each day that overlap which contribute to the total hours.

Here is a sample of the table I am working with:

I am hoping to get:

I've tried using SUM DATEDIFF with some WHERE statements but couldn't get them to work. What I have found is how to identify the overlapping ranges(Detect overlapping date ranges from the same table), but not how to sum the difference to get the desired outcome of total non-overlapping hours of operation.

CodePudding user response：

Believe you are trying to identify the total hours of operation for each location. Now because some programs can overlap, you want to rule those out. To do this, I generate a tally table of each possible 15 minute increment in the date and then count the time periods that have a program operating

Identify Total Hours of Operation per Date

DROP TABLE IF EXISTS #OperationSchedule
CREATE TABLE #OperationSchedule (ID INT IDENTITY(1,1),Location CHAR(1),Program INT,OpDate DATE,OpStart TIME(0),OpEnd TIME(0))
INSERT INTO #OperationSchedule
VALUES ('a',1,'09-22-21','14:45:00','15:45:00')
,('a',2,'09-22-21','15:30:00','16:30:00')
,('b',88,'09-22-21','10:45:00','12:45:00')
,('b',89,'09-22-21','10:45:00','14:45:00');

/*1 row per 15 minute increment in a day*/
;WITH cte_TimeIncrement AS (
    SELECT StartTime = CAST('00:00' AS TIME(0))
    UNION ALL
    SELECT DATEADD(minute,15,StartTime)
    FROM cte_TimeIncrement
    WHERE StartTime < '23:45'
),
/*1 row per date in data*/
cte_DistinctDate AS (
    SELECT OpDate
    FROM #OperationSchedule
    GROUP BY OpDate
),
/*Cross join to generate 1 row for each time increment*/
cte_DatetimeIncrement AS (
SELECT *
FROM cte_DistinctDate
CROSS JOIN cte_TimeIncrement
)

/*Join and count each time interval that has a match to identify times when location is operating*/
SELECT Location
    ,A.OpDate
    ,HoursOfOperation = CAST(COUNT(DISTINCT StartTime) * 15/60.0 AS Decimal(4,2))
FROM cte_DatetimeIncrement AS A
INNER JOIN #OperationSchedule AS B
    ON A.OpDate = B.OpDate
    AND A.StartTime >= B.OpStart
    AND A.StartTime < B.OpEnd
GROUP BY Location,A.OpDate

CodePudding user response：

Here is an alternative method without having to round to nearest 15 minute increments:

Declare @OperationSchedule table (
        ID       int Identity(1, 1)
      , Location char(1)
      , Program  int
      , OpDate   date
      , OpStart  time(0)
      , OpEnd    time(0)
        );

 Insert Into @OperationSchedule (Location, Program, OpDate, OpStart, OpEnd)
 Values ('a',  1, '09-22-21', '14:45:00', '15:45:00')
      , ('a',  2, '09-22-21', '15:30:00', '16:30:00')
      , ('b', 88, '09-22-21', '10:45:00', '12:45:00')
      , ('b', 89, '09-22-21', '10:45:00', '14:45:00')
      , ('c', 23, '09-22-21', '12:45:00', '13:45:00')
      , ('c', 24, '09-22-21', '14:45:00', '15:15:00')
      , ('3', 48, '09-22-21', '09:05:00', '13:55:00')
      , ('3', 49, '09-22-21', '14:25:00', '15:38:00')
      ;

   With overlappedData
     As (
 Select *
      , overlap_op = lead(os.OpStart, 1, os.OpEnd) Over(Partition By os.Location Order By os.ID)
   From @OperationSchedule os
        )
 Select od.Location
      , start_date = min(od.OpStart)
      , end_date = max(iif(od.OpEnd < od.overlap_op, od.OpEnd, od.overlap_op))
      , hours_of_operation = sum(datediff(minute, od.OpStart, iif(od.OpEnd < od.overlap_op, od.OpEnd, od.overlap_op)) / 60.0)
   From overlappedData                  od
  Group By
        od.Location;