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

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) 

 [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

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.