Want to Calculate Business Working hours

Question

Want to Calculate Business Working hours

Viewing 14 posts - 16 through 28 (of 28 total)

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

Robert Goode SSC Veteran Points: 292 More actions · Answer 1

Hi Jeff,

Many thanks for posting this. If I could ask how I would go about modifying it to return the correct date and time based on an input of startDateTime and HoursToAdd?

Any help in pointing me in the right direction would be much appreciated.

Cheers

Robert Goode

Dublin, Ireland

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

Ken Champion (12/11/2009)
This is my first experience with a tally table. I modified your solution to calculate number of business days given the start datetime and end datetime, and it worked perfectly. Amazingly elegant and fast.

Wow... my apologies, Ken. I certainly lost track of this thread. Thanks for the feedback and sorry for the late reply.

--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: 1003863 More actions · Answer 3

Robert Goode (6/2/2010)
Hi Jeff,
Many thanks for posting this. If I could ask how I would go about modifying it to return the correct date and time based on an input of startDateTime and HoursToAdd?
Any help in pointing me in the right direction would be much appreciated.
Cheers
Robert Goode
Dublin, Ireland

My recommendation would be to use a Calendar Table for such a thing.

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

Your Name Here SSCrazy Points: 2344 More actions · Answer 4

I tinkered with this for a little while today and came up with something that just dealt with business [hours]. If anybody wants to goof around with it a little more, feel free. A prior version of this code didn't deal with 'variable' hours per day and just hardcoded 8 am to 5 pm for all five days. That removed the table variable and made the code a bit smaller.

Cheers,

Ken

SET DATEFIRST 7

DECLARE @businesshours TABLE (

DOW TINYINT,

StartTime VARCHAR(5),

EndTime VARCHAR(5)

)

INSERT INTO @businesshours (DOW, StartTime, EndTime)

SELECT 2, '08:00', '17:00'

UNION

SELECT 3, '08:00', '12:00'

UNION

SELECT 4, '09:00', '13:00'

UNION

SELECT 5, '13:00', '17:00'

UNION

SELECT 6, '08:00', '17:00'

DECLARE @startdate DATETIME,

@enddate DATETIME

SELECT @startdate = '2010-07-06 08:00',

@enddate = GETDATE()

DECLARE @hours INT

SET @hours = 0

WHILE @startdate <= @enddate

BEGIN

SELECT @hours = @hours + COUNT(*)

FROM @businesshours

WHERE DOW = DATEPART(dw, @startdate)

AND DATEPART(hh, @startdate) BETWEEN DATEPART(hh, StartTime) AND DATEPART(hh, EndTime)

SELECT @startdate = DATEADD(hh, 1, @startdate)

END

SELECT @hours = @hours - 1

SELECT @hours

Your Name Here SSCrazy Points: 2344 More actions · Answer 5

<LOL>

I didn't see the entire second page of replies.

My apologies to Jeff and the group.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Ken Klaft-381933 (7/7/2010)
<LOL>
I didn't see the entire second page of replies.
My apologies to Jeff and the group.

😛

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

karthik_ayyagari SSC Rookie Points: 47 More actions · Answer 7

Hi,

I have a smiliar requirement. A ticket comes to us with status as 'New', then we 'Accept' it and work on it and finally 'Close' it. In between the ticket can be put on 'Hold' for various reasons. Our business hours are 9AM to 6PM. Weekends are Off. No 'Lunch Break' time excluded. Can you please help me with a solution for this ?

Thanks,

KAy

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

Hi Karthik,

Following is the resolution I see after analyzing your requirement. Status and 'Date and time' of a ticket is required to maintained as follows:

'InProgress': when a ticket is raised ('Accepted') or restarted after a possible 'Hold'

'Hold': when a ticket is put on hold

'Closed': when a ticket is closed.

The business hours between all the possible consecutive

'InProgress' and 'Hold'

and the mandatory 'InProgress' and 'Closed' for a ticket

are required to add up and get the total time taken to close a ticket.

Sincerely,

Maz

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

karthik_ayyagari SSC Rookie Points: 47 More actions · Answer 9

Yes Maz,

For the time being can you write a procedure which can calculate number of weekends between two datetimes so that i can delete the n*48 hrs from the time taken from 'Accepted' to 'Closed'. I'm very new to SQL i don't much about it.

Say,

Ticket Accepted Ticket Closed

--------------------------------------------------

04-01-2013 18:00:00 07-01-2013 18:00:00

With 5th and 6th as Saturday & Sunday ( or any number of weekends in that case) ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

karthik_ayyagari (1/4/2013)
Yes Maz,
For the time being can you write a procedure which can calculate number of weekends between two datetimes so that i can delete the n*48 hrs from the time taken from 'Accepted' to 'Closed'. I'm very new to SQL i don't much about it.
Say,
Ticket Accepted Ticket Closed
--------------------------------------------------
04-01-2013 18:00:00 07-01-2013 18:00:00
With 5th and 6th as Saturday & Sunday ( or any number of weekends in that case) ?

Show us what you have done so far to achieve this goal. We are volunteers here and we shouldn't just be doing your work for you.

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

Very true. Karthik, you need to 'give your question a chance of being answered correctly' ..

Lynn Pettis (1/4/2013)
karthik_ayyagari (1/4/2013)
Yes Maz,
For the time being can you write a procedure which can calculate number of weekends between two datetimes so that i can delete the n*48 hrs from the time taken from 'Accepted' to 'Closed'. I'm very new to SQL i don't much about it.
Say,
Ticket Accepted Ticket Closed
--------------------------------------------------
04-01-2013 18:00:00 07-01-2013 18:00:00
With 5th and 6th as Saturday & Sunday ( or any number of weekends in that case) ?
Show us what you have done so far to achieve this goal. We are volunteers here and we shouldn't just be doing your work for you.

Nevermind, here is the answer to what you asked in your second post

select datediff(hh,convert(datetime,'04-01-2013 18:00:00',105),

convert(datetime,'07-01-2013 18:00:00',105)) -(1*48)

It will give you the total hours between your dates '04-01-2013 18:00:00' and '07-01-2013 18:00:00' (I assumed you are using datetime format 'dd-mm-yyyy hh:mm:ss'). The 'n' you meant is 1 here. You can get started from here. By the way, the 'n' here needs to be automated through the code. My signature post 'Calculating the Number of Business Hours Passed Between Two Points of Time' can be useful for you in this.

Sincerely,

Maz

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

mona_vahab Grasshopper Points: 15 More actions · Answer 12

Hello,

I have the same question but the problem is in my case the login and logout time is not indicated. Could you please kindly help me with that.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 13

mona_vahab (2/16/2016)
Hello,
I have the same question but the problem is in my case the login and logout time is not indicated. Could you please kindly help me with that.

There are a few related but different questions in this thread. Which one exactly do you have?

There are also answers to all questions in this thread. Which one have you tried? How far did you get and where did you get stuck?

Or to put it differently, please post CREATE TABLE statements for your tables, INSERT statements with a few rows of well-chosen sample data, the results you expect returned from that sample data, and the work you have done so far.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

MMartin1 One Orange Chip Points: 27768 More actions · Answer 14

mona_vahab (2/16/2016)
Hello,
I have the same question but the problem is in my case the login and logout time is not indicated. Could you please kindly help me with that.

Hi and welcome to the forum. Please see this link so help you formalise your question to better equip us to help you,

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

It may also makes sense if the circumstances are different enough to just start a new thread for your question.

----------------------------------------------------