Future Date - Taking w/e & holidays into account

[applecake]

Hey all,

I'm designing a query that must calculate a future date based on a recorded date. Unfortunately it's not as simple as just using adddate (unless I was using Excel!). Here's my requirements:

Future Date = [StartDate] + 45 business days

I am able to take weekends into account, but haven't been able to figure out how to bring holidays into the equation. I have an existing holidays table that I am using for another query within the database (calculates # of business days between 2 existing dates).

Hope this makes sense... I've been staring at it all day so I may not be thinking clearly anymore.

Any help is greatly appreciated.

 

[KernelK]

If you already have the weekends figured out and have a table with the holidays in it, you should be able to query the Holiday table for any holiday Dates that are between your Start Date and your calculated end date (45 days out minus weekends). You would then push your date out by the number of matches from your calculated end date to get the final calcuation, because any match would mean that there is a holiday in between your range and each holiday is going to push you out one more day.

Heck, you wouldn't even need a query, just use the DCount function.