SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
PeteO-430407
PeteO-430407
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 100
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 problemTongue
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 2445
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 Wink, possibly you just did not pay attention just because you are the bossw00t
there. Have a nice time.

Sincerely,
Maz

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Attachments
PeteOCalcTimeBetweenTwoDates.doc (49 views, 33.00 KB)
PeteO-430407
PeteO-430407
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 100
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!!
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 2445
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 Smile

Sincerely,
Maz

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

PeteO-430407
PeteO-430407
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 100
Just wanted to let you know we did implement this into our work order time tracking reports. It works GREAT!!!
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 2445
Thanks for the information. It would be nice if you share more details like you used it in what scenario.

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

PeteO-430407
PeteO-430407
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 100
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89437 Visits: 41144
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 <= DATEDIFF(mi,@StartDateTime,@EndDateTime)/@BinSize
)
,
cteDates AS
(--==== Separates the Time as a separate column and removes weekends
SELECT ts.TimeSlot,
Date = DATEADD(dd,DATEDIFF(dd,0,ts.TimeSlot),0),
Time = ts.TimeSlot - DATEADD(dd,DATEDIFF(dd,0,ts.TimeSlot),0)
FROM cteTimeSlots ts
WHERE DATEPART(dw,ts.TimeSlot) NOT IN (@Saturday,@Sunday)
)
--===== Counts time slots within the workday and converts to
-- decimal hours. To exclude dates from a Holiday table,
-- uncomment the last line and modify as necessary.
SELECT COUNT(*)*@BinSize/60.0
FROM cteDates d
WHERE (
(d.Time >= @WorkTimeStart1 AND d.Time < @WorkTimeEnd1)
OR
(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...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dmaddi 20731
dmaddi 20731
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
What is with all the smaller functions:

dbo.DateAtMidnight
dbo.DateAt730
dbo.DateAt1130 etc etc....
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 2445
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") 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,

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search