SQL query adding workdays

[DitkaisGod]

I currently pull data from the db into an excel sheet and do some analysis there to determine our vendors performance in delivering our product to us on time. What has been requested is to give them a 3 day buffer on the required date in my analysis but not changing data in the db. I had the dateadd function going until I realized it may be adding based on the 7 day week.

dateadd("d", 3, por.por_requireddate)

We want to be adding work days, not weekends or holidays. For example, required date = a Friday, we want the analysis to run against a required date of the following Wednesday and not Monday. Can anyone shed some insight on how this might be done?

Thanks!

 

[SQLSister]

Suggest a table where you store the workdays and then joining to it. You could havea job that updates it annually to get the next years set of dates. This way you would be able to exclude holidays as well as weekends from your analysis. Other wise, consider using DATEPART. Look it up in BOL.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

You may also want to take a look at this...

faq183-5075



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

If you want to handle holidays you're best having a table with all the valid workdays in it (or the reverse, a table with all the holidays and possibly weekends in it). There's no other practical or realistic way for handling complicated and potentially changing business rules about holidays.

If you could assume that any weekday was okay, this might do the trick:

SELECT OriginalDeliveryDate + 3 + CASE WHEN Datediff(dd, 0, OriginalDeliveryDate) % 7 BETWEEN 2 AND 6 THEN 2 ELSE 0 END

... so much for waiting so long to reply, other folks beat me to it!