| Author |
 Topic  |
|
latingntlman
Yak Posting Veteran
USA
96 Posts |
 Posted - 11/08/2006 : 14:45:07
|
I have to perform a calculation between two datetimes, but I need to exclude non-business hours and weekends.
i.e. DateSentToShipping, DateShipped
Can this be done? If so, How?
thx,
JOhn   |
|
|
Norwich
Posting Yak Master
Kenya
158 Posts |
Posted - 11/08/2006 : 16:41:18
|
It can be done. (Almost anything can be done in QA except make coffee).
Because you didn't post any info on you problem: I will just give you a framework that you can work from.
Declare @DateShipped datetime,
@DateDelivered Datetime,
@DayDiff int,
@x int
Set @DateShipped = '2006/06/17'
Set @DateDelivered = '2006/06/24'
If Datename(dw,@DateShipped) = 'Saturday' --If Shipped date falls on a Sat then Move the date to the following Date
Begin --If it falls on a Sunday then move it to Monday.
Set @DateShipped = Dateadd(dd, 2,@DateShipped) --This logic applies to the Delivery date.
End
Else
If Datename(dw,@DateShipped) = 'Sunday'
Begin
Set @DateShipped = Dateadd(dd, 1,@DateShipped)
End
If Datename(dw,@DateDelivered) = 'Saturday'
Begin
Set @DateDelivered = Dateadd(dd, 2,@DateDelivered)
End
Else
If Datename(dw,@DateDelivered) = 'Sunday'
Begin
Set @DateDelivered = Dateadd(dd, 1,@DateDelivered)
End
Select @DayDiff = Datediff(d, @DateShipped, @DateDelivered)
Print @DayDiff --[Totals Days (incld Wkends)]
Select @x = Sum(@DayDiff/7)
if @x >= 1
Begin
Set @DayDiff = @DayDiff-(@x*2)
End
Print @DayDiff --[Totals Days (excld Wkends)]
I added code that moves the Ship/Delivery date to a Monday if it's on Saturday Or Sunday.
Let me know if you need something more specific.
Regards
N
The revolution won't be televised! |
 |
|
|
pir8d
Starting Member
Australia
10 Posts |
Posted - 03/04/2007 : 19:15:04
|
Good Morning All,
I'm after a variation to the original post, something a little more detailed.
I need to calculate the business hours between two dates. Currently I have the business hours outlined in a table as below:
CREATE TABLE [dbo].[ttr_calendar] (
[day_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[day_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[begin_time] [datetime] NULL ,
[end_time] [datetime] NULL ,
[duration] [real] NULL
) ON [PRIMARY]
The table looks something like this:
[day_number] [day_name] [begin_time] [end_time] [duration]
1 Monday 6:30:00 AM 5:00:00 PM 37800
2 Tuesday 6:30:00 AM 9:00:00 PM 52200
3 Wednesday 6:30:00 AM 9:00:00 PM 52200
4 Thursday 6:30:00 AM 9:00:00 PM 52200
5 Friday 6:30:00 AM 9:00:00 PM 52200
6 Saturday 8:00:00 AM 5:00:00 PM 32400
7 Sunday 8:00:00 AM 5:00:00 PM 32400
Note: The duration column is a calculated using DATEDIFF(ss,begin_time,end_time) and is shown in seconds for accuracy.
Now, given a start_date and an end_date, does anyone have any ideas how I can calculate the business hours between those dates?
Eg.
start_date = '15 Feb 2007 11:00:00 AM'
end_date = '17 Feb 2007 10:00:00 PM'
+ 10 hours (15 Feb 11:00AM - 15 Feb 9:00PM business hours)
- 9.5 hours (15 Feb 9:00PM - 16 Feb 6:30AM non-business hours)
+ 14.5 hours (16 Feb 6:30AM - 16 Feb 9:00PM business hours)
- 9.5 hours (16 Feb 9:00PM - 17 Feb 6:30AM non-business hours)
+ 14.5 hours (17 Feb 6:30AM - 17 Feb 9:00PM business hours)
- 1 hour (17 Feb 9:00PM - 17 Feb 10:00PM non-business hours)
= 39 hours
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/04/2007 : 20:28:19
|
drop table #ttr_calendar
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', '6:30:00 AM', '5:00:00 PM', 37800 union all
select 2, 'Tuesday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 3, 'Wednesday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 4, 'Thursday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 5, 'Friday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 6, 'Saturday', '8:00:00 AM', '5:00:00 PM', 32400 union all
select 7, 'Sunday', '8:00:00 AM', '5:00:00 PM', 32400
declare @start_date datetime,
@end_date datetime
select @start_date = '15 Feb 2007 11:00:00 AM',
@end_date = '17 Feb 2007 10:00:00 PM'
select total_hours = sum(case
when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then
case
when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)
else duration
end
when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then
case
when @end_date < [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)
else duration
end
else duration
end
)
/ 60.0 / 60.0
from F_TABLE_DATE(@start_date, @end_date) d inner join #ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
using F_TABLE_DATE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE
KH
|
Edited by - khtan on 03/04/2007 20:38:39 |
|
|
|
pir8d
Starting Member
Australia
10 Posts |
Posted - 03/04/2007 : 21:21:53
|
Hi KH,
Thanks for the info and the quick reply, the function and the script you wrote work nicely, when calculating the total_hours, it seems to just take the sum of the days in which is spans across.
Perhaps we need to add/subtract the difference between the start_date and the begin_time for that day and add/subtract the difference between the end_date and the end_time for that day?
Eg. If we use:
select @start_date = '15 Feb 2007 11:00:00 AM',
@end_date = '15 Feb 2007 3:00:00 PM'
We get 14.5 hours, which is the total number of hours for that business day, but the actual business hours between the start_date and end_date is 4 hours.
Sorry, I know this query is getting quite complicated, your assistance is very much appreciated.
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/04/2007 : 21:26:20
|
missed out the condition when start_date and end_date are same day.
drop table #ttr_calendar
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', '6:30:00 AM', '5:00:00 PM', 37800 union all
select 2, 'Tuesday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 3, 'Wednesday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 4, 'Thursday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 5, 'Friday', '6:30:00 AM', '9:00:00 PM', 52200 union all
select 6, 'Saturday', '8:00:00 AM', '5:00:00 PM', 32400 union all
select 7, 'Sunday', '8:00:00 AM', '5:00:00 PM', 32400
declare @start_date datetime,
@end_date datetime
select @start_date = '15 Feb 2007 11:00:00 AM',
@end_date = '15 Feb 2007 03:00:00 PM'
select total_hours = sum(case
when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then
datediff(second, @start_date, @end_date)
when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then
case
when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)
else duration
end
when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then
case
when @end_date < [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)
else duration
end
else duration
end
)
/ 60.0 / 60.0
from F_TABLE_DATE(@start_date, @end_date) d inner join #ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
KH
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/04/2007 : 21:27:19
|
by the way, the nice function F_TABLE_DATE() is written by MVJ.
KH
|
|
|
|
pir8d
Starting Member
Australia
10 Posts |
Posted - 03/04/2007 : 21:36:55
|
Wow, thanks again for the quick reply. That solution that you have there is exactly what I was looking for, it is incredible.
Thanks also to MVJ for the awesome F_TABLE_DATE function. |
|
|
|
pir8d
Starting Member
Australia
10 Posts |
Posted - 03/12/2007 : 20:31:24
|
Hi khtan,
Thanks again for your advice on this code. Just after one more bit of advice.
I have got the above code to work correctly in a stored procedure - it takes two parameters (start_date and end_date) and it returns an output (total_hours).
If I have a table of values, eg.
CREATE TABLE [dbo].[#ttr_calendar] (
[row_id] [varchar] (50) NOT NULL ,
[start_date] [datetime] NULL ,
[end_date] [datetime] NULL ,
[total_hours] [real] NULL
) ON [PRIMARY]
What I would like to do is run through the table and perform the stored procedure on each row, and store the total_hours returned from the stored procedure in the total_hours row.
Any ideas? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/12/2007 : 21:55:13
|
your start_date and end_date should be of same date day right ? then just use datediff(hour, start_date, end_date)
KH
|
Edited by - khtan on 03/12/2007 22:27:10 |
|
|
|
pir8d
Starting Member
Australia
10 Posts |
Posted - 03/12/2007 : 22:16:27
|
Hi khtan,
But if I just run DATEDIFF then it won't take into account the business hours calculations you helped me create earlier on in this thread. Sorry if I wasn't clear enough, I would like to populate the total_hours field with the result of the stored procedure (which is a calculation of the total business hours between the two dates).
Cheers. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/12/2007 : 22:34:59
|
create a function out of the script
create function fn_workinghour
(
@start_date datetime,
@end_date datetime
)
returns decimal(10,2)
as
begin
declare @total_hours decimal(10,2)
select @total_hours = sum(case
when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then
datediff(second, @start_date, @end_date)
when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then
case
when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)
else duration
end
when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then
case
when @end_date < [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)
else duration
end
else duration
end
)
/ 60.0 / 60.0
from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
return @total_hours
end
Then use it to calculate the total hours
update t
set total_hours = dbo.fn_workinghour(start_date, end_date)
from yournewtable t
KH
|
|
|
|
pir8d
Starting Member
Australia
10 Posts |
Posted - 03/12/2007 : 22:57:15
|
Thanks again mate, exactly what I was looking for. I tried doing that with a function but had problems as it doesn't allow the use of temp tables. Changed them to actual tables like your code above and it works exactly as I wanted.
Cheers! |
|
|
|
MKD
Starting Member
2 Posts |
Posted - 04/10/2007 : 09:33:26
|
| Wow.Its very helpful.Can yoiu tell me how can I ignore the weekends please?Thanks a lot |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/10/2007 : 09:44:23
|
Ignore dates that has weekdayname Sunday and Saturday.
Peter Larsson
Helsingborg, Sweden |
|
|
|
MKD
Starting Member
2 Posts |
Posted - 04/18/2007 : 16:44:00
|
I want to ignore weekends and Take time only from 2pm to 5 pm on Friday and 10 am to 3 pm on Monday.It is not showing hrs correctly.(For example My starttime is Friday 2Pm and end time is 3PM Monday.I am expecting answer as 2 hrs.1 hr on Friday and 1 hr on Monday.I change minutes values in the table calendar.Sti;ll no luck.Any ideas
Declare @start_date datetime
Declare @end_date datetime
declare @total_hours decimal(10,2)
set @start_date='4/13/2007 8:00:00 AM'
set @end_date='4/17/2007 5:00:00 AM'
select @total_hours =sum(case
when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) and [WEEKDAY_NAME] not in ('Sat','Sun')then
datediff(second, @start_date, @end_date)
when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) and [WEEKDAY_NAME] not in ('Sat','Sun')then
case
when @start_date > [DATE] + begin_time and [WEEKDAY_NAME] not in ('Sat','Sun') then datediff(second, @start_date, [DATE] + end_time)
else datediff(second, @start_date, [DATE] + end_time)
end
when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then
case
when @end_date < [DATE] + end_time and [WEEKDAY_NAME] not in ('Sat','Sun') then datediff(second, [DATE] + begin_time, @end_date)
else datediff(second, [DATE] + begin_time, @end_date)
end
else datediff(second, [DATE] + begin_time, @end_date)
end
)
/ 60.0 / 60.0
from F_TABLE_DATE(@start_date, @end_date) d inner join calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
Print @total_hours
|
|
|
|
thecrow55
Starting Member
1 Posts |
Posted - 08/08/2007 : 06:21:18
|
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! |
|
|
|
FSA
Starting Member
USA
4 Posts |
Posted - 10/01/2007 : 17:20:56
|
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. |
|
|
|
FSA
Starting Member
USA
4 Posts |
Posted - 10/01/2007 : 19:37:25
|
I need to clarify my request. In addition to calculating business hours, I also need to subtract and holiday hours. The holidays are maintained in a table.
Again, I appreciate any help! |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/01/2007 : 21:39:31
|
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
KH
Time is always against us
|
|
|
|
FSA
Starting Member
USA
4 Posts |
Posted - 10/02/2007 : 11:08:39
|
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! |
|
|
|
Topic |
|
|
|
How to calculate Business Hours
