-
Notifications
You must be signed in to change notification settings - Fork 671
/
udf_CalcWorkingHours.sql
67 lines (66 loc) · 4.56 KB
/
udf_CalcWorkingHours.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
/*
Programmer: Luis Cazares
Date: 2014-05-22
Purpose: This function will return working hours between given 2 dates.
This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM.
This function also assumes that the working hours are between 7:30 AM and 4 PM.
This function was inspired by Goran Borojevic
*/
CREATE function [dbo].[CalcWorkingHours] (@StartDate datetime, @EndDate datetime)
RETURNS table
AS RETURN
SELECT ISNULL((((DATEDIFF(dd,@StartDate,@EndDate)-1) --Start with total number of days including weekends
- (DATEDIFF(wk,@StartDate,@EndDate)*2) --Subtact 2 days for each full weekend
- (1-SIGN(DATEDIFF(dd,6,@StartDate)%7)) --If StartDate is a Sunday, Subtract 1
- (1-SIGN(DATEDIFF(dd,5,@EndDate) %7))) * 465) --This will give us full days minus one that we'll complete with following operations
+ (SELECT CASE WHEN @StartDate <= DATEADD(MI, 450, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 465 --if Start Date is earlier than 7:30 then it counts as full day
WHEN @StartDate >= DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 0 --if Start Date is later than 16:00 then it won't count
ELSE DATEDIFF(MI, @StartDate, DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))) --Minutes between start date and 16:00
-
CASE WHEN @StartDate <= DATEADD(MI, 585, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 15 --If it's earlier than the break, substract whole break
WHEN @StartDate <= DATEADD(MI, 600, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
--If it's earlier than the end of the break, substract corresponding minutes
THEN DATEDIFF( MI, @StartDate, DATEADD(MI, 600, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0)))
ELSE 0
END
-
CASE WHEN @StartDate <= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 30 --If it's earlier than lunch, substract whole lunch time
WHEN @StartDate <= DATEADD(MI, 780, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
--If it's earlier than the end of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, @StartDate, DATEADD(MI, 780, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0)))
ELSE 0
END
END
+ CASE WHEN @EndDate <= DATEADD(MI, 450, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 0 --if End Date is earlier than 7:30 then it won't count
WHEN @EndDate >= DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 465 --if End Date is later than 16:00 then it counts as full day
ELSE DATEDIFF(MI, DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate) --Minutes between 7:30 and end date
-
CASE WHEN @EndDate >= DATEADD(MI, 600, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 15 --If it's later than the break, substract whole break
WHEN @EndDate >= DATEADD(MI, 585, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
--If it's later than the start of the break, substract corresponding minutes
THEN DATEDIFF( MI, DATEADD(MI, 585, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate)
ELSE 0
END
-
CASE WHEN @EndDate >= DATEADD(MI, 780, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 30 --If it's later than lunch, substract whole lunch time
WHEN @EndDate >= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
--If it's later than the start of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate)
ELSE 0
END
END
WHERE @StartDate <= @EndDate)
/* Uncomment to use holidays table
- ((SELECT count(*)
FROM holidaystable
WHERE [Date] BETWEEN @StartDate AND @EndDate) * 7.75)
*/
,0) / 60.0 AS WorkingHours