# Calculating the Number of Business Hours Passed Since a Point of Tme

• VERY COOL. We needed to be able to calculate time between the creation date of a help ticket and the time it was first modified. Without being able to take in account business hours and holidays any returns we came up with were useless. I just modified the code to reflect our 8-5 business hours, and removed the lunch hour deduction. Again thanks a BUNCH!!

• Mazharuddin Ehsan (12/28/2007)

Incidently, I am also using this solution to calculate and report the duration passed for the unresolved IT HelpDesk cases at my work place. The results are displayed on an overhead dashboard for everyone to see.

quote]

How exactly do you pass your variables to the function. We have a date created field and a date modified field. I wish to compare the times between these fields but am having trouble figuring out how to do this. (I am somewhat of a newbie so this probably explains it:hehe:) It sounds like I am trying to do exactly what you are doing so if you could possibly show me how you have accomplished this it would be greatly appreciated!

• You can use the functions CalcTimeBetweenTwoDates & CalcTimeSinceCreated in any SQL statement, stored procedure or UDF

For example

`select dbo.CalcTimeBetweenTwoDates(date_created, date_modified) from [Your Table]`

will give the duration between the two fields.

`select dbo.CalcTimeSinceCreated(date_created) from [Your Table]`

will give you duration passed since the case was created. You can use this function to track the duration since creation for the unresolved cases.

Sincerely,

Maz

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

• Hmmmm, I must be doing something wrong then, as the code you have described is exactly what I am doing. Initially I was using a view and passing these parameters to the function. Doing this I received this error:

Msg 8115, Level 16, State 2, Procedure CalcTimeBetweenTwoDates, Line 35

Arithmetic overflow error converting expression to data type datetime.

I thought this might be due to the fact that I was using a view so I inserted the values into a table. I then used this code:

select id,TicketCreatedDate,StatusChangeTime

from StatusTimes

where dbo.CalcTimeBetweenTwoDates(TicketCreatedDate,StatusChangeTime) > 4

order by id

but I get the same exact error message.

So I went in and modified my table. I only put one row in the table and everything worked fine. I then put a second row into the table with the EXACT same data. This also worked fine. I then added a row with DIFFERENT data at which time I got the above error.

I have modified your code to remove the lunch hour and I am pretty sure I caused the problem by doing this. I have attached the code for dbo.CalcTimeBetweenTwoDates . I can also add all the other functions if you need me to. Once again your help in this is GREATLY appreciated.

• Oh another bug! Let me catch it this weekend!:)

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

• That is cool! I am looking at where I modified the code and that is where SQL is throwing the error. Just not smart enough to determine exactly what is the problem:P

• Caught the bug, You did a visibly small mistake with the begins and ends while modifying the code

which botched up the whole thing.

Every “begin” should have its “end” in the proper place. This is a simple rule with each and

everything in this universe I believe.

I tested and found that is the only snag. Correct this and this will work. I have modified and writen my

comments in the attachment.

Check these two lines in the attachment.

` --end (You have put this “end” wrongly here. And you are missing one ‘END’ which corresponds to the ‘BEGIN’ above which should be just before the line ‘return @CalcHours’)`

`END – You were missing this END here`

Also I have a piece of advice. When you are doing programming (even if the original writer is someone else

and you are modifying it) you must pay your fullest attention. No doubt it is time consuming but this is all

I believe you are smart enough ;), possibly you just did not pay attention just because you are the boss:w00t:

there. Have a nice time.

Sincerely,

Maz

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

• This seems to work GREAT!!! Thanks so much for your help. It really helps to have someone else to look at your code, so thanks for doing my proofreading for mr!!

• Hi Pete,

I am pleased to know that it is working for you. Testing and debugging a piece of programming script to make it work exactly as you want is different than 'proofreading'. It involves understanding the business logic and much more other than knowing the syntax of the language. So be careful and take care 🙂

Sincerely,

Maz

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

• Just wanted to let you know we did implement this into our work order time tracking reports. It works GREAT!!!

• Thanks for the information. It would be nice if you share more details like you used it in what scenario.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

• In our work order system we have service levels that we must meet. One of the service levels is how long a call is open before it is assigned to a technician. We use the business hours function to give an accurate report on the calls that are currently opened and have not been assigned in the allotted time frame and we also use it to look at closed calls and see over a certain time frame the amount of calls that were assignedot assigned on time.

• If I may suggest...

1. We don't need RBAR to do this... no WHILE loops please. :sick:

2. We don't need 8 functions to do this... that's a lot of complicated code to maintain not to mention the overhead of calling 8 functions.

3. We certainly don't need any hardcoding of times for this. Even the DatePart(DW) shouldn't be hardcoded because of the possible settings of DATEFIRST

4. The absolute best way to do this is to have a Calendar table. Yeah, I know... for some reason, lots and lots of folks would rather use some very complicated code and While Loops to do this instead of the very simple method of using a Calendar table.

With all of that in mind, might I suggest the following instead?

`--===== Declare some obviously named variables`

`DECLARE @StartDateTime DATETIME,`

` @EndDateTime DATETIME,`

` @WorkTimeStart1 DATETIME,`

` @WorkTimeEnd1 DATETIME,`

` @WorkTimeStart2 DATETIME,`

` @WorkTimeEnd2 DATETIME,`

` @BinSize INT,`

` @Saturday INT, --Datepart(dw) for Saturday regardless of DATEFIRST`

` @Sunday INT --Datepart(dw) for Sunday regardless of DATEFIRST`

`;`

`--===== Preset the variables`

` SELECT @StartDateTime = '2007-11-16 15:30', --Likely parameter in function`

` @EndDateTime = '2007-11-20 14:00', --Could be parameter in function`

` @WorkTimeStart1 = '07:30', --Could be parameter in function`

` @WorkTimeEnd1 = '11:30', --Could be parameter in function`

` @WorkTimeStart2 = '12:00', --Could be parameter in function`

` @WorkTimeEnd2 = '16:00', --Could be parameter in function`

` @BinSize = 15, --Minutes, Could be parameter in function`

` @Saturday = DATEPART(dw,5), --First Saturday of 1900`

` @Sunday = DATEPART(dw,6) --First Sunday of 1900`

`;`

`--===== Using the start and end time, calculate the number of business hours`

` -- between those two date/times.`

`WITH`

`cteTimeSlots AS`

`(--==== Produces a list of datetime slots in @BinSize minute intervals`

` SELECT DATEADD(mi,(t.n-1)*@BinSize,@StartDateTime) AS TimeSlot`

` FROM dbo.Tally t`

` WHERE t.N = @WorkTimeStart1 AND d.Time = @WorkTimeStart2 AND d.Time < @WorkTimeEnd2)`

` )`

`-- AND NOT EXISTS (SELECT 1 FROM dbo.Holiday h WHERE d.Date = h.Date)`

As usual, details of how it works are in the comments. If you don't know what a Tally table is or how it works, please see the following article...

[font="Arial Black"]http://www.sqlservercentral.com/articles/T-SQL/62867/[/font][/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• What is with all the smaller functions:

dbo.DateAtMidnight

dbo.DateAt730

dbo.DateAt1130 etc etc....

• Jeff Moden (7/26/2009)

If I may suggest...

1. We don't need RBAR to do this... no WHILE loops please. :sick:

2. We don't need 8 functions to do this... that's a lot of complicated code to maintain not to mention the overhead of calling 8 functions.

3. We certainly don't need any hardcoding of times for this. Even the DatePart(DW) shouldn't be hardcoded because of the possible settings of DATEFIRST

4. The absolute best way to do this is to have a Calendar table. Yeah, I know... for some reason, lots and lots of folks would rather use some very complicated code and While Loops to do this instead of the very simple method of using a Calendar table.

With all of that in mind, might I suggest the following instead?

Hi Jeff,

The alternative you provided looked smart in the initial glance.

I tried to use it and below are my observations:

Using my function CalcTimeBetweenTwoDates:

`select getdate()`

`select dbo.CalcTimeBetweenTwoDates('2004-11-16 15:30', '2007-11-21 07:31')`

`select getdate()`

`Result:`

`2009-09-24 17:05:58.280`

`6280.52`

`2009-09-24 17:05:58.543`

Time taken = 263 ms

The below is how it works using the alternative you provided:

(Note: I had to increase the number of rowes in the Taly table (from the article "The "Numbers" or "Tally" Table: What it is and how it replaces a loop"[/url]) from 11000 to 110000 to get it working)

`2009-09-24 17:21:06.280`

`6280.500000`

`2009-09-24 17:21:06.687`

Time taken = 407 ms

As you see, the time taken is more than the loop method and the result is also not accurate (It should be 6280.52)

I know, to get the accurate result, I need to increase the number of rows in the tally table. But the question is how much and why should I do it, when I am doing it better using the loop. There can be reservations by the db admin to invest so much space in a Tallly table just to do calculations.

1. I agree that the aternative you provided uses less amount of TSQL code, but on the other hand it requires an ever demanding tally tabl.

2. The one thing impressive and useful that you are providing is dynamic work timings. This is worth emulating.

Thanks,

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Viewing 15 posts - 16 through 30 (of 45 total)

You must be logged in to reply to this topic. Login to reply