3

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?

Improve this question
0

2 Answers 2

Reset to default
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
Improve this answer
1
  • 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. Commented Dec 12, 2024 at 7:31
8

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;
Improve this answer
1
  • Thank you @Charlieface, very helpful Commented Dec 10, 2024 at 23:22

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.