SQL_Case and When

I have a data set where I need to calculate minimum amount due. I have used the below code

SELECT *, Sales - Reversal AS Net_Sale, charge1 - charge1_rev AS Net_Fee1,

CASE WHEN Net_Sale < 500 THEN Net_Sale
ELSE 0.1*Net_Sale END 
AS Min_Sale_amt,

Min_Sale_amt + Net_Fee1 AS Min_AmountDue
FROM Table

The intention is to bring minimum amount due as Rs. 500 if the net sales is less than Rs. 500, if the net sales is more than Rs. 500 then I need to calculate 10% of Net sales.

Sample data for reference

create table sales(
sales int,
Reversal int,
charge1 int,
charge1_rev int
);

Insert into sales values(1000,200,10,0);
Insert into sales values(495,0,10,0);

For some reason I’m unable to get the result. Help would be much appreciated!

Answer

Compute the aliases in a CTE first, then subquery that to get the result you want:

WITH cte AS (
    SELECT *, Sales - Reversal AS Net_Sale,
              charge1 - charge1_rev AS Net_Fee1,
              CASE WHEN Sales - Reversal < 500
                   THEN Sales - Reversal
                   ELSE 0.1*(Sales - Reversal) END AS Min_Sale_amt
    FROM sales
)

SELECT *, Min_Sale_amt + Net_Fee1 AS Min_AmountDue
FROM cte;