Skip to content
Advertisement

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:

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]

TableMaintenance – columns [date],[maintenance reason]

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

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

Advertisement

Answer

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:

Then, I created the following function:

@Deadline [datetime])

The daily procedure calls the function

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

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement