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 |
|
junior6202
Starting Member
45 Posts |
 Posted - 2014-07-07 : 02:09:06
|
| I wrote a query from a shipping table which subtracts two columns arrival date and shipping date. I used the DATEDIFF function and it work perfectly, but now I have to find a way to exclude the weekends and holidays from the results. For example, if a package arrived on Friday and was shipped on Monday it still counts as one business day excluding the weekend and the same goes for the holidays. I am not really sure how to achieve that, so any guidance will be really appreciated. |
|
|
BBarn
Starting Member
14 Posts |
Posted - 2014-07-07 : 08:33:17
|
| I've done this in the past for the exact same reason. The below logic should get you pointed in the right direction.dw returns a number for the day of week represented by MyDate. Sunday is 1 Saturday is 7....CASE DATEPART(dw, MyDate) WHEN 1 then MyDate - 2 --sets it to Friday WHEN 7 then MyDate - 1 --sets it to Friday else MyDateEND as MyCalcDate... |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-07-08 : 17:07:30
|
| I suggest creating a NonWorkDays table and load it with all weekend, holiday and other non-work days (company shutdown, natural disaster, etc.). You then subtract the count of entries in the NonWorkDays calendar from the total days. |
|
|
|
|
|
|
Exclude Weekend and Holiday