title: Count status for items with particular flags at different times
body: <p>I have items which can be flagged with a status for a time range.  In each row, I have the start and stop times for a item with a flag.</p>
  
  <p>For example:</p>
  
  <pre><code>start   stop    itemid  flag
  1-Jan   1-Feb   1       a
  1-Feb   1-Mar   1       b
  1-Feb   1-Mar   2       a
  1-Jan   1-Mar   3       a
  1-Jan   null    4       a
  </code></pre>
  
  <p>What I need is a subcount of a count.  That is, for two dates given (say <code>1-Jan</code> and <code>1-Mar</code>), I need to count the number of items which had flag <code>a</code> at the first date but not flag <code>a</code> at the second date. (And other counts, with the other 3 combinations of <code>a</code> and not-<code>a</code> at those two dates.)</p>
  
  <p>I also need to somehow account for no flag at either date (but not both), but for now I can assume that no flag is <code>b</code>.</p>
  
  <p>Essentially, I need to count the following cases: (a,a), (a,b), (a,null), (b,a), (b,b), (b,null); for (x,y), where x is the flag for each item at the first date, and y is the flag at the second date.</p>
  
  <p>Existing, I can count how many flags <code>a</code> or <code>b</code> exist for a date.</p>
  
  <pre><code>select count(1)
  from table
  where start &lt;= '2014-02-15'
    and (stop &gt;= '2014-02-15' or stop is null)
    and flag = a
  </code></pre>