Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zany
Starting Member
4 Posts |
 Posted - 2007-03-07 : 14:15:28
|
| Hi,I want to select all the orders where the difference between the ordered date and the delivered date is greater than 5 working days. I wrote a query which will do this. But I don't think this is the optimized way to get the job done. can anyone suggest a better solution?TablesTableA has Ordered DateTableB has Delivered DateTableC has holiday list (columns: HolidayName, Date )Here is my queryselect A.OrderID, Datediff ( dd, A.OrderedDate, B.DeliveredDate) as NumberOfDaysfrom TableA A left join TableB Bon A.OrderID = B.OrderIDwhere Datediff ( dd, A.OrderedDate, B.DeliveredDate) - (Select count(*) From TableC Where [Date] between A.OrderedDate and B.DeliveredDate) >= 5Thanks in advance |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-07 : 14:55:52
|
| zany, why do you think this is not cool? you could use function but that would be an overkill for this one.... |
 |
|
|
zany
Starting Member
4 Posts |
Posted - 2007-03-07 : 15:19:41
|
| Thank you for the reply Paresh. I was just hoping there could be a better solution. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-07 : 17:08:41
|
what is your definition of working days ?
KH |
|
|
|
zany
Starting Member
4 Posts |
Posted - 2007-03-07 : 17:14:05
|
| All days excluding Saturdays, Sundays and holidays |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
Calculate no. of working days