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 Thursday, September 24, 2009 5:41 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 37,104, Visits: 31,659
Thanks for the feedback, Mazharuddin... I'll take a look.

--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 #793624
Posted Thursday, September 24, 2009 5:51 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 37,104, Visits: 31,659
I ran your code from the previous post... here it is, again...
select getdate()
select dbo.CalcTimeBetweenTwoDates('2004-11-16 15:30', '2007-11-21 07:31')
select getdate()

Here's what I get for an error message from that code...

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CalcTimeBetweenTwoDates", or the name is ambiguous.

Looking at the code from your good article, I see no function called dbo.CalcTimeBetweenTwoDates. I'm still looking, though.


--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 #793626
Posted Thursday, September 24, 2009 5:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 37,104, Visits: 31,659
I get it... you're not running the same code as in the article. Please post the code you are running. Thanks.

--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 #793627
Posted Friday, September 25, 2009 3: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
Jeff Moden (9/24/2009)
I get it... you're not running the same code as in the article. Please post the code you are running. Thanks.

You are right Jeff. The code in the article Calculating the Number of Business Hours Passed since a Point of Time does not contain the code for the function CalcTimeBetweenTwoDates.
I posted it later in the discussion forum of the article as a reader requested for it. Please find attached the code for the function CalcTimeBetweenTwoDates in the discussion forum of the article

Mazharuddin Ehsan (11/28/2007)
Comments posted to this topic are about the item Calculating The Number Of Business Hours Passed Since a Point of Time

The comments of some of the readers motivated me to do enhancements in the solution:
1. To calculate duration between two values of time.
2. Considering the holidays.

See the attachment for the details.
Edited: 1/13/2008 5:43 PM by Mazharuddin Ehsan

CalculatingDurationOfOfficeHoursPassedBetweenTwoPointsOfTime.doc


-Mazharuddin


-----------------------------------------------------------
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 #793733
Posted Monday, November 30, 2009 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 11:44 PM
Points: 1, Visits: 32
Hey Guys,

Thanks for the really great functions, but I'm having some wierd issue here. I know this is a old thread, sorry.

We have a shift cycle from 7am - 10 pm here with a 1 hour lunch. I have altered the lunch calc to 3600 which works fine and changed the dateFN's to hours that we use. So basically there are 14 working hours in days (long days).

The problem is that when ever I try calculate hours over a date period greater than 2 days, I loose 6 hours per day?

I am more than confused...

select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-16 22:30')
14 --(as expected)
select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-17 22:30')
28 --(as expected)
select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-18 22:30')
36 -- (6 hours short????)
select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-19 22:30')
44 --(12 hours short now???)


  Post Attachments 
Functions.doc (16 views, 52.50 KB)
Post #826287
Posted Wednesday, May 5, 2010 3:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:37 AM
Points: 299, Visits: 561
Hi, could you give me a little guidance on removing the lunch break as we have a rolling support team who work from 8am till 6PM.

I have managed to update the start and end working hours, but do not want to deduct a half hour lunch break.

Any help would be appreciated as you solution works very well.

Thanks in advance.


MCITP SQL 2005, MCSA SQL 2012
Post #915911
Posted Wednesday, May 5, 2010 5:10 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
Thank you very much RTaylor.
For your requirement, simply you do not need to use two of the functions

DateAt1130
DateAt12

Also modify
the below three functions to remove the reference of the above two functions
CalcCreateDate
CalcGetDate
CalcTimeBetweenTwoDates

Additionally, modify the functions DateAt730 to DateAt8
and DateAt16 to DateAt18

to suit your timing
8am till 6PM


-----------------------------------------------------------
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 #916576
Posted Thursday, May 6, 2010 3:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:37 AM
Points: 299, Visits: 561
Thanks I managed to get it all working successfully.

Also I modified the functions so I could provide 2 dates so I can calculate the time passed between them instead of using getdate().

Nice work.


MCITP SQL 2005, MCSA SQL 2012
Post #916741
Posted Thursday, May 6, 2010 6:36 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 37,104, Visits: 31,659
RTaylor2208 (5/6/2010)
Thanks I managed to get it all working successfully.

Also I modified the functions so I could provide 2 dates so I can calculate the time passed between them instead of using getdate().

Nice work.


Cool... two way street here, though. Please post your solution and functions. Thanks.


--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 #916874
Posted Wednesday, June 30, 2010 7:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 27, 2012 4:55 AM
Points: 3, Visits: 10
Hello - I have the below function (source: http://ask.sqlteam.com/questions/1105/regarding-sql-query-further-queries) that basically calculates the business hours/minutes elapsed between two **smalldatetime** fields:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[getBusinessHours] (@smalldatetime1 smalldatetime, @smalldatetime2 smalldatetime)

RETURNS bigint

AS

BEGIN

DECLARE @Diff bigint;
DECLARE @adjusted_1 smalldatetime;
DECLARE @adjusted_2 smalldatetime;

SET @adjusted_1 = case when @smalldatetime1 - dateadd(day, datediff(day, 0, @smalldatetime1), 0) < '18:00' then @smalldatetime1 else dateadd(day, datediff(day, 0, @smalldatetime1), 0) + case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then '17:30' else '18:00' end end;

SET @adjusted_2 = case when @smalldatetime2 - dateadd(day, datediff(day, 0, @smalldatetime2), 0) > '08:30' then @smalldatetime2 else dateadd(day, datediff(day, 0, @smalldatetime2), 0) + case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then '09:30' else '08:30' end end;

SET @Diff = case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then datediff(minute, @adjusted_1, @adjusted_2) - (datediff(day, @adjusted_1, @adjusted_2) * 960) else datediff(minute, @adjusted_1, @adjusted_2) - (datediff(day, @adjusted_1, @adjusted_2) * 870) end;

return @Diff

END


The problem I am facing is that the function fails to calculate the business hours correctly if the "**smalldatetime1**" falls between **12:00 AM** and **8:30 AM**.

Also FYI -

- smalldatetime1 is the date/time when a call is logged into the database.
- smalldatetime2 is the date/time when the call was closed.

Now basically I would like to track the calls that took more than 24 hrs / 4 hrs to close, only considering business hours, which is:

- 8:30 AM - 6:00 PM on Weekdays
- 9:30 AM - 5:30 PM on Weekends

Any help would be much appreciated.

Thanks!
Post #945884
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse