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