Datediff in mins, taking into account custom bank holidays, maintenance days and working hours

I have a table (table1) which includes columns [deadline] and [completion], both of datatype datetime.

I need to calculate the date diff in minutes between [deadline] and [completion].

For example:

• deadline = 2018-11-22 09:05
• completion = 2018-11-22 9:10

Result needs to be 5.

However, I also need to factor in bank holidays, using a previously generated custom table, say [tableBH], which has [date] [bank holiday name].

Also, there is another table a maintenance table [tableMaintenance] with columns [date] [maintenance reason].

Lastly, I working hours monday to friday are 0900 to 1900, and on saturday 1100 – 1500.

I need the datediff, ideally in minutes, to take into account non-working hours, non-working days, and bank holidays.

Ideally by way of a function. Can anyone give pointers as to where to start?

My current script only does DATEDIFF ( mi , deadline, completion)

Well, I say that, but it is currently a little more convoluted, but I can deal with that later.

For transparency, my current code, which does not take into account maintenance days, bank holidays, and non-working hours is:

CONVERT(varchar, Datediff(n, [deadline],[completion])/60) + ':' + Right('0' + CONVERT(varchar,(datediff(n,[deadline],[completion]) %60)),2)) as [TimeDIFF]

This format doesn’t matter too much.

What matters is referencing the

• custom bank holiday table, not sys,
• custom maintenance table,
• working hours (different on a saturday to working days)
• passed through a function (in SQL Server 2017)

Sample:

TableBH – columns [date],[bank holiday name]

26 November 2018, FakeBH1

TableMaintenance – columns [date],[maintenance reason]

28 November 2018, FakeMaintenance1

table1 – columns [id], [deadline], [completion]

1, 2018-11-24 14:55, 2018-11-27 09:05 
2, 2018-11-24 15:05, 2018-11-27 09:04
3, 2018-11-27 11:00, 2018-11-27 10:35
4, 2018-11-27 16:50, 2018-11-29 09:10

output id, duration in mins 1 = 10 2 = 4 3 = -25 4 = 20

Answer

BankDate    BankTimeStart       BankTimeEnd         WorkingMins
2018-11-19  09:00:00.0000000    19:00:00.0000000    720
2018-11-20  09:00:00.0000000    19:00:00.0000000    720
2018-11-21  09:00:00.0000000    19:00:00.0000000    720
2018-11-22  09:00:00.0000000    19:00:00.0000000    720
2018-11-23  09:00:00.0000000    19:00:00.0000000    720
2018-11-24  09:00:00.0000000    19:00:00.0000000    480
2018-11-25  09:00:00.0000000    19:00:00.0000000    480
2018-11-26  09:00:00.0000000    19:00:00.0000000    480 *(Bank Holiday)*
2018-11-27  09:00:00.0000000    19:00:00.0000000    720
2018-11-28  00:00:00.0000000    00:00:00.0000000    0 *(fakeMaintenance)*
2018-11-29  09:00:00.0000000    19:00:00.0000000    720
2018-11-30  09:00:00.0000000    19:00:00.0000000    720

Above table inspired by @donPablo

Actually, Bank holidays and Sundays are now 9am to 5pm.

There are are also 2 additional columns [IsBankHoliday] and [IsNonWorkingDay] (generally maintenance).

Then I added the following to the DimDate procedure:

CASE 
    WHEN [nw].[NonWorkingDayDate] IS  NOT NULL
        THEN '00:00:00'
    ELSE '09:00:00'
END AS [StartTime],


CASE 
    WHEN [nw].[NonWorkingDayDate] IS  NOT NULL
        THEN '00:00:00'
    WHEN [bh].[BankHolidayDate] IS  NOT NULL or [DayOfWeekNumber] IN (6,7)
        THEN '17:00:00'
    ELSE '21:00:00'
END AS [EndTime],

Then, I created the following function:

ALTER FUNCTION [Udf].[GenerateBreachTime] (@CompletionAt [datetime],

@Deadline [datetime])

SELECT (SELECT
  CASE
    WHEN @Deadline > @CompletionAt THEN -1
    WHEN CONVERT(varchar(8), @Deadline, 112) <= CONVERT(varchar(8), @CompletionAt, 112) THEN CASE   --NWD DC|| ||DC  = 0
        WHEN ([Non Working Day] = 1 AND
          CONVERT(varchar(8), @Deadline, 112) = CONVERT(varchar(8), [DateId], 112)) --nwd
          OR @Deadline > CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) --deadline after hours
          OR @CompletionAt < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) THEN 0
        -- D|C|
        WHEN @Deadline < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), @CompletionAt)
        --D||C
        WHEN @Deadline < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt > CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime))
        -- |DC|
        WHEN @Deadline >= CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt <= CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, @Deadline, @CompletionAt)
        --|D|C
        WHEN @Deadline >= CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt > CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, @Deadline, CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime))
        ELSE 0
      END
    ELSE 0
  END
  AS [BreachMins]
FROM [Vw].[Dim Date] DimDate
WHERE dateid = CONVERT(varchar(8), @Deadline, 112))
+ (SELECT
  CASE
    WHEN CONVERT(varchar(8), @Deadline, 112) >= CONVERT(varchar(8), @CompletionAt, 112) THEN 0
    WHEN CONVERT(varchar(8), @Deadline, 112) <= CONVERT(varchar(8), @CompletionAt, 112) THEN CASE
        -- C|| OR NWD
        WHEN @CompletionAt < CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) OR
          ([Non Working Day] = 1 AND
          CONVERT(varchar(8), @CompletionAt, 112) = CONVERT(varchar(8), [DateId], 112)) THEN 0
        -- |C|
        WHEN @CompletionAt <= CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime)
        --THEN 1
        THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), @CompletionAt)
        --||C
        WHEN @CompletionAt > CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime)
        --THEN 2
        THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime))
        ELSE 0
      END
    ELSE 0
  END
  AS [BreachMins]
FROM [Vw].[Dim Date] DimDate
WHERE dateid = CONVERT(varchar(8), @CompletionAt, 112))
+ (SELECT
  CASE
    WHEN SUM(workingminsinday) IS NULL THEN 0
    ELSE SUM(workingminsinday)
  END AS [x]
FROM vw.[Dim Date]
WHERE dateid BETWEEN CONVERT(varchar(8), @Deadline + 1, 112) AND CONVERT(varchar(8), @CompletionAt - 1, 112))

AS [BreachMins]

The daily procedure calls the function

        ,[BreachTime].[BreachInMins]
    FROM [Syn].[X]

      CROSS APPLY [udf].[GenerateBreachTime] ([Completed],[Deadline]) [BreachTime]

The subsequent view handles -1 cases, changing them to NULL.

I hope this helps someone else, and apologies if not entirely clear.