SQL calculate a time slice for each day for a datetime range

Question

Let's say I have a datetime range defined in SQL:

declare @STARTTIME datetime = '2014-09-10 13:00'
declare @ENDTIME datetime = '2014-09-12 13:00'

and I have a time slice:

declare @sliceStart time = '12:00'
declare @sliceEnd time = '16:00'

(see the picture)

I'd like to calculate how long does this slice overlap in each day of the duration of the time range. In the first day the overlap was for 3 hours (from 13 to 16), in the second day it was for 4 hours and in the final day only for 1 hour.

The ranges are in fact inside a table and there are millions of them and I'd like to calculate the total duration considering these time slices. In my example there is only one datetime range, but my current best guess is that I'd need to create a function out of this that I'll apply to each row, hence the example shows directly the range start and end as variables.

Answer 1

I know there could be a more efficient way, but you can try the following. Here is a Fiddle of it.

DECLARE @STARTTIME DATETIME
SET     @STARTTIME = '2014-09-10 13:00'
DECLARE @ENDTIME DATETIME
SET     @ENDTIME = '2014-09-12 13:00'

DECLARE @sliceStart TIME
SET     @sliceStart = '12:00'
DECLARE @sliceEnd TIME
SET     @sliceEnd = '16:00'

;WITH SlicesCTE AS
(
    SELECT @STARTTIME AS StartTime
    UNION ALL SELECT DATEADD(hh,1,StartTime) AS StartTime 
              FROM   SlicesCTE 
              WHERE  StartTime < @ENDTIME
)
SELECT CONVERT(DATE,StartTime),
       SUM
       (
           CASE 
           WHEN CONVERT(TIME,StartTime) >= @sliceStart 
                AND CONVERT(TIME,StartTime) < @sliceEnd 
           THEN 1 
           ELSE 0 
           END
       ) 
FROM   SlicesCTE 
GROUP BY CONVERT(DATE,StartTime)

Answer 2

    [sss_M_M_M_M_M_M_M_M_eee]
|- - - -|- - - -|- - - -|- - - -|

Consider each "range" as a collection of:

• a start period (sss)
• an end period (eee)
• a middle period duration of zero or more complete days (_M_M_M_)

And:

1. A slice might be before, or overlap, or be within the start period
2. A slice might be within, or overlap, or be after the end period
3. A slice will occur for each day in the middle

The start period is known by the time of the "range" start date , and the end period is known by the time of the "range" end date. If a "range" start date and end date are different, the middle period is > 0 and is calculated by (DATEDIFF(DAY, starttime, endtime) - 1)

In the query below, by using cross apply, we are able to give some needed calculations column aliases that can be reused elsewhere in the query. Hence the second cross apply is simplified by using aliases established in the first cross apply.

The case expression in the second cross apply caters for the conditions 1, 2, & 3. noted earlier:

if range start & end are the same day             (middle = 0)
    then calculate any overlap of slice and range            
else                                              (middle > 0)
    1 overlap for each day in the middle
  + any overlap in the start period (1 or less)
  + any overlap in the end period   (1 or less)

Note: To cater for partial hours in the slice and/or range start times/end times that don't align to full hours minutes are used as the unit of measure; hence the slice is likewise treated as minutes but from this we are able to work back to hours if needed and the number of "overlaps" (really "overlap equivalents") can also be calculated.

Sample results:

| DATE_START |   DATE_END |TIME_START| TIME_END | SLICE_MIN | OVERLAP_MIN | OVERLAPS | OVERLAP_HOURS |
|------------|------------|----------|----------|-----------|-------------|----------|---------------|
| 2014-09-10 | 2014-09-12 | 13:00:00 | 13:00:00 |       240 |         480 |        2 |             8 |
| 2014-09-10 | 2014-09-12 | 12:00:00 | 16:00:00 |       240 |         720 |        3 |            12 |
| 2014-09-10 | 2014-09-12 | 13:15:00 | 17:00:00 |       240 |         645 |   2.6875 |         10.75 |
| 2014-08-10 | 2014-09-12 | 13:15:00 | 17:00:00 |       240 |        8085 |  33.6875 |        134.75 |

Query: (assumes "ranges" exist in a table being compared to a slice)

DECLARE @sliceStart TIME
SET     @sliceStart = '12:00'
DECLARE @sliceEnd TIME
SET     @sliceEnd = '16:00'

SELECT
      ca1.*
    , ca2.*
    , ca2.overlap_min / (ca1.slice_min * 1.0)  AS overlaps
    , ca2.overlap_min / 60.0                   AS overlap_hours
FROM Ranges
cross apply (
             select
                    CAST(starttime AS date)                  AS date_start
                  , CAST(endtime AS date)                    AS date_end
                  , CAST(starttime AS time)                  AS time_start
                  , CAST(endtime AS time)                    AS time_end
                  , DATEDIFF(MINUTE, @slicestart, @sliceend) AS slice_min
            ) ca1
cross apply (
             select
                  (CASE
                        WHEN ca1.date_start = ca1.date_end THEN 
                        (CASE
                              WHEN @sliceend > ca1.time_start AND @slicestart < ca1.time_end THEN DATEDIFF(MINUTE,
                                  (CASE
                                        WHEN @slicestart < ca1.time_start THEN ca1.time_start ELSE @slicestart END),
                                  (CASE
                                        WHEN @sliceend > ca1.time_end THEN ca1.time_end ELSE @sliceend END)
                                  )
                              ELSE 0 END)
                        ELSE (DATEDIFF(DAY, starttime, endtime) - 1) * ca1.slice_min
                        + (CASE
                              WHEN @sliceend > ca1.time_start THEN DATEDIFF(MINUTE, (CASE
                                            WHEN @slicestart < ca1.time_start THEN ca1.time_start ELSE @slicestart END),
                                      @sliceend)
                              ELSE 0 END)
                        + (CASE
                              WHEN @slicestart < ca1.time_end THEN DATEDIFF(MINUTE, @slicestart,
                                      (CASE
                                            WHEN @sliceend > ca1.time_end THEN ca1.time_end ELSE @sliceend END)
                                      )
                              ELSE 0
                         END)
                    END) AS overlap_min
             ) as ca2
;

See this SQLFiddle demo

---

By the way, this method is a variation on calculating working days

Answer 3

Here is a method. Count the number of hours of overlap on the first day and last day and then add in the complete days in-between.

SQL Server makes this a bit harder than necessary, because it is hard to add times. Here is the code using hours as the difference:

select t.*,
       (case when cast(starttime as date) = cast(endtime as date)
             then (case when sliceend > cast(starttime as time) and
                             slicestart < cast(endtime as time)
                        then datediff(hour,
                                      (case when slicestart < cast(starttime as time) then cast(starttime as time) else slicestart end),
                                      (case when sliceend > cast(endtime as time) then cast(endtime as time) else sliceend end)
                                     )
                        else 0
                   end)
             then datediff(hour,
                           (case when slicestart < cast(starttime as time) then cast(starttime as time) else slicestart end),
                           (case when sliceend > cast(endtime as time) then cast(endtime as time) else sliceend end)
                          )
             else (datediff(day, starttime, endtime) - 1) * datediff(hour, slicestart, sliceend) +
                  (case when sliceend > cast(starttime as time)
                        then datediff(hour, (case when slicestart < cast(starttime as time) then cast(starttime as time) else slicestart end),
                                      sliceend)
                        else 0
                   end) +
                  (case when slicestart < cast(endtime as time)
                        then datediff(hour, slicestart,
                                      (case when sliceend > cast(endtime as time) then cast(endtime as time) else sliceend end)
                                     )
                        else 0
                   end)
       end) as hours
from (select cast('2014-09-10 13:00' as datetime) as starttime,
             cast('2014-09-12 13:00' as datetime) as endtime,
             cast('12:00' as time) as slicestart,
             cast('16:00' as time) as sliceend
     ) t;

Here is a SQL Fiddle with the query.