How to get the elapsed time between two dates ignoring a certain time range?

Question

I have a table with a DATETIME Start column and a DATETIME End column. I want to return the number of minutes between the beginning and the end (End is always after Start). Usually I just used "DateDiff ()", but this time I need to exclude a different date range. For example, from Tuesday at 9am to Wednesday at 6pm, every week should be ignored.

If the line has a beginning of Tuesday at 8 am and an end of Wednesday at 7 pm - the elapsed time should be two hours (120 minutes) - due to the ignored date range.

I am having problems with a decent way to do this, and my search on the Internet has not found what I'm looking for. Can someone help me?

Answer 1

Try the following:

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

It can be 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 

Answer 3

,

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 - ( SQL), DATEFIRST

d - c

Cast(Sign(a - b) + 1 as bit) 1, a b, 0

(x + ABS(x)) / 2 x , 0

, :

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

Answer 4

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

, , , , - . , , , , .

, . , . , .

, - 8:59:30, - 6:00:30. , , 9-6. datediff(minute, ...), : , , . , , .

. , , , , , . , , 1.

Please note that the solution also relies datefirston Sunday installation . This can be modified or made more portable if necessary.