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

Question

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

Viewing 15 posts - 31 through 45 (of 45 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 4

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[/url] 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 [/url]
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

-----------------------------------------------------------[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]

geoff.walton Grasshopper Points: 13 More actions · Answer 5

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???)

RTaylor2208 SSChampion Points: 13298 More actions · Answer 6

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 SSCarpal Tunnel Points: 4552 More actions · Answer 7

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

-----------------------------------------------------------[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]

RTaylor2208 SSChampion Points: 13298 More actions · Answer 8

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 SSC Guru Points: 1003861 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

QuestionBoy SSC Rookie Points: 41 More actions · Answer 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!

c1680222 SSC Rookie Points: 33 More actions · Answer 11

Hello, it's a great tool for calculate SLA's

but I'm trying to use this for calculate saturday morning include (here (Brazil) we work in saturdays too)

can you help me to do this modification?

thank you

sunnyplace SSC Enthusiast Points: 148 More actions · Answer 12

Very nice job with the function that calculates time between two dates, I am working in a report that I need to calculate the number of working days less holidays and I was wondering if those functions can be modified to be used to calculate working days excluding holidays, I do not need to remove lunch hour. I am new in this, so I will appreciate a little guidance.

Learning user SSC Rookie Points: 41 More actions · Answer 13

hi

i am new to this forum

my problem is i have table to define the working hours of the employees based on that i need to calculate the hours

there is no such rule of common shift timeing

>>Problem statements

Have Creation date of ticket

Employee work in diffrent shift based on the World country they support

so weekoff and shift time are in one table

and list of holidays in another table

so now i need to calculate the time spent as of now... received time and current time - i should get the business hours

Table 1 : Shift start Time | Shift End time | Weekoff1 | weekoff2 |

Table 2 : Holidays

Result should be business hours

Ian.Donaldson Grasshopper Points: 22 More actions · Answer 14

I am confused I am just getting

I just keep getting error messages because of the dbo.DateAt...... I have checked through it and understand that this is saying the start and end times but confused on how this needs to be written so the function will work. HELP 🙂

Ryk Say Hey Kid Points: 682 More actions · Answer 15

Jeff,

I found your 'business hours' solution within this old post and just wanted to thank you for it. My initial testing shows that it should work just fine for my particular needs with a few tweaks to remove the lunch hour and add some alternate time windows & holidays.

You might not have thought that your reply would still be helping folks 6+ years later, but I'm sure glad you saved me a boatload of upfront work on this! Your solution seems much better and elegant than the other solution offered in the thread, since it's set-based.

Again, thanks so much!