Author |
Topic  |
FSA
Starting Member
USA
4 Posts |
Posted - 10/02/2007 : 11:23:36
|
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. |
 |
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 10/02/2007 : 23:19:34
|
FSA, please provide sample data and the expected result
KH Time is always against us
|
 |
|
MessKit
Starting Member
1 Posts |
Posted - 12/05/2007 : 12:27:00
|
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) |
 |
|
X002548
Not Just a Number
15586 Posts |
|
jjm2958
Starting Member
1 Posts |
Posted - 03/02/2009 : 15:20:02
|
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? |
 |
|
sujit
Starting Member
1 Posts |
Posted - 11/16/2009 : 20:01:03
|
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. |
 |
|
gooral1
Starting Member
3 Posts |
Posted - 05/26/2010 : 03:45:47
|
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 |
 |
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 05/26/2010 : 04:12:16
|
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
|
 |
|
gooral1
Starting Member
3 Posts |
Posted - 05/26/2010 : 04:23:34
|
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 |
 |
|
gooral1
Starting Member
3 Posts |
Posted - 05/26/2010 : 06:40:02
|
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 |
 |
|
shenkarthik
Starting Member
India
1 Posts |
Posted - 11/23/2011 : 12:49:32
|
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!
|
 |
|
hedhunter
Starting Member
1 Posts |
Posted - 10/19/2012 : 06:33:10
|
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 |
 |
|
Topic  |
|
|
|