title: "Conditional Conditions" in a WHERE clause (Which condition to apply depends on a "mode" flag)
body: <p>I have a situation which I have solved in two different ways, but was wondering what people thought about the options, and if they have any other alternatives...</p>
  
  <p>The system is processing "intervals" of data.  </p>
  
  <ul>
  <li>All the data is allocated to an "interval"  </li>
  <li>The interval is represented by an "interval_start" DATETIME in the Fact table  </li>
  <li>A Dimenstion table holds the duration of the "interval" (can be different for different entities)  </li>
  <li>Also in the Dimension table is a "mode" flag</li>
  </ul>
  
  <p><br>
  The following rules are needed in a SQL Query...</p>
  
  <p><strong>Mode 0</strong><br>
  If a record entry is in the fact table, it can be processed.<br>
  (No restrictions.)  </p>
  
  <p><strong>Mode 1</strong><br>
  A record is only valid for processing if the "interval_start" is in the past.
  (Intervals of time that have already begun, but not necessarily finished.)</p>
  
  <p><strong>Mode 2</strong><br>
  A record is only valid for processing if the whole "interval" is in the past.
  (Intervals of time that have finished.)</p>
  
  <p><br>
  The first WHERE clause created for this was as follows...</p>
  
  <pre><code>WHERE
      getDate() &gt;=
          CASE [table].mode
              WHEN 0 THEN 0
              WHEN 1 THEN [log].interval_start
              WHEN 2 THEN [log].interval_start + [table].interval_period
              ELSE        NULL
          END
  </code></pre>
  
  <p><br>
  There was a concern that this would obsfuscate indexes from optimising the clause.  The alternative was to use multiple AND/OR conditions.</p>
  
  <pre><code>WHERE
     ([table].mode = 0 AND getDate() &gt;= 0)
  OR ([table].mode = 1 AND getDate() &gt;= [log].interval_start)
  OR ([table].mode = 2 AND getDate() &gt;= [log].interval_start + [table].interval_period)
  </code></pre>
  
  <p><br>
  Obviously, which will perform best will depend on the data and indexes, etc.  But does anyone have any opion or alternative for what I'm currently describing as "conditional conditions"?  :)</p>
  
  <p>Cheers,
  Mat.</p>