Use a new variable to CASE WHEN

Question

I have the following table:

CREATE TABLE my_table 
(
    the_debt_id varchar(6) NOT NULL, 
    the_debt_amount int NOT NULL, 
    the_debt_date date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS01', '180', '12/2/2019'), 
       ('LMUS01', '200', '11/2/2019'), 
       ('LMUS01', '300', '13/2/2019'), 
       ('LMUS02', '100', '10/2/2019'), 
       ('LMUS02', '150', '12/2/2019')

And I want to create a variable named debt_taxed, and then a variable based on debt_taxed when on certain limits to name it as "exceed", "contained" or "rolling:

SELECT the_debt_amount * 1.18 as debt_taxed, 
CASE WHEN the_debt_taxed >= 250 THEN 'Exceed' 
WHEN (the_debt_taxed < 250 and the_debt_taxed >= 200) THEN 'contained' 
ELSE 'rolling' 
END AS status 
FROM my_table

But I get an error saying that the_debt_taxed doesn't exist.

Please, any help will be appreciated.

Answer 1

Just replace debt_taxed in your CASE with the actual calculation.

SELECT
    debt_taxed = the_debt_amount * 1.18
    ,status = CASE
                  WHEN the_debt_amount * 1.18 >= 250 THEN 'Exceed'
                  WHEN
                  (
                      the_debt_amount * 1.18 < 250
                      AND the_debt_amount * 1.18 >= 200
                  ) THEN 'contained'
                  ELSE 'rolling'
              END
FROM my_table;

If you wanted to use a CTE (common table expression), you'd do something like this:

;WITH CTE AS
(
    SELECT
        debt_taxed = m.the_debt_amount * 1.18
        ,m.the_debt_id
        ,m.the_debt_date
    FROM my_table m
)
SELECT 
    c.debt_taxed
    ,c.the_debt_id
    ,c.the_debt_date
    ,status = CASE
                  WHEN c.debt_taxed >= 250 THEN 'Exceed'
                  WHEN
                  (
                      c.debt_taxed < 250
                      AND c.debt_taxed >= 200
                  ) THEN 'contained'
                  ELSE 'rolling'
              END
FROM CTE c;