November 7, 2010 at 3:54 am
I have to provide a report on how long it takes to produce an order. The time must not take into account weekends though. I have been searching around on the internet and cannot find anything that will help. I have seen some articles on how to do this but they do not seem to work if the orders were completed on the weekends, or if they started and completed on a weekend. In the scenario of an order starting or completing on a weekend day a default value of 0 returns.
I have created some sample data.
create table ##orders
(order_number int not null,
order_taken datetime not null,
order_completed datetime not null)
insert into ##orders
Values (1, '2010-11-02 09:03:34.600','2010-11-03 14:40:06.600')
Insert Into ##orders
Values (2, '2010-11-02 09:15:27.600','2010-11-03 19:21:06.600')
Insert Into ##orders
Values (3, '2010-11-03 10:23:34.600','2010-11-03 14:46:07.930')
Insert Into ##orders
Values (4, '2010-11-05 08:25:34.600','2010-11-06 10:46:07.590')
Insert Into ##orders
Values (5, '2010-11-05 10:26:34.600','2010-11-08 11:46:08.497')
Insert Into ##orders
Values (6, '2010-11-05 10:34:34.600','2010-11-06 19:57:22.002')
Insert Into ServiceRequest
Values (7, '2010-11-05 13:27:34.600','2010-11-09 21:46:08.497')
Insert Into ##orders
Values (8, '2010-11-12 08:27:34.600','2010-11-14 07:34:56.820')
Insert Into ##orders
Values (9, '2010-11-13 10:41:34.600','2010-11-14 07:34:56.820')
Insert Into ##orders
Values (10, '2010-11-14 14:41:34.600','2010-11-15 10:20:56.820')
/*
if I run the following query I will get the total hours difference between
order_taken and order_completed. the time_taken column needs to exclude
the time over the weekends.
For example order 5 should have a time_taken of 25 hours.
order 9 should have a time_taken value of 0 as it was taken and completed
over the weekend Order 10 should have a time_taken value of 0 as it was taken
over the weekend and completed on the monday.
*/
select order_number, order_taken, order_completed,
datediff(hh, order_taken,order_completed) as time_taken
from ##orders
order_number order_taken order_completed time_taken
------------ ----------------------- ----------------------- -----------
1 2010-11-02 09:03:34.600 2010-11-03 14:40:06.600 29
2 2010-11-02 09:15:27.600 2010-11-03 19:21:06.600 34
3 2010-11-03 10:23:34.600 2010-11-03 14:46:07.930 4
4 2010-11-05 08:25:34.600 2010-11-06 10:46:07.590 26
5 2010-11-05 10:26:34.600 2010-11-08 11:46:08.497 73
6 2010-11-05 10:34:34.600 2010-11-06 19:57:22.003 33
8 2010-11-12 08:27:34.600 2010-11-14 07:34:56.820 47
November 7, 2010 at 1:45 pm
Try this:
See Attachment
You can also do this by adding a Date table to your schema; that would provide scaleability when you start adding holiday, days off ect. Let me know if you would like to see an example of that.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply