SQL Query to Count Number of Days, Excluding Holidays/Weekends

I have a "workDate" field and a "receivedDate" field in table "tblExceptions." I need to count the number of days beteen the two. workDate always comes first - so, in 开发者_如何学JAVAeffect, it's kind of like workDate is "begin date" and receivedDate is "end date". Some exclusions make it tricky to me though:

First, I need to exclude holidays. i do have a table called "tblHolidays", with one field - holidayDate. I have holidays stored up through next year, so maybe I can incorporate that into the query?

Also, most flummoxing is that work dates can never occur on weekends, but received dates can. So, i somehow need to exclude weekends when i'm counting, but allow for a weekend if the received date happens to fall on a saturday or sunday. so, if the work date is June 3rd, 2011, and received date is June 11th, 2011, the count of valid days would be 7, not 9.

Any help on this is much appreciated. Thanks!

Something like this should give you the number of days with the holidays subtracted:

select
  days = datediff(day, workDate, receivedDate)
  - (select count(*) from tblHolidays where holidayDate >= workDate and holidayDate < receivedDate)
from
  tblExceptions

Note that the date functions differ between database systems. This is based on MS SQL Server, so it may need adapting if you are using some other database.

If you have a table full of dates to include (non-weekends, non-holidays, etc.) and you knew when the 'begin' date and the 'end' date is, then you can do something roughly like this:

select count(*) from tblIncludedDates where beginDateValue <= dateField and endDateValue >= dateField

to get the number of valid days between those dates.