Variable SQL join operator using case statement

Question

I have a fact table that contains sick leave values for employees (Table 1) and I need to slot each value into one of the bands (Table 2)

enter image description here

enter image description here

The problem is that the fact table contains some negative values that represent non-numeric bands. Those values are -6666,-7777,-8888,-9999 and I can't use a between operator for them because they'll also be picked up in the first band in Table 2.

Is it possible to use a case statement to change the operator for those values? Something like this:

SELECT * FROM SickLeave lv
INNER JOIN SickLeaveBands lb
ON 
CASE WHEN lb.SickLeave in (-6666,-7777,-8888, -9999)
THEN lb.SickLeave = MinHours
ELSE lb.SickLeave BETWEEN MinHours AND MaxHours
END

I was able to get around this by joining using a union query for the right side of the join, but is there a simpler solution that also avoids using dynamic sql?

Answer 1

Case returns a value expression, but that does not mean that it cannot be used in this case. Just need to have it return a value than is then used in a comparison, as this:

SELECT
 *
FROM SickLeave lv
INNER JOIN SickLeaveBands lb
 ON 
 CASE
 WHEN lb.SickLeave IN (-6666,-7777,-8888, -9999) AND lb.SickLeave = MinHours THEN 1
 WHEN lb.SickLeave NOT IN (-6666,-7777,-8888, -9999) AND lb.SickLeave BETWEEN MinHours AND MaxHours THEN 1
 ELSE 0
 END = 1

• Annette: Thanks @Mario. I take your point. I had already gone down the UNION route and your method shaved two minutes off my query's run-time. The db I'm working with is relatively small, but I do have a lot of dimensions to join, so I'll try to compare methods as I progress.

Answer 2

CASE is not a statement, it returns a value expression, so it's not going to work in a situation where you need a logic boolean result. You can just use OR instead

SELECT *
FROM SickLeave lv
INNER JOIN SickLeaveBands lb
 ON (
 lb.SickLeave IN (-6666,-7777,-8888, -9999) AND lb.SickLeave = lv.MinHours
 OR
 lb.SickLeave NOT IN (-6666,-7777,-8888, -9999) AND lb.SickLeave BETWEEN lv.MinHours AND lv.MaxHours
 );

Having said that, it's probably inefficient to use such an OR condition, as indexes cannot be used properly. You should probably change this to some kind of UNION ALL.

Either

SELECT *
FROM SickLeave lv
INNER JOIN SickLeaveBands lb
 ON lb.SickLeave = lv.MinHours
WHERE lb.SickLeave IN (-6666,-7777,-8888, -9999)
UNION ALL
SELECT *
FROM SickLeave lv
INNER JOIN SickLeaveBands lb
 ON lb.SickLeave BETWEEN MinHours AND MaxHours
WHERE lb.SickLeave NOT IN (-6666,-7777,-8888, -9999);

Or:

SELECT *
FROM SickLeave lv
CROSS APPLY (
 SELECT *
 FROM SickLeaveBands lb
 WHERE lb.SickLeave = lv.MinHours
 AND lb.SickLeave IN (-6666,-7777,-8888, -9999)
 UNION ALL
 SELECT *
 FROM SickLeaveBands lb
 WHERE lb.SickLeave BETWEEN lv.MinHours AND lv.MaxHours
 AND lb.SickLeave NOT IN (-6666,-7777,-8888, -9999)
) lb;

• Annette: Thank you @Charlieface, very helpful