Geek Answers Handbook

"Conditional conditions" in the WHERE clause (which condition to apply depends on the "mode" flag)

I have a situation that I decided in two different ways, but I wondered what people think about the options, and if they have other alternatives ...

The system processes the "intervals" of the data.

  • All data is assigned to the "interval"
  • The interval is represented by the interval_start DATETIME table in the fact table
  • Dimenstion table contains the duration of the "interval" (may be different for different objects)
  • Also in the table Dimension is the flag "mode"


The following rules are required in SQL Query:

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

Mode 1
Recording is only valid for processing if "interval_start" is in the past. (Intervals that have already begun but are not necessarily completed.)

Mode 2
Recording is only valid for processing if the entire “interval” is in the past. (Time intervals that are over.)


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


Concern was expressed that this would obscure the indices from optimizing the situation. An alternative was to use several 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, the best result will depend on data and indexes, etc. But does anyone have any option or alternative to what I now describe as “conditional conditions”? :)

Cheers, Mat.

+5
2

- . SQL, OR- WHERE CASE, .

, .

+3

:

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

More articles:


All Articles