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.
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.