January 17, 2008 at 10:58 am
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!!
January 18, 2008 at 10:39 am
Mazharuddin Ehsan (12/28/2007)
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!
January 19, 2008 at 12:29 pm
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]
January 22, 2008 at 9:35 am
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.
January 22, 2008 at 11:30 am
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]
January 22, 2008 at 1:01 pm
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
January 24, 2008 at 12:14 pm
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
about programming sadly.
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]
January 29, 2008 at 11:50 am
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!!
January 31, 2008 at 10:50 am
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]
March 7, 2008 at 12:12 pm
Just wanted to let you know we did implement this into our work order time tracking reports. It works GREAT!!!
March 11, 2008 at 10:13 am
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]
March 11, 2008 at 10:34 am
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.
July 26, 2009 at 1:14 pm
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
Change is inevitable... Change for the better is not.
September 21, 2009 at 9:57 am
What is with all the smaller functions:
dbo.DateAtMidnight
dbo.DateAt730
dbo.DateAt1130 etc etc....
September 24, 2009 at 9:07 am
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