title: Determining Sick Periods from ranges
body: <p>I have the below set of data which represents employee sick/absence days over a period (12 months) of time, in a table named Absence:</p>
  
  <pre><code>Day         Date       DaysSick   OccasionsSick Notes
  Tuesday     2016-09-27     1                    Lisa A working today 
  Thursday    2016-09-29     1                    Lisa sick today Celeste 
  Thursday    2017-01-05     1                    Lisa sick today 
  </code></pre>
  
  <p>I would like to update the OccasionsSick column based upon the instances of being sick.  So i would have the following:</p>
  
  <pre><code>Day         Date       DaysSick   OccasionsSick Notes
  Tuesday     2016-09-27     1           1         Lisa A working today 
  Thursday    2016-09-29     1                     Lisa sick today Celeste 
  Thursday    2017-01-05     1           1         Lisa sick today 
  </code></pre>
  
  <p>So, the first two entries are the same period of sick leave, so i need a 1 in the first row, and the last entry is a separate sick period, so a 1 again.</p>
  
  <p>Now, in order to establish a sick period there would be a reference to a roster table containing the below:</p>
  
  <pre><code>Date         RosterType
  2016-09-27   Sick
  2016-09-28   Day Off
  2016-09-29   Sick
  2016-09-30   Normal
  </code></pre>
  
  <p>So the 27th and 29th were sick days, but the 28th was a standard day off, which is a likely occurrence, so using consecutive days is not an option.  I need to be able to look for sick days until a "normal" RosterType is found, this then breaks the sick period.  This 1 then needs to be assigned as per the desired result set.</p>
  
  <p>What is the best way of updating the data here?  I have come up with a big blank on this, apart from the logic of determining a sick period.</p>
  
  <p>I am presenting this data in Excel with VBA, so it could also be easier to assign the sick periods in VBA, as opposed to SQL for the raw data</p>