May 22, 2014 at 1:46 pm
I Googled around for a while for a solution on this and not finding one I decided to write/share my own.
Any and all feedback is welcomed. Thanks.
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.
There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
CREATE function [dbo].[fnc_myHinkley_ASSY_CalcWorkingMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS decimal(14,2)
/*
Programmer: Goran Borojevic
Date: 5/14/2014
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.
*/
AS
BEGIN
--check if one of the dates is null
if @StartDate is null or @EndDate is null
RETURN 0
if @EndDate < @StartDate
return 0
declare @StartDateAsDate datetime, @EndDateAsDate datetime
declare @MinuteDiff decimal(14,2)
DECLARE @FullDays int, @Holidays int;
set @StartDateAsDate = convert(datetime, convert(nvarchar(15), @StartDate, 101))
set @EndDateAsDate = convert(datetime, convert(nvarchar(15), @EndDate, 101))
set @MinuteDiff = 0;
--first decide are we in the same day?
IF @StartDateAsDate = @EndDateAsDate
begin
--GB. 5.21.2014 we are in the same day so lets calculate just for the same day difference in minutes
set @MinuteDiff = datediff(MI, @StartDate, @EndDate)
--now if the time falls during break lets substratct 15 minutes
if (@StartDate <= dateadd(mi,9.75*60, @StartDateAsDate) and @EndDate >= dateadd(mi,10*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 15
END
if (@StartDate <= dateadd(mi,12*60, @StartDateAsDate) and @EndDate >= dateadd(mi,12.5*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 30
END
GOTO ReturnDiff
end
ELSE
--so we didnt have the transaction in the same day. Now lets determine in this case.
BEGIN
--first we will take difference from start to the end of the day.
IF @StartDate < DATEADD(MI, 16*60, @StartDateAsDate) --SINCE WE WORK TILL 4 PM.
BEGIN
SET @MinuteDiff = datediff(mi, @StartDate, DATEADD(MI, 16*60, @StartDateAsDate))
END
--GB. 5.21.2014 next lets take away any breaks or lunches out:
--now if the time falls during break lets substract 15 minutes
if (@StartDate <= dateadd(mi,9.75*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 15
END
if (@StartDate <= dateadd(mi,12*60, @StartDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 30
END
--GB. 5.21.2014 next lets get the number of whole days between the 2 specified dates.
set @FullDays = 0
while @StartDateAsDate < @EndDateAsDate-1
BEGIN
if datename(dw,@StartDateAsDate) not in ('Saturday','Sunday')
BEGIN
Set @FullDays = @FullDays + 1;
END
set @StartDateAsDate = @StartDateAsDate +1
END
--important reset the start date
set @StartDateAsDate = convert(datetime, convert(nvarchar(15), @StartDate, 101))
Set @Holidays = isnull((select count(id) from ESYNERGY.dbo.publicholidays where [Date] between @StartDateAsDate and @EndDateAsDate), 0)
set @MinuteDiff = @MinuteDiff + ((@FullDays-@Holidays) * 7.75*60)
--number of full days days [minus] holidays [times] 7 hours and 45 minutes per day [times] 60 minutes
--finally get the last day of the transaction
IF @EndDate > DATEADD(MI, 7*60, @EndDateAsDate) --SINCE WE WORK from 7.30 AM some might come early.
BEGIN
SET @MinuteDiff = @MinuteDiff + datediff(mi, DATEADD(MI, 7*60, @EndDateAsDate), @EndDate)
END
--GB. 5.21.2014 next lets take away any breaks or lunches out:
--now if the time falls during break lets substratct 15 minutes
if (@EndDate >= dateadd(mi,10*60, @EndDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 15
END
if (@EndDate >= dateadd(mi,12.5*60, @EndDateAsDate))
BEGIN
SET @MinuteDiff = @MinuteDiff - 30
END
END
ReturnDiff:
return @MinuteDiff
END
May 22, 2014 at 4:23 pm
So you are doing the time in minutes, not hours, correct?
Working on a set based example for you ...
May 22, 2014 at 4:31 pm
I might have overcomplicated this, but it might be worth it. Unless someone else comes with a better version of this. If you want to go further, you can generate variables to determine different schedules for working hours, break and lunch.
I didn't test the holiday part because I obviously don't have your table, but it's commented for you to test the functionality.
This is the function I came out with:
DECLARE @StartDate DATETIME = '20140523 13:30'
,@EndDate DATETIME= '20140522 16:00:00'
GO
/*
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(id)
FROM ESYNERGY.dbo.publicholidays
WHERE [Date] BETWEEN @StartDateAsDate AND @EndDateAsDate) * 465)
*/
,0) / 60.0 AS WorkingHours
This is an inline table-valued function which should outperform the scalar function that you wrote. But don't trust me, test it yourself and share the results.
You'll have to call the function in a different way, something like this:
SELECT t.StartDate,
t.EndDate,
wh.WorkingHours
FROM Mytable t
CROSS APPLY [dbo].[CalcWorkingHours] (t.StartDate, t.EndDate) wh
EDIT: Correction in value for holidays calculation
May 22, 2014 at 5:25 pm
I don't know Luis, I think that's not complicated enough 🙂
A quick example building some tables to work with. It was thrown together quickly so some testing may be in order and the Calendar and TimeOfDay tables are not all they could be as of yet
--First, lets make a Calendar table
-- Note: usually, this would have some more fields so it could be used for other things
CREATE TABLE [dbo].[Calendar](
[calendar_date] [date] NOT NULL,
[month_nbr] [tinyint] NOT NULL,
[month_name] [varchar](10) NOT NULL,
[day_of_week_nbr] [tinyint] NOT NULL,
[day_of_week_name] [varchar](10) NOT NULL,
[year_nbr] [int] NOT NULL,
[IsWeekend] [bit] NOT NULL,
[IsHoliday] [bit] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[calendar_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Now, a TimeOfDay table (same note as above)
CREATE TABLE [dbo].[TimeOfDay](
[TimeOfDay] [time](0) NOT NULL,
[hour_nbr] [tinyint] NOT NULL,
[minute_nbr] [tinyint] NOT NULL,
CONSTRAINT [PK_TimeOfDay] PRIMARY KEY CLUSTERED
(
[TimeOfDay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Now we create a ScheduleTimes table. If this was a working solution, you would have a parent Schedule table too describing each schedule, but I skipped it
-- this table lets you customize all those business rules
CREATE TABLE [dbo].[ScheduleTimes](
[time_id] [int] IDENTITY(1,1) NOT NULL,
[schedule_id] [int] NOT NULL,
[start_time] [time](0) NOT NULL,
[end_time] [time](0) NOT NULL,
[time_description] [varchar](255) NULL,
[in_working_day] [bit] NOT NULL,
[is_working_time] [bit] NOT NULL,
CONSTRAINT [PK_ScheduleTimes] PRIMARY KEY CLUSTERED
(
[time_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Now lets load the Calendar table with some test dates. For now I only make christmas day a holiday. Make your own holiday rules or even set them as you do with your holiday table
;
WITH t AS (
SELECT 1 as mynum UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1),
quicktally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn
FROM t t1, t t2, t t3
),
dates AS (
SELECT DATEADD(day, qt.rn, CAST(getdate() AS DATE)) datetally
FROM quicktally qt
)
INSERT Calendar (calendar_date, month_nbr, month_name, day_of_week_nbr, day_of_week_name, year_nbr, IsWeekend, IsHoliday)
SELECT d.datetally,DATEPART(month,d.datetally),DATENAME(month,d.datetally),DATEPART(weekday,d.datetally),DATENAME(weekday,d.datetally),DATEPART(year,d.datetally),CASE WHEN DATENAME(weekday,d.datetally) IN ('Saturday','Sunday') THEN 1 ELSE 0 END, CASE WHEN MONTH(d.datetally)=12 AND DAY(d.datetally)=25 THEN 1 ELSE 0 END
FROM dates d
-- Now, fill up TimeOfDay
;
WITH t AS (
SELECT 1 as mynum UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1),
quicktally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn
FROM t t1, t t2, t t3, t t4
),
times AS (
SELECT CAST (DATEADD(MINUTE, qt.rn, CAST(CAST (getdate() AS DATE) AS DATETIME)) AS TIME(0)) timetally
FROM quicktally qt
)
INSERT TimeOfDay (TimeOfDay, hour_nbr, minute_nbr)
SELECT TOP 1440 ti.timetally, DATEPART(HOUR,ti.timetally),DATEPART(minute,ti.timetally)
FROM times ti
-- And now add times for the schedule your function used
INSERT [dbo].[ScheduleTimes] (schedule_id, start_time, end_time, time_description, in_working_day, is_working_time)
SELECT 1, '00:00:00','07:29:59','Morning pre-work',0,0
UNION ALL
SELECT 1, '07:30:00', '09:44:59','Morning pre-break',1,1
UNION ALL
SELECT 1, '09:45:00', '09:59:59','Morning break',1,0
UNION ALL
SELECT 1, '10:00:00', '12:29:59','Morning post break',1,1
UNION ALL
SELECT 1, '12:30:00', '12:59:59','Lunch',1,0
UNION ALL
SELECT 1, '13:00:00', '15:59:59','Afternoon work',1,1
UNION ALL
SELECT 1, '16:00:00', '23:59:59','Afternoon post work',0,0
-- Now, we define our function. Inline table valued as with Luis'
CREATE FUNCTION dbo.SetBasedWorkingMinutes (@StartDate datetime, @EndDate datetime, @schedule_id int)
RETURNS TABLE
AS
RETURN (
SELECT SUM(CASE WHEN c.IsWeekend = 0 AND c.IsHoliday = 0 AND s.in_working_day = 1 THEN 1 ELSE 0 END) AS WorkingDayMinutes,
SUM(CASE WHEN c.IsWeekend = 0 AND c.IsHoliday = 0 AND s.is_working_time = 1 THEN 1 ELSE 0 END) AS WorkedMinutes,
FROM ( SELECT CAST(c.calendar_date AS datetime) + CAST(t.TimeOfDay AS datetime) DateAndTime,c.calendar_date ,t.TimeOfDay,c.IsWeekend,c.IsHoliday
FROM Calendar c
CROSS JOIN TimeOfDay t
) c
JOIN ScheduleTimes s
ON c.TimeOfDay BETWEEN s.start_time AND s.end_time
WHERE c.DateAndTime >= @StartDate
AND c.DateAndTime < @EndDate
AND s.schedule_id = @schedule_id
)
GO
NOTES:
-The function added a parameter to link to the schedule times table (so you could create a 9-5 schedule, for example, and run the function against it)
-The function is returning minutes, not hours
-Since the function is table valued for performance, I gave you 2 return fields, WorkingDayMinutes counts the minutes from the start to end time without worrying about breaks, WorkedMinutes should match what you had in your query)
- This example took a while to put together even using quickly thrown together data, so it should be tested carefully before being used
- Because it actually selects instead of parsing date/times, the wider your date range, the worse it will perform compared to Luis' . But it should still be pretty fast (2 years took me 1 second) as long as the size of the calendar table is reasonable.
- The ScheduleTimes table lets you customize business rules around breaks and such, and use the same function for different working schedules
- It would actually perform better I think with one date/time calendar table with a record per minute (make better use of an index), but I like the tables separate as both have other uses
EDIT: Fixed the end time to match the test case. Had 5pm by accident originally. Damn 24 hour clock 🙂
May 22, 2014 at 5:34 pm
Nice Nevyn 😉
To be honest, I'd prefer your option over mine, but I wanted to avoid dependance on the schema in case that someone can't create new tables.
Yours is simple and clean.
July 16, 2014 at 2:21 pm
Grrr my email blocker blocked all of my notifications. I didn't see the responses.
Thanks for the responses guys. I shall check it out and write back.
July 18, 2014 at 8:09 am
Functions are good unfortunately I only have a SQL 2005 at this time so the Date variable type is not available to me.
Thanks for the responses. Mine works as well.
July 18, 2014 at 11:33 am
The date variable is fairly easy to work around I think. Instead of casting to that type, you cast to a string, substring off the time, cast back to a datetime.
July 18, 2014 at 4:33 pm
Nevyn (7/18/2014)
The date variable is fairly easy to work around I think. Instead of casting to that type, you cast to a string, substring off the time, cast back to a datetime.
Just for reference:
DATEADD( dd, DATEDIFF( dd, 0, @dt), 0) is faster than CONVERT( DATETIME, CONVERT( VARCHAR(10), @dt, 101)) in the million row test. About twice as fast if memory serves.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 30, 2014 at 5:37 pm
not a fan of all those magic numbers.... 960? 465? 450? 585?
Also, i'm thinking the nine times "DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)" is called, and the nine times "DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0)" is called surely takes longer than defining a variable for each and computing each once. Your mileage may vary.
That... and it doesn't work properly. Try the following:
select * from dbo.calcworkingHours('2014-01-01 06:00','2014-01-01 15:21');
I'm pretty sure the answer shouldn't be negative 1.4 hours.
October 30, 2014 at 6:15 pm
ikjeft01 (10/30/2014)
not a fan of all those magic numbers.... 960? 465? 450? 585?
That's why I included a bunch of comments to know what they're referring to.
ikjeft01 (10/30/2014)
Also, i'm thinking the nine times "DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)" is called, and the nine times "DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0)" is called surely takes longer than defining a variable for each and computing each once. Your mileage may vary.
The reason for that is to keep it as an inline table-valued function. Using variables would imply that the function becomes a multiline function which would perform slower.
ikjeft01 (10/30/2014)
That... and it doesn't work properly. Try the following:
select * from dbo.calcworkingHours('2014-01-01 06:00','2014-01-01 15:21');I'm pretty sure the answer shouldn't be negative 1.4 hours.
Here I messed up, I'm not sure what happened as I'm sure that I tested the function correctly, but something went wrong.
October 31, 2014 at 5:19 pm
Here's my take on this. I listed the break and lunch time as separate columns but would change the final code to instead subtract from the working minutes in the final code, as commented below. This code is easy enough to change to an inline function if you want to try it.
/*
CREATE TABLE dbo.publicholidays ( date datetime NOT NULL )
INSERT INTO dbo.publicholidays VALUES('20141127')
*/
DECLARE @startdatetime datetime
DECLARE @enddatetime datetime
SET @startdatetime = '20141126 09:55'
SET @enddatetime = '20141201 12:50'
--SET @startdatetime = '20140101 06:00'
--SET @enddatetime = '20140101 15:21'
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
SELECT 1 AS tally
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
),
cteTally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
FROM cteTally100 c1
CROSS JOIN cteTally10 c2
)
--/*
SELECT
CONVERT(varchar(10), work_date, 101) AS work_date,
StartDate, EndDate,
StartTime, EndTime,
work_date,
--calc work hours for the current work_date
--SUM(
CASE
WHEN is_nonworkday = 1 THEN 0
ELSE DATEDIFF(MINUTE,
CASE --determine proper start time to use in hours calc for this day
WHEN t.tally = 0 THEN --first day
CASE WHEN StartTime >= Work_Standard_End_Time OR
EndTime < Work_Standard_Start_Time THEN 0
WHEN StartTime >= Work_Standard_Start_Time THEN StartTime
ELSE Work_Standard_Start_Time END
WHEN work_date = EndDate AND EndTime <= Work_Standard_Start_Time THEN 0 --last day
ELSE Work_Standard_Start_Time --normal day
END,
CASE --determine proper end time to use in hours calc for this day
WHEN t.tally = 0 THEN --first day
CASE WHEN StartTime >= Work_Standard_End_Time OR
EndTime < Work_Standard_Start_Time THEN 0
WHEN work_date = EndDate AND EndTime < Work_Standard_End_Time THEN EndTime
ELSE Work_Standard_End_Time END
WHEN work_date = EndDate THEN
CASE WHEN EndTime <= Work_Standard_Start_Time THEN 0
WHEN EndTime < Work_Standard_End_Time THEN EndTime
ELSE Work_Standard_End_Time END
ELSE Work_Standard_End_Time --normal day
END)
END AS work_minutes --comment this line out in final code!
, --check for break time; change comma to minus sign in final code!
CASE WHEN is_nonworkday = 1 THEN 0
WHEN t.tally = 0 THEN --first day
CASE WHEN StartTime >= Work_Standard_Break_End OR EndTime <= Work_Standard_Break_Start THEN 0
ELSE DATEDIFF(MINUTE,
CASE WHEN StartTime > Work_Standard_Break_Start THEN StartTime ELSE Work_Standard_Break_Start END,
CASE WHEN work_date = EndDate AND EndTime < Work_Standard_Break_End THEN EndTime
ELSE Work_Standard_Break_End END)
END
WHEN work_date = EndDate AND EndTime <= Work_Standard_Break_Start THEN 0
ELSE DATEDIFF(MINUTE, Work_Standard_Break_Start,
CASE WHEN work_date = EndDate AND EndTime < Work_Standard_Break_End THEN EndTime
ELSE Work_Standard_Break_End END) --normal day
END AS break_minutes --comment this line out in final code!
, --check for lunch time; change comma to minus sign in final code!
CASE WHEN is_nonworkday = 1 THEN 0
WHEN t.tally = 0 THEN --first day
CASE WHEN StartTime >= Work_Standard_Lunch_End OR EndTime <= Work_Standard_Lunch_Start THEN 0
ELSE DATEDIFF(MINUTE,
CASE WHEN StartTime > Work_Standard_Lunch_Start THEN StartTime ELSE Work_Standard_Lunch_Start END,
CASE WHEN work_date = EndDate AND EndTime < Work_Standard_Lunch_End THEN EndTime
ELSE Work_Standard_Lunch_End END)
END
WHEN work_date = EndDate AND EndTime <= Work_Standard_Lunch_Start THEN 0
ELSE DATEDIFF(MINUTE, Work_Standard_Lunch_Start,
CASE WHEN work_date = EndDate AND EndTime < Work_Standard_Lunch_End THEN EndTime --last day
ELSE Work_Standard_Lunch_End END) --normal day
END AS lunch_minutes --comment this line out in final code!
--END AS work_minutes_adjusted --uncomment this line in final code!
FROM cteTally1000 t
CROSS APPLY (
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, @StartDatetime), 0) AS StartDate,
DATEADD(DAY, DATEDIFF(DAY, 0, @EndDatetime), 0) AS EndDate,
'07:30' AS Work_Standard_Start_Time,
'16:00' AS Work_Standard_End_Time,
'09:45' AS Work_Standard_Break_Start,
'10:00' AS Work_Standard_Break_End,
'12:30' AS Work_Standard_Lunch_Start,
'13:00' AS Work_Standard_Lunch_End
) AS assign_aliases_1
CROSS APPLY (
SELECT
DATEADD(DAY, -CAST(StartDate AS int), @StartDatetime) AS StartTime,
DATEADD(DAY, -CAST(EndDate AS int), @EndDatetime) AS EndTime
) AS assign_aliases_2
CROSS APPLY (
SELECT DATEADD(DAY, t.tally, StartDate) AS work_date
) AS assign_aliases_3
CROSS APPLY (
SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.publicholidays WHERE [Date] = work_date)
OR DATEDIFF(DAY, 0, work_date) % 7 = 6 --Sunday
--OR DATEDIFF(DAY, 0, work_date) % 7 IN (5, 6) --Saturday or Sunday
THEN 1 ELSE 0 END AS is_nonworkday
) AS assign_aliases_4
WHERE
t.tally BETWEEN 0 AND DATEDIFF(DAY, StartDate, EndDate)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 6, 2021 at 2:25 pm
This was removed by the editor as SPAM
May 6, 2021 at 2:36 pm
Hello everyone,
the attached function is used to calculate the difference between two dates (working days) by removing the lunch break (13-14).
The structure consists of:
1) "Calendar" table containing data from 2000 to 2099, present in the attached .csv file
2) V_calendar view where the "IsWorkerDay" field is set.
The field is used to define holidays/working days thinking about holidays (Easter, New Year, etc.)
3) "fn_RF_GetTotalWorkingHours" function which returns the desired value in minutes/hours.
The working hours are 9-13 14-18 every day.
If a date is out of the real time, the time is set to the closest cutoff time
Example:
Email creation time 8:30, sending time 10:00.
The function reports 1 h (9-10) because the office has an opening time of 9, same thing at closing
I hope I was helpful
Fabio
V_Calendar are like this:
Calendar_Date;Calendar_Year;Calendar_Month;Calendar_Day;Calendar_Day_Suffix;Calendar_Quarter;Calendar_Quarter_Name;First_Day_in_Week;First_Day_in_Month;Day_of_Week;Day_of_Year;Week_of_Year;Is_WeekDay;Day_Name;Month_Name
2000-01-01 00:00:00.000;2000;1;1;st;1;First;1999-12-26 00:00:00.000;2000-01-01 00:00:00.000;7;1;1;0;Saturday;January
2000-01-02 00:00:00.000;2000;1;2;nd;1;First;2000-01-02 00:00:00.000;2000-01-01 00:00:00.000;1;2;2;0;Sunday;January
2000-01-03 00:00:00.000;2000;1;3;rd;1;First;2000-01-02 00:00:00.000;2000-01-01 00:00:00.000;2;3;2;1;Monday;January
2000-01-04 00:00:00.000;2000;1;4;th;1;First;2000-01-02 00:00:00.000;2000-01-01 00:00:00.000;3;4;2;1;Tuesday;January
2000-01-05 00:00:00.000;2000;1;5;th;1;First;2000-01-02 00:00:00.000;2000-01-01 00:00:00.000;4;5;2;1;Wednesday;January
2000-01-06 00:00:00.000;2000;1;6;th;1;First;2000-01-02 00:00:00.000;2000-01-01 00:00:00.000;5;6;2;1;Thursday;January
2000-01-07 00:00:00.000;2000;1;7;th;1;First;2000-01-02 00:00:00.000;2000-01-01 00:00:00.000;6;7;2;1;Friday;January
USE [DB]
GO
/****** Object: View [dbo].[v_Calendar] Script Date: 06/05/2021 16:05:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[v_Calendar] as
select Calendar_Date ,
CASE
WHEN DBO.GetEasterSunday(Calendar_Year)=Calendar_Date THEN 0
WHEN dateadd(dd,1,DBO.GetEasterSunday(Calendar_Year))=Calendar_Date THEN 0
when Calendar_Day=1 and Calendar_Month=1 then 0
when Calendar_Day=6 and Calendar_Month=1 then 0
when Calendar_Day=25 and Calendar_Month=4 then 0
when Calendar_Day=1 and Calendar_Month=5 then 0
when Calendar_Day=2 and Calendar_Month=6 then 0
when Calendar_Day=15 and Calendar_Month=8 then 0
when Calendar_Day=1 and Calendar_Month=11 then 0
when Calendar_Day=8 and Calendar_Month=12 then 0
when Calendar_Day=25 and Calendar_Month=12 then 0
when Calendar_Day=26 and Calendar_Month=12 then 0
when Is_WeekDay=0 then 0
when Is_WeekDay=1 then 1
else 1 end as IsWorkerDay
,C.Is_WeekDay
from Calendar c
where c.Calendar_Year<2080
USE [DB]
GO
/****** Object: UserDefinedFunction [dbo].[fn_RF_GetTotalWorkingHours] Script Date: 06/05/2021 15:57:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_RF_GetTotalWorkingHours]
(
@DateFrom Datetime,
@DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @tblDatesAll AS TABLE (DateValue DATE,IsWeekDay int,IsWorkerDay int)
--declare @DateFrom as datetime = '2021-04-02 13:30:00.000'
--declare @DateTo as datetime = '2021-04-06 12:00:00.000'
declare @DateFromRecalculate as datetime = DATEADD(HOUR,18,CAST(CAST(@DateFrom AS DATE) AS DATETIME))
declare @DateToRecalculate as datetime = DATEADD(HOUR,9,CAST(CAST(@DateTo AS DATE) AS DATETIME))
IF DATEPART(HOUR,@DateFrom) < 9
SET @DateFrom = DATEADD(HOUR,9,CAST(CAST(@DateFrom AS DATE) AS DATETIME))
ELSE
IF DATEPART(HOUR,@DateFrom) > 18
SET @DateFrom = DATEADD(HOUR,18,CAST(CAST(@DateFrom AS DATE) AS DATETIME))
IF DATEPART(HOUR,@DateTo) < 9
SET @DateTo = DATEADD(HOUR,9,CAST(CAST(@DateTo AS DATE) AS DATETIME))
ELSE
IF DATEPART(HOUR,@DateTo) > 18
SET @DateTo = DATEADD(HOUR,18,CAST(CAST(@DateTo AS DATE) AS DATETIME))
IF DATEPART(HOUR,@DateFrom) >= 13 and DATEPART(HOUR,@DateFrom) < 14 and DATEPART(HOUR,@DateTo) <= 13
SET @DateFrom =DATEADD(HOUR,13,CAST(CAST(@DateFrom AS DATE) AS DATETIME))
else if DATEPART(HOUR,@DateFrom) >= 13 and DATEPART(HOUR,@DateFrom) < 14 and DATEPART(HOUR,@DateTo) > 13
SET @DateFrom =DATEADD(HOUR,14,CAST(CAST(@DateFrom AS DATE) AS DATETIME))
IF DATEPART(HOUR,@DateTo) >= 13 and DATEPART(HOUR,@DateTo) < 14
SET @DateTo = DATEADD(HOUR,13,CAST(CAST(@DateTo AS DATE) AS DATETIME))
INSERT INTO @tblDatesAll(DateValue, IsWeekDay, IsWorkerDay)
SELECT cast(c.Calendar_Date as date),c.Is_WeekDay,c.IsWorkerDay
FROM dbo.[v_Calendar] c
where cast(c.Calendar_Date as date) between cast(@DateFrom as date) and cast(@DateTo as date)
declare @WorkerDay int = (select count(1) from @tblDatesAll where IsWorkerDay=1)
declare @MMFrom int = (select
case when (SELECT IsWorkerDay FROM dbo.[v_Calendar] WHERE cast(Calendar_Date as date) = cast(@DateFrom as date)) =1
then
case
when DATEPART(HOUR,@DateFrom) < 14 then DATEDIFF(MINUTE,@DateFrom,@DateFromRecalculate) -60
else DATEDIFF(MINUTE,@DateFrom,@DateFromRecalculate)
end
else 0 end)
declare @MMto int = (select
case when (SELECT IsWorkerDay FROM dbo.[v_Calendar] WHERE cast(Calendar_Date as date) = cast(@DateTo as date))=1
then
case
when DATEPART(HOUR,@DateTo) >= 14 then DATEDIFF(MINUTE,@DateToRecalculate,@DateTo)-60
else DATEDIFF(MINUTE,@DateToRecalculate,@DateTo)
end
else 0 end)
declare @HHFrom float = (select
case when @MMFrom = 0 then 0
when DATEPART(HOUR,@DateFrom) < 14 then (@MMFrom/60)-1
else (@MMFrom/60) end)
declare @HHTo float = (select
case when @MMto = 0 then 0
when DATEPART(HOUR,@DateTo) >= 14 then (@MMto/60)-1
else (@MMto/60) end)
set @WorkerDay = case when (SELECT IsWorkerDay FROM dbo.[v_Calendar] WHERE cast(Calendar_Date as date) = cast(@DateFrom as date)) = 1
then @WorkerDay-1 else @WorkerDay end
set @WorkerDay = case when (SELECT IsWorkerDay FROM dbo.[v_Calendar] WHERE cast(Calendar_Date as date) = cast(@DateTo as date)) = 1
then @WorkerDay-1 else @WorkerDay end
--minutes
RETURN (select
case
when @DateTo<@DateFrom then 0
when year(@DateFrom)=year(@DateTo) and month(@DateFrom)=MONTH(@DateTo) and day(@DateFrom)=day(@DateTo) then
case
when (SELECT IsWorkerDay FROM dbo.[v_Calendar] WHERE cast(Calendar_Date as date) = cast(@DateFrom as date)) =0 then 0
when cast(@DateTo as time) <=cast(@DateFrom as time) then 0
when DATEPART(HOUR,@DateFrom) <= 13 and DATEPART(HOUR,@DateTo)>14
then (cast(DATEDIFF(MINUTE,@DateFrom,@DateTo) as float)/60)-1
else cast(DATEDIFF(MINUTE,@DateFrom,@DateTo) as float)/60
end
else @WorkerDay*60*8 + @MMFrom + @MMto
end)
--hour
--RETURN (select
--case
--when @DateTo<@DateFrom then 0
--when year(@DateFrom)=year(@DateTo) and month(@DateFrom)=MONTH(@DateTo) and day(@DateFrom)=day(@DateTo) then
-- case
-- when (SELECT IsWorkerDay FROM dbo.[v_Calendar] WHERE cast(Calendar_Date as date) = cast(@DateFrom as date)) =0 then 0
-- when cast(@DateTo as time) <=cast(@DateFrom as time) then 0
-- when DATEPART(HOUR,@DateFrom) <= 13 and DATEPART(HOUR,@DateTo)>14
-- then (cast(DATEDIFF(MINUTE,@DateFrom,@DateTo) as float)/60)-1
-- else cast(DATEDIFF(MINUTE,@DateFrom,@DateTo) as float)/60
-- end
--else cast((@WorkerDay*60*8 + @MMFrom + @MMto)as float)/60
--end)
END
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply