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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

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

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

Group: General Forum Members
Points: 84889 Visits: 41069
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.
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
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)

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

geoff.walton
geoff.walton
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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???)
Attachments
Functions.doc (29 views, 52.00 KB)
RTaylor2208
RTaylor2208
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 1188
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
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Mr or Mrs. 500
Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)

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

RTaylor2208
RTaylor2208
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 1188
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84889 Visits: 41069
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.
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
QuestionBoy
QuestionBoy
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: 11
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!
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