I have a table what has a set of start_dt and end_dt. What I'm looking for is within SQL figuring out the number of working days between two dates. The working days would only be Monday - Friday not including holidays. Any help would be great.


Thanks in Advance
Jonsey

So you want a 1 for every Mon thru friday and a zero for every sat and sunday.

There are two different methods for doing this. One is to create a date table and put in one row for every day and have a collumn with a value of 1 if its a work day or not. Then you relate on day to that table and sum this column.

Another shortcut is to do a case statement and sum it. I don't have a SQL server available to me to test it right now but here is the basic idea.

sum (case somedate when monday = 1
                   when tuesday = 1
                   when wed = 1
                   when thur = 1
                   when fri = 1
                   else 0
     end
    )

You can determine the mon-friday using date part. It's not the exact code but this should give you the idea.