How To Get Elapsed Time Between Two Dates, Ignoring Some Time Range?

Question

I have a table with a DATETIME column 'Start' and a DATETIME column 'End'. I want to return the number of minutes between the start and the end (End is always after than Start). Usually I'd just use 'DateDiff()' but this time I need to exclude another date range. For example - From Tuesday at 9am until Wednesday at 6pm, of each week, should be ignored.

If a row has a Start of Tuesday at 8am and an End of Wednesday at 7pm - the elapsed time should be two hours (120 minutes) - because of the ignored date range.

I'm having trouble coming up with a decent way of doing this and my searching online hasn't found quite what I'm looking for. Can someone help me along?

Answer 1

-- excluded range (weekday numbers run from 1 to 7)
declare @x datetime = /*ignore*/ '1900012' + /*start day # and time*/ '3 09:00am';
declare @y datetime = /*ignore*/ '1900012' + /*  end day # and time*/ '4 06:00pm';

-- normalize date to 1900-01-21, which was a Sunday
declare @s datetime =
    dateadd(day, 19 + datepart(weekday, @start), cast(cast(@start as time) as datetime));
declare @e datetime =
    dateadd(day, 19 + datepart(weekday, @end), cast(cast(@end as time) as datetime));

-- split range into two parts, one before @x and the other after @y
-- each part collapses to zero when @s and @e respectively fall between @x and @y
select (
    datediff(second, -- diff in minutes would truncate so count seconds
        case when @s < @x then @s else @x end, -- minimum of @s, @x
        case when @e < @x then @e else @x end  -- minimum of @e, @x
    ) +
    datediff(second,
        case when @s > @y then @s else @y end, -- maximum of @s, @y
        case when @e > @y then @e else @y end  -- maximum of @e, @y
    )
) / 60; -- convert seconds to minutes, truncating with integer division

I glanced at the earlier answers and I thought that surely there was something more straightforward and elegant. Perhaps this is easier to understand and one clear advantage over some solutions is that it's trivial to change the excluded range and that range doesn't have to be limited to a single day.

I'm assuming that your dates never span more than one regular calendar week. It wouldn't be too difficult to extend it to handle more though. One approach would be to handle starting and ending partial weeks plus the full weeks in the middle.

Imagine that your start time is 8:59:30am and your end time is 6:00:30pm. In such a case I'm figuring that you'd want to accumulate the half minutes on each side to get a full minute in total after subtracting the 9-6 block. If you use datediff(minute, ...) you would be truncating the partial minutes and never get the chance to add them together: so that's why I count seconds and then divide by sixty at the end. Of course, if you're only dealing in whole minutes then you won't need to do it that way.

I've chosen my reference date somewhat arbitrarily. At first I thought it might possibly be handy to look at a real and convenient date on the calendar but ultimately it only really matters that it falls on a Sunday. So I settled on the first Sunday falling on a date ending in the digit 1.

Note that the solution also relies on datefirst being set to Sunday. That could be tweaked or made more portable if necessary.

Answer 2

Putting the additional constraint that there can only be one excluded range between any two date

CREATE TABLE worktable (
  _Id INT
, _Start DATETIME
, _End DATETIME
);
INSERT INTO worktable VALUES
  (1, '2015-11-09 00:00:00', '2015-11-09 00:45:00') -- Start and End before excluded range
, (2, '2015-11-09 00:00:00', '2015-11-11 21:45:00') -- Start before, End after
, (3, '2015-11-09 00:00:00', '2015-11-10 21:00:00') -- Start before, End between
, (4, '2015-11-10 10:00:00', '2015-11-11 10:00:00') -- Start between, End between
, (5, '2015-11-10 10:00:00', '2015-11-11 21:45:00') -- Start between, End after

With getDates As (
  SELECT _Id
       , a = _Start
       , b = _End
       , c = DATEADD(hh, 9
           , DATEADD(DAY,DATEDIFF(DAY, 0, _Start) / 7 * 7
           + 7 * Cast(Sign(1 - DatePart(dw, _Start)) + 1 as bit), 1))
       , d = DATEADD(hh, 18
           , DATEADD(DAY,DATEDIFF(DAY, 0, _Start) / 7 * 7
           + 7 * Cast(Sign(1 - DatePart(dw, _Start)) + 1 as bit), 2))
  FROM   worktable
), getDiff As (
  SELECT c_a = DATEDIFF(mi, a, c)
       , c_b = DATEDIFF(mi, b, c)
       , b_d = DATEDIFF(mi, d, b)
       , a, b, c, d, _id
  FROM   getDates
)
Select _id
     , (c_a + ABS(c_a)) / 2
     - (c_b + ABS(c_b)) / 2
     + (b_d + ABS(b_d)) / 2
FROM   getDiff;

c is the date of the first Tuesday after the start date (Find the next occurance of a day of the week in SQL) you may need to adjust the last value depending from DATEFIRST

d is the date of the first Wednesday after the start date in the same week of c

Cast(Sign(a - b) + 1 as bit) is 1 if a is more than or equal b, 0 otherwise

(x + ABS(x)) / 2 is x if not negative, otherwise 0

Given that the formula to get the elapsed time with the excluded range is:

 + (Exclusion Start - Start) If (Start < Exclusion Start)
 - (Exclusion Start - End) If (End < Exclusion Start) 
 + (End - Exclusion End) If (Exclusion End < End)

Answer 3

Try This:

--total time span to calculate difference
DECLARE @StartDate DATETIME = '2015-11-10 8:00:00AM',
        @EndDate DATETIME = '2015-11-11 7:00:00PM'

--get the day of week (-1 because sunday is counted as first weekday)
DECLARE @StartDayOfWeek INT = (SELECT DATEPART(WEEKDAY, @StartDate)) -1
DECLARE @EndDayOfWeek INT = (SELECT DATEPART(WEEKDAY, @EndDate)) -1

--set the time span to exclude
DECLARE @InitialDOWToExclude TINYINT = 2
DECLARE @InitialTODToExclude VARCHAR(100) = '9:00:00 AM'

DECLARE @EndDOWToExclude TINYINT = 3
DECLARE @EndTODToExclude VARCHAR(100) = '6:00:00 PM'

--this will be the final output in hours
DECLARE @ElapsedHours INT = (SELECT DATEDIFF(HOUR, @StartDate, @EndDate))

DECLARE @WeeksBetween INT = (SELECT DATEDIFF(WEEK, @StartDate, @EndDate))
DECLARE @Iterator INT = 0
WHILE (@Iterator <= @WeeksBetween)
BEGIN
    DECLARE @InitialDaysBetween INT = @StartDayOfWeek - @InitialDOWToExclude
    DECLARE @StartDateToExclude DATETIME = (SELECT DATEADD(DAY, @InitialDaysBetween, DATEADD(WEEK, @Iterator, @StartDate)))
    SET @StartDateToExclude =CAST(DATEPART(YEAR, @StartDateToExclude) AS VARCHAR(100))
                              + CAST(DATEPART(MONTH, @StartDateToExclude) AS VARCHAR(100))
                              + CAST(DATEPART(DAY, @StartDateToExclude) AS VARCHAR(100))
                              + ' '
                              + CAST(@InitialTODToExclude AS VARCHAR(100))

    DECLARE @EndDaysBetween INT = @EndDayOfWeek - @EndDOWToExclude 
    DECLARE @EndDateToExclude DATETIME = (SELECT DATEADD(DAY, @EndDaysBetween, DATEADD(WEEK, @Iterator, @EndDate)))
    SET @EndDateToExclude =CAST(DATEPART(YEAR, @EndDateToExclude) AS VARCHAR(100))
                              + CAST(DATEPART(MONTH, @EndDateToExclude) AS VARCHAR(100))
                              + CAST(DATEPART(DAY, @EndDateToExclude) AS VARCHAR(100))
                              + ' '
                              + CAST(@EndTODToExclude AS VARCHAR(100))

    SET @ElapsedHours = @ElapsedHours - DATEDIFF(HOUR, @StartDateToExclude, @EndDateToExclude)

    SET @Iterator = @Iterator + 1
END

SELECT @ElapsedHours

Answer 4

This might get you pretty close..

DECLARE @Table1 TABLE ([Id] INT, [Start] DATETIME, [End] DATETIME)
INSERT INTO @Table1 VALUES
(1, '2015-11-08 00:00:00', '2015-11-10 21:45:38'),
(2, '2015-11-09 00:00:00', '2015-11-11 21:45:38')
;

-- hours to exclude
WITH excludeCTE AS 
(
    SELECT * 
    FROM (VALUES('Tuesday', 9, 0), ('Wednesday', 0, 0)) AS T([Day], [Hour], [Amount])
    UNION ALL 
    SELECT [Day], [Hour] + 1, [Amount]  
    FROM excludeCTE
    WHERE  ([Day] = 'Tuesday' AND [Hour] < 23) OR ([Day] = 'Wednesday' AND [Hour] < 18)
),
-- all hours between start and end
dateCTE AS
(
    SELECT  [Id], 
            [Start], 
            [End], 
            DATENAME(weekday, [Start])[Day],
            DATENAME(hour, [Start])[Hour]
    FROM    @Table1 t
    UNION ALL 
    SELECT  cte.[Id], 
            DATEADD(HOUR, 1, cte.[Start]), 
            cte.[End], 
            DATENAME(weekday, DATEADD(HOUR, 1, cte.[Start]))[Day],
            DATENAME(hour, DATEADD(HOUR, 1, cte.[Start]))[Hour]
    FROM    @Table1 t
            JOIN dateCTE cte ON t.Id = cte.Id
    WHERE   DATEADD(HOUR, 1, cte.[Start]) <= t.[End]

)
SELECT  t.[Id], 
        t.[Start], 
        t.[End], 
        SUM(COALESCE(e.[Amount], 1)) [Hours]
FROM    @Table1 t
        INNER JOIN dateCTE d ON t.[Id] = d.[Id]
        LEFT JOIN excludeCTE e ON d.[Day] = e.[Day] AND d.[Hour] = e.[Hour]
GROUP BY t.[Id], 
        t.[Start], 
        t.[End]
OPTION (MAXRECURSION 0) -- allow more than 100 hours