Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Calculating the Number of Business Hours Passed Since a Point of Tme Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2008 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:44 AM
Points: 7, 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 problem:P
Post #446078
Posted Thursday, January 24, 2008 12:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
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
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


  Post Attachments 
PeteOCalcTimeBetweenTwoDates.doc (36 views, 33.00 KB)
Post #447141
Posted Tuesday, January 29, 2008 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:44 AM
Points: 7, 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!!
Post #449036
Posted Thursday, January 31, 2008 10:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
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


-----------------------------------------------------------
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
Post #450089
Posted Friday, March 7, 2008 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:44 AM
Points: 7, Visits: 100
Just wanted to let you know we did implement this into our work order time tracking reports. It works GREAT!!!
Post #466064
Posted Tuesday, March 11, 2008 10:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
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
Post #467555
Posted Tuesday, March 11, 2008 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:44 AM
Points: 7, 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.
Post #467584
Posted Sunday, July 26, 2009 1:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
If I may suggest...

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

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #759801
Posted Monday, September 21, 2009 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 25, 2009 8:39 AM
Points: 1, Visits: 4
What is with all the smaller functions:

dbo.DateAtMidnight
dbo.DateAt730
dbo.DateAt1130 etc etc....
Post #791271
Posted Thursday, September 24, 2009 9:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
Jeff Moden (7/26/2009)
If I may suggest...

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

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
Post #793353
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse