Date 30 business days in future MS SQL

I have a table in my database called holidays with the federal holidays in it.  I need to add a column in another table that is a calculated date value of what is 30 business days in the future.  I know there is a lot out there on this but I need some assistance on asking our DBa admin to set this up.
atljarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

atljarmanAuthor Commented:
What this should have said was that I need the default value to be 30 business days in the future so that when a new item js added the date is calculated and stored.  The column to base the future dates is DateDone.  The calculation has to factor in weekends and the holidays in the table.  So for example if there are two holidays and 4 weekends in a 30 day period, it might be a date add if 40 days to account for the weekends and holidays.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
I'd suggest you refer to this article by jimhorn:

Date Fun, Part One:  Build your own SQL calendar table to perform complex date expressions

In it you will see steps to create a calendar table and how it may be used for working day calculations. Once you have the calendar table in existence often a scalar function is used to supply a working days result that can account for irregular holidays etc. and/or use the calendar to count days between 2 dates via queries.
0
PortletPaulfreelancerCommented:
by the way, if you do build a calendar table you may want to keep it and maintain it in a 'utility database' so you don't need to replicate it in many locations - but that's really up to you of course.
0
Scott PletcherSenior DBACommented:
My table too contains only non-work days, but it has ALL non-work days, including weekends, holidays (even those that "move"), plant shutdowns, whatever.

Then I use a scalar function to return +/- (0 to ~60) business days from a given date.

[Actually, I have *two* non-work-related date tables: a *control* table with non-work dates "definitions" and the dates table itself.  The control table is modified as needed, then the actual data table rows are (re)generated on demand by a proc for the date range specified.  This makes it both very easy to customize non-work days by customer (which is 100% required for us) and much easier to generate many future years' worth of non-work days.]

If you want details / actual code for that, let me know.
0
atljarmanAuthor Commented:
Did not completely anwser my question yet the walkthough was helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.