SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to calculate Business Hours
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

FSA
Starting Member

USA
4 Posts

Posted - 10/02/2007 :  11:23:36  Show Profile  Click to see FSA's MSN Messenger address  Reply with Quote
I think I see my problem. I don't want to include Saturday and Sunday as business days. So my TTR_CALENDAR table looked like:

CREATE TABLE [dbo].[ttr_calendar] (
[day_number] [varchar] (50) NOT NULL ,
[day_name] [varchar] (50) NULL ,
[begin_time] [datetime] NULL ,
[end_time] [datetime] NULL ,
[duration] [real] NULL
) ON [PRIMARY]

insert into ttr_calendar
select 1, 'Monday', '7:00:00 AM', '6:00:00 PM', 39600 union all
select 2, 'Tuesday', '7:00:00 AM', '6:00:00 PM', 39600 union all
select 3, 'Wednesday', '7:00:00 AM', '6:00:00 PM', 39600 union all
select 4, 'Thursday', '7:00:00 AM', '6:00:00 PM', 39600 union all
select 5, 'Friday', '7:00:00 AM', '6:00:00 PM', 39600 union all
select 6, 'Saturday', '7:00:00 AM', '7:00:00 AM', 0 union all
select 7, 'Sunday', '7:00:00 AM', '7:00:00 AM', 0


So when I input a start_date that falls on the weekend, the result is a negative total.

Any idea how I could fix this problem? I only want to consider the 5 day work week, not the weekend.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 10/02/2007 :  23:19:34  Show Profile  Reply with Quote
FSA, please provide sample data and the expected result


KH
Time is always against us

Go to Top of Page

MessKit
Starting Member

1 Posts

Posted - 12/05/2007 :  12:27:00  Show Profile  Reply with Quote
This solved the negative numbers for me:

DECLARE @total_hours INT

SELECT @total_hours = SUM(CASE WHEN @start_date<[DATE] + begin_time AND @end_date<[DATE] + begin_time -- range is outside business hours
OR @start_date>[DATE] + end_time AND @end_date>[DATE] + end_time
THEN 0
ELSE
DATEDIFF(mi,
(CASE WHEN @start_date<[DATE] + begin_time THEN [DATE] + begin_time ELSE @start_date END)
, (CASE WHEN @end_date>[DATE] + end_time THEN [DATE] + end_time ELSE @end_date END)
)
END)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/05/2007 :  12:56:52  Show Profile  Reply with Quote
quote:
Originally posted by FSA

I know the thread is dated but is it possible to subtract holidays from the start and end dates. Can someone show me how to add that to the query?

KHTAN I really appreciated the solution you posted!

Thanks.



Read this

http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jjm2958
Starting Member

1 Posts

Posted - 03/02/2009 :  15:20:02  Show Profile  Reply with Quote
quote:
Originally posted by thecrow55


I know this Thread is a bit old... but an additional tip to the well presented sample would be appreciate it.

When the start_date and end_date are on the same day, you are using just datediff no matter if the start or end dates are outside business hours.

Could it be possible this sample: the start_date is at 6am but business hours start at 8am. We are at 10am. So the result should be 2 hours and not 4.

Thanks in advance for your help!



I know this thread is years old now, but I stumbled upon it and the calendar table at the beginning and the functions have helped me get to 99% completion. The only sticking point left is calculating the business hours when the start date and end date are on the same day. I see where in the first case statement you are just doing a datediff and that would be fine if the start date is always after the start of business hours adn the end date is always before the end of business hours. Consider the following:

Business hours defined as 8:00 AM to 5:00 PM Monday through Friday.
Ticket open datetime (for example) is 7:00 AM Monday and tech response time is 9:00 AM Monday. The duration I'm expecting is 1 hour since business hours don't start until 8am. The above referenced function gives a total_hours of 2. Any other test cases (when teh start date and end date are on different days) works fine.

Is anyone still available to look at this and provide any insight?
Go to Top of Page

sujit
Starting Member

1 Posts

Posted - 11/16/2009 :  20:01:03  Show Profile  Reply with Quote
Not sure if this is the right place to post a question, apologies if so.

My query is as follows:

I have a call tracking database with two smalldatetime fields. I would like to run some reports on this database (via SQL 2005 Reporting Services) using MS SQL 2005 / Query Analyser.

What I'm trying to extract is all the calls that haven't met a specific service level agreement (eg: all calls with a time difference of more than 4 hours between the two smalldatetime fields).

The logic holds true from Monday to Sunday, so no need to exclude weekends, etc.

Is there a simple query to achieve this?

Detailed examples of what I need is shown below (Business hours 8:30 AM - 6:00 PM):


Example1:

smalldatetime1 = 16/11/2009 9:00:00
smalldatetime2 = 17/11/2009 14:00:00

Result (business hours) = 14 hours and 30 mins


Example2:

smalldatetime1 = 16/11/2009 9:00:00
smalldatetime2 = 16/11/2009 12:00:00

Result (business hours) = 3 hours

Any help would be much appreciated.

Thanks heaps,
Sujit.
Go to Top of Page

gooral1
Starting Member

3 Posts

Posted - 05/26/2010 :  03:45:47  Show Profile  Reply with Quote
Hi KHTAN!

quote:
do you have a holiday table ?


from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
left join holiday h
on d.DATE = h.DATE
where h.DATE is NULL



Code works great but I have a problem with the elimination of such holidays Christmas and Easter. How exactly should I entered a table with Christmas?

thx
gooral
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 05/26/2010 :  04:12:16  Show Profile  Reply with Quote
quote:
Originally posted by gooral1

Hi KHTAN!

quote:
do you have a holiday table ?


from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
left join holiday h
on d.DATE = h.DATE
where h.DATE is NULL



Code works great but I have a problem with the elimination of such holidays Christmas and Easter. How exactly should I entered a table with Christmas?

thx
gooral



that should be in the holiday table


KH
Time is always against us

Go to Top of Page

gooral1
Starting Member

3 Posts

Posted - 05/26/2010 :  04:23:34  Show Profile  Reply with Quote
And would you give to the council to make a sample table with some entries? And how to unite with the rest of the code?

gooral
Go to Top of Page

gooral1
Starting Member

3 Posts

Posted - 05/26/2010 :  06:40:02  Show Profile  Reply with Quote
I have a sample table with holidays


CREATE TABLE dbo.WorkCalendar
(
-- we can use the date as primary key
dt SMALLDATETIME PRIMARY KEY CLUSTERED,

-- computed column; very deterministic
isWeekDay AS CONVERT(BIT, CASE
WHEN datepart(dw, dt) IN (6,7)
THEN 0 ELSE 1 END),

-- not a computed column, because we may
-- need to override
isWorkDay BIT DEFAULT 1
);
GO

-- populate the table

DECLARE @dt SMALLDATETIME;
SET @dt = '20070101';
WHILE @dt <= '20151231'
BEGIN
INSERT dbo.WorkCalendar(dt) SELECT @dt;
SET @dt = @dt + 1;
END

-- now we have to set the weekends back to 0
UPDATE dbo.WorkCalendar
SET isWorkDay = 0
WHERE isWeekday = 0;

-- now, let's populate some holidays
UPDATE dbo.WorkCalendar
SET isWorkDay = 0
WHERE isWorkDay = 1
AND dt IN
(



'20090101', --New Year,
'20090412', --Easter Day,

'20100101', --New Year,
'20100404' --Easter Day,


);



How do I use it with function F_TABLE_DATE.txt and business hours?

gooral
Go to Top of Page

shenkarthik
Starting Member

India
1 Posts

Posted - 11/23/2011 :  12:49:32  Show Profile  Reply with Quote
hi,
could you please give your code which you did. i have similar task like that thanks
send a mail karthimca06@gmail.com

quote:
Originally posted by FSA

Hi KHTAN!

I was hoping you'd read my request. I implemented your solution and it works. However, when I use the code in a function and do the following:

SELECT dbo.fn_workinghour('2007-09-21 23:56:48.000','2007-09-23 23:49:44.000')

I get -5.95.

Should I input the dates differently?

My code is like this:

SELECT dbo.fn_Workinghour(OPEN_TIME, CLOSE_TIME)
FROM MyActivityTable

Both OPEN_TIME and CLOSE_TIME are DATETIME fields.

Thank you for your help!

Go to Top of Page

hedhunter
Starting Member

1 Posts

Posted - 10/19/2012 :  06:33:10  Show Profile  Reply with Quote






quote:
Originally posted by jjm2958

quote:
Originally posted by thecrow55


I know this Thread is a bit old... but an additional tip to the well presented sample would be appreciate it.

When the start_date and end_date are on the same day, you are using just datediff no matter if the start or end dates are outside business hours.

Could it be possible this sample: the start_date is at 6am but business hours start at 8am. We are at 10am. So the result should be 2 hours and not 4.

Thanks in advance for your help!



I know this thread is years old now, but I stumbled upon it and the calendar table at the beginning and the functions have helped me get to 99% completion. The only sticking point left is calculating the business hours when the start date and end date are on the same day. I see where in the first case statement you are just doing a datediff and that would be fine if the start date is always after the start of business hours adn the end date is always before the end of business hours. Consider the following:

Business hours defined as 8:00 AM to 5:00 PM Monday through Friday.
Ticket open datetime (for example) is 7:00 AM Monday and tech response time is 9:00 AM Monday. The duration I'm expecting is 1 hour since business hours don't start until 8am. The above referenced function gives a total_hours of 2. Any other test cases (when teh start date and end date are on different days) works fine.

Is anyone still available to look at this and provide any insight?




I've done this myself, pretty quick and dirty so far, but will clean it up later.

If the start and end time are on the same day, I do the below.

First, I grab the day of the week for the day in question. I then query the tt table to find out the start and opening hours.

If the start and end time are on the same day, I do the below.

If the Starttime happened before the opening hours, I replace the starttime with the opening hours.

if the Endtime happened after the closing hours, I replace the Endtime with the closing hours.

Once that's done, the datediff will work correctly.


declare @opentime time, @closetime time ,@Day varchar(50)
SET @Day =

CASE (DATEPART(dw, @start_date) + @@DATEFIRST) % 7
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 0 THEN 'Saturday'
END


set @opentime = (select cast(begin_time as time) from #ttr_calendar where [day_name] = @Day)
set @closetime = (select cast(end_time as time) from #ttr_calendar where [day_name] = @Day)


if dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0)
BEGIN
If @opentime > cast(@start_date as time)
BEGIN
set @start_date = cast(cast(@start_date as date)as datetime) + @opentime
END
IF @closetime < cast(@end_date as time)
BEGIN
set @end_date = cast(cast(@end_date as date)as datetime) + @closetime
END
END

Edited by - hedhunter on 10/19/2012 06:33:37
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000