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]