title: How to create a sql query for sql reporting services 2008 to return the number of occurences of sick days
body: <p>How to create a sql query to return the number of occurrences of sick days.</p>
  
  <p>Where one occurrence is defined as consecutive days out sick. It would also be considered one occurrence if the a weekend fell within that time period or a holiday fell in that time period.</p>
  
  <p>Examples of what is consider one occurrence:</p>
  
  <ul>
  <li>A person is out sick Monday and Tuesday.</li>
  <li>A person is out sick on a Friday and the following Monday.</li>
  <li>A person is out sick Thursday, Friday is a Holiday, and Monday they are sick.</li>
  </ul>
  
  <p>For these examples it would be considered three occurrences.</p>
  
  <p>There is a table that contains the sick days (date) (one row for every sick day) and a table that contains the observed holiday dates.</p>
  
  <p>To simplify the tables and fields:</p>
  
  <pre><code>tbl_emp
     empid
     empname
  
  tbl_sick
     empid
     sickdate
  
  tbl_holiday
     holiday
  </code></pre>