leveraging cross apply for reusing calculated column

Question

I've a productTable from where I'm reading a column, making a calculation and re-using the same calculation going forward.

INSERT INTO #tempTable
(
    productName,
    quantity,
    priceInUSD,
    isValidPrice,
    discountDescription
)
SELECT  TOP (1000) WITH TIES
        pt.productName,
        pt.quantity,
        pt.priceInUSD,
        IIF(pt.priceInUSD >= 10000 OR pt.priceInUSD < 0 , 0, 1) AS isValidPrice,
        IIF(isValidPrice > 0, '10% discount allowed', 'Max $100 discount allowed' )
FROM    ProductTable pt
WHERE   pt.type like 'stock product'
ORDER BY pt.purchaseId ASC

Is there a way I can calculate isValidPrice from a column of productTable and use isValidPrice going forward?

    IIF(pt.priceInUSD >= 10000 OR pt.priceInUSD < 0, 0, 1) AS isValidPrice,
    IIF(isValidPrice > 0, '10% discount allowed', 'Max $100 discount allowed' )

Answer 1

You can just move the expression to the FROM clause:

SELECT  TOP (1000) WITH TIES
        pt.productName,
        pt.quantity,
        pt.priceInUSD,
        v.isValidPrice,
        (CASE WHEN v.isValidPrice > 0 THEN '10% discount allowed' ELSE 'Max $100 discount allowed' END)
FROM ProductTable pt CROSS APPLY
     (VALUES (CASE WHEN pr.priceInUSD >= 10000 OR pr.priceInUSD < 0 THEN 0 ELSE 1 END)
     ) v(isValidPrice)
WHERE  pt.type like 'stock product'
ORDER BY pt.purchaseId ASC;

Note: I recommend using standard SQL syntax instead of bespoke functionality, so I changed the IIF() to a CASE expression.