View Single Post
  #2 (permalink)  
Old February 5th, 2008, 05:37 PM
robprell robprell is offline
Friend of Wrox
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts

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

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

Reply With Quote