Work Days without Holidays

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I am trying to calculate and expected completion date
based off a start date and the number of days to
completion. I only want to count work days (no weekends
and no holidays). I'm using the DateAdd function but
cannot find a good way to remove the holidays. Any
suggestions??

Thanks
 
You have to create a table with holidays dates (which would include
office-closed dates too like the boss's dog birthday)

Then you can subtract the count of dates in the table that fall between
sdate and edate:
select count(*) from offdates where offdate between sdate and edate

If you don't include sat and sun dates in the table, then you have to loop
from sdate to edate, use dateadd 1 day and get the day name and check...

The sophisticated way of doing this, and for any scheduling app, would be to
use a Time Dimension table (used in data warehousing) which would include
dates for at least 3 years with many other fields - with this you can get
what you want in a single SQL statement. If you are interested in this,
check out Ralph Kimball books.
 
Back
Top