CODEWITHSUNDEEP
sqlsql-servercase
HansDampf
HansDampf

Reputation: 121

SQL: How to use CASE WHEN output in new column calculation

I am trying to use the output of a CASE WHEN for a different column calculation, but I do not quite know how to.

Example:

CASE 
    WHEN (T.Delivery_Option = 'International Standard') AND (T.[Net_Qty] * T.[Unit_Charge]) < 50 THEN 4.99 
    WHEN (T.Delivery_Option = 'International Standard') AND (T.[Net_Qty] * T.[Unit_Charge]) >= 50 THEN 0
    ELSE 0
END AS 'Test',

What would I need to do, to use the output of "Test" in a different calculation such as:

T.[Net_Qty] / test

Upvotes: 1

Views: 196

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

SQL Server supports lateral joins. This is a nifty feature that allows you to define new columns in the FROM clause . . . and they can then be used throughout the query:

SELECT . . .,
       v.test,
       t.Net_Qty / NULLIF(v.Test, 0)
FROM T CROSS APPLY
     (VALUES (CASE WHEN T.Delivery_Option = 'International Standard' AND T.[Net_Qty] * T.[Unit_Charge] < 50 THEN 4.99 
                  WHEN T.Delivery_Option = 'International Standard' AND T.[Net_Qty] * T.[Unit_Charge] >= 50 THEN 0
                 ELSE 0
              END)
     ) v(Test)

Also note the use of NULLIF(). Test can easily take on 0 values, so you need to take that into account. In fact, the second WHEN is not needed because it returns the same value as the ELSE, so this can be simplified to:

FROM T CROSS APPLY
     (VALUES (CASE WHEN T.Delivery_Option = 'International Standard' AND T.[Net_Qty] * T.[Unit_Charge] < 50 THEN 4.99 
                 ELSE 0
              END)
     ) v(Test)

Upvotes: 2

Артем Карпец
Артем Карпец

Reputation: 84

You can use subquery like this

SELECT R.Test*5 FROM (

SELECT CASE 
    WHEN (T.Delivery_Option = 'International Standard') AND (T.[Net_Qty] * T.[Unit_Charge]) < 50 THEN 4.99 
    WHEN (T.Delivery_Option = 'International Standard') AND (T.[Net_Qty] * T.[Unit_Charge]) >= 50 THEN 0
    ELSE 0
END AS 'Test'
FROM TBL T

) as R

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521063

One option is to put your current logic into a CTE and then subquery it:

WITH cte AS (
    SELECT *,
        CASE WHEN Delivery_Option = 'International Standard' AND [Net_Qty] * T.[Unit_Charge] < 50 THEN 4.99 
             WHEN Delivery_Option = 'International Standard' AND [Net_Qty] * T.[Unit_Charge] >= 50 THEN 0
             ELSE 0 END AS test
    FROM yourTable
)

SELECT [Net_Qty] / test AS item
FROM cte;

Upvotes: 2

Mureinik
Mureinik

Reputation: 311163

The easiest way to reuse it would probably be to use a common table expression (CTE):

WITH cte AS (
    SELECT *, 
           CASE 
               WHEN (T.Delivery_Option = 'International Standard') AND (T.[Net_Qty] * T.[Unit_Charge]) < 50 THEN 4.99 
               WHEN (T.Delivery_Option = 'International Standard') AND (T.[Net_Qty] * T.[Unit_Charge]) >= 50 THEN 0
               ELSE 0
           END AS Test
    FROM T
)
SELECT Net_Qty / Test

Upvotes: 2

Related Questions