title: SQL Case select
body: <p>maybe someone can help me</p>
  
  <p>i have an SQL datebase that is used for logging employees leave. we have some many different shifts, that on our employees table we have 7 fields that represent the days they work. these are a bit data type, 1 for working that day, and 0 for not working.</p>
  
  <p>a second table has all the employees leave. containing employee id, leave date and reason. </p>
  
  <p>i can easily query the employees table and get how many people are to work on any given day of the week, and i can easily query the leave table to see how many people are off on a given date.</p>
  
  <p>what i looking to do is based on the day of the week in the leave table, count how many people are supposed to be in on that day.</p>
  
  <p>the code im trying to make work is</p>
  
  <pre><code>select TBL_Leave.Leave_Date AS 'Date',
      datepart(weekday,TBL_Leave.Leave_Date) - 1 AS 'Day Of Week',
      count(TBL_Leave.Leave_Date) AS 'Total Off',
  
      case
          when datepart(weekday,TBL_Leave.Leave_Date) - 1 = 5 then select SUM(convert(int,Mon)) from TBL_Employees)
          else 'Flase'
      end
  
  from TBL_Leave
  
  where Leave_Date between '2010-01-01' AND '2010-12-31'
  
  group by TBL_Leave.Leave_Date
  </code></pre>
  
  <p>but sure enough, it dont work. </p>
  
  <p>im trying to count the number of people working from one table based the the day of the week from a field in another.</p>
  
  <p>any help anyone can give will be great</p>
  
  <p>cheers
  Paul</p>
  
  <hr>
  
  <p>i have this query to get how many people are off on any date</p>
  
  <pre><code>select TBL_Leave.Leave_Date AS 'Date',
      datepart(weekday,TBL_Leave.Leave_Date) - 1 AS 'Day Of Week',
      count(TBL_Leave.Leave_Date) AS 'Total Off'
  
  from TBL_Leave
  
  where Leave_Date between '2010-01-01' AND '2010-12-31'
  
  group by TBL_Leave.Leave_Date
  </code></pre>
  
  <p>and this to see how many people are in on any day</p>
  
  <pre><code>select SUM(convert(int,Mon)) as 'Monday',
         SUM(convert(int,Tue)) AS 'Tuesday',
         SUM(convert(int,Wed)) AS 'Wednesday',
         SUM(convert(int,Thu)) AS 'Thursday',
         SUM(convert(int,Fri)) AS 'Friday',
         SUM(convert(int,Sat)) AS 'Saturday',
         SUM(convert(int,Sun)) AS 'Sunday'
  
  from TBL_Employees
  
  where planned = 1
  </code></pre>