"Conditional Conditions" in a WHERE clause (Which condition to apply depends on a "mode" flag)

Question

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...

The system is processing "intervals" of data.

• All the data is allocated to an "interval"
• The interval is represented by an "interval_start" DATETIME in the Fact table
• A Dimenstion table holds the duration of the "interval" (can be different for different entities)
• Also in the Dimension table is a "mode" flag

The following rules are needed in a SQL Query...

Mode 0
If a record entry is in the fact table, it can be processed.
(No restrictions.)

Mode 1
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.)

Mode 2
A record is only valid for processing if the whole "interval" is in the past. (Intervals of time that have finished.)

The first WHERE clause created for this was as follows...

WHERE
    getDate() >=
        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

There was a concern that this would obsfuscate indexes from optimising the clause. The alternative was to use multiple AND/OR conditions.

WHERE
   ([table].mode = 0 AND getDate() >= 0)
OR ([table].mode = 1 AND getDate() >= [log].interval_start)
OR ([table].mode = 2 AND getDate() >= [log].interval_start + [table].interval_period)

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"? :)

Cheers, Mat.

Answer 1

How about:

WHERE
     interval_start <= CASE mode
                         WHEN 0 THEN '9999-12-31'
                         WHEN 1 THEN GETDATE()
                         WHEN 2 THEN GETDATE() - interval_period
                       END

Answer 2

Another option would be to run three separate queries and UNION them together. Depending on your index and table structure, that might lead to more efficient SQL than OR-ing together the clauses in the WHERE clause or using CASE statements, although it would purely be gaming the optimizer.

I'd test all three approaches and pick the one that works best for you.