In my job, I often come across SQL queries that include large CASE blocks. Often, the same decision logic is repeated for multiple columns within the query, making the SQL very verbose.
Now, I don't really have an opinion about whether CASE blocks are good or bad practices; I tend to think that whatever works is the right choice, and if needed, you can always refactor and/or optimize later. However, there is usually a purely mathematical way to represent a CASE block as a single expression that may or may not improve performance and readability of the code.
For example, consider the following:
CASE WHEN MONTH_ID < 7
AND MONTHLY_FACTOR <> 0 THEN MONTHLY_FACTOR * PLAN_SLS_H1
WHEN MONTH_ID < 7
AND MONTHLY_FACTOR = 0 THEN PLAN_SLS_H1
WHEN MONTH_ID > 6
AND MONTHLY_FACTOR <> 0 THEN MONTHLY_FACTOR * PLAN_SLS_H2
WHEN MONTH_ID > 6
AND MONTHLY_FACTOR = 0 THEN PLAN_SLS_H2
END AS PLAN_MONTH_SLS2
This code has two conditions that are being handled. First, depending on whether the month is in the first or second half, the value in PLAN_SLS_H1 or PLAN_SLS_H2 is used. Second, if MONTHLY_FACTOR is not zero, then the first/second half value is multiplied by the monthly factor. The combination of this logic results in 4 possible branches in the CASE block.
How can we mathematically tell in the month is in the first half or second half? Well, the month number (range 1-12) divided by 7 will either be 0 <= x < 1 (for the first half) or 1 <= x < 2 (for the second half). Using the FLOOR() function, this means that the first half can be definitively be represented as 0, and the second half as 1.
FLOOR(MONTH_ID / 7.0)
Given that we can represent each half as 0 or 1, how can we now get the appropriate planned sales value for the half? To accomplish this, we want to somehow multiply each half's value by 1 if the month is in that half, or 0 if the month is not in that half, and then add these two results together. Because the non-half member will become zero, we'll be left with the correct half's value, thus satisfying part of what the CASE logic was trying to do.
The second half is already taken care of, because a "1" from the FLOOR() operation indicates that we're in the second half. But, the FLOOR() operation returns a zero for the first half. To convert this, simply subtract the half indicator from 1 (first half would be 1 - 0 = 1, second half would be 1 - 1 = 0).
( 1 - FLOOR(MONTH_ID / 7.0)) * PLAN_SLS_H1 )
+ ( FLOOR(MONTH_ID / 7.0) * PLAN_SLS_H2 )
Finally, we need a way to multiply by the MONTHLY_FACTOR only when the factor is not zero. Stated another way, we need to multiply by the sum of the MONTHLY_FACTOR and either 1 (when the factor is zero) or 0 (when the factor is not zero). This way, we'll always multiply by something, which will be either the factor or by one.
To tell if the MONTHLY_FACTOR is zero or not, we can use the SIGN function. This returns -1, 0, or 1 depending on whether the input was negative, zero, or positive. Wrapping this in the ABS function results in a 0 or a 1, indicating that MONTHLY_FACTOR was either zero or not (respectively). Like the "First Half" logic above, these values are the opposite of what we actually need, so subtract the result from 1:
1 - ABS(SIGN(MONTHLY_FACTOR))
This new result will then be a 1 if MONTHLY_FACTOR is zero, or 0 is MONTHLY_FACTOR is not zero. All that remains is to add MONTHLY_FACTOR itself to this number, and we'll have either MONTHLY_FACTOR or 1, which can then be multiplied by the first/second half value from above.
In the end, the following expression is equivalent to the CASE block listed at the beginning of this post:
(
(
(( 1 - FLOOR(MONTH_ID / 7.0)) * PLAN_SLS_H1 )
+ ( FLOOR(MONTH_ID / 7.0) * PLAN_SLS_H2 )
)
* ( 1 - ABS(SIGN(MONTHLY_FACTOR)) + MONTHLY_FACTOR )
) AS PLAN_MONTH_SLS1
After going through this conversion exercise to see if I could simplify the SQL code, I decided to just leave the existing CASE block in place. I wasn't the first developer to look at this code, and I'm going to be sure that I'm not the last developer to ever work with it. As a result, readability was more important than conciseness and performance at this point. I felt that the intended logic was clearer in the CASE form rather than the expressions form, so consider this as my gift to the next developer. ^_^