CODEWITHSUNDEEP
sqlsql-server
theo
theo

Reputation: 299

SQL how to assign CASE under SELECT query to a variable?

I have this SQL SELECT query. 

SELECT  STOCK
    ,QTY*PRICE AS 'TOTAL'
    ,(QTY*PRICE)*RATE AS 'VALUATION'
    ,((QTY*PRICE)*RATE)+1 AS 'ADD_VALUATION'
    ,CASE WHEN GRP = 'A' THEN  100 - isnull(LDP_A,0)
     WHEN GRP = 'B' THEN  100 - isnull(LDP_B,0)
     WHEN GRP = 'C' THEN  100 - isnull(LDP_C,0)
     -- TO Z
     END AS 'LDP'
     ,(((QTY*PRICE)*RATE)+1) 
        / 
        (CASE WHEN GRP = 'A' THEN  100 - isnull(LDP_A,0)
        WHEN GRP = 'B' THEN  100 - isnull(LDP_B,0)
        WHEN GRP = 'C' THEN  100 - isnull(LDP_C,0)
        -- TO Z
        END) * RATE AS HTC
FROM STOCKBL

Is there a way to clean this redundancy of statements? How can I assign the CASE to a variable so I can replace this with more neat code.

Upvotes: 0

Views: 62

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use CTE or subquery as below:

;with cte as 
(
SELECT  STOCK
    ,QTY*PRICE AS 'TOTAL'
    ,(QTY*PRICE)*RATE AS 'VALUATION'
    ,CASE WHEN GRP = 'A' THEN  100 - isnull(LDP_A,0)
     WHEN GRP = 'B' THEN  100 - isnull(LDP_B,0)
     WHEN GRP = 'C' THEN  100 - isnull(LDP_C,0)
     -- TO Z
     END AS 'LDP'
     , RATE
FROM STOCKBL
)
SELECT Stock, total, Valuation, Valuation + 1 as  'ADD_VALUATION',
        ( (Valuation+1)/ LDP) * Rate ) as HTC from cte

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

In SQL Server you can use CROSS APPLY:

SELECT  STOCK
       ,t2.TOTAL AS 'TOTAL'
       ,t2.TOTAL*RATE AS 'VALUATION'
       ,(t2.TOTAL*RATE)+1 AS 'ADD_VALUATION'
       ,t1.LDP AS 'LDP'
       ,((t2.TOTAL*RATE)+1) / t1.LDP * RATE AS HTC
FROM STOCKBL
CROSS APPLY (
   SELECT CASE 
             WHEN GRP = 'A' THEN  100 - isnull(LDP_A,0)
             WHEN GRP = 'B' THEN  100 - isnull(LDP_B,0)
             WHEN GRP = 'C' THEN  100 - isnull(LDP_C,0)
             -- TO Z
          END AS LDP) AS t1
CROSS APPLY (SELECT QTY*PRICE AS TOTAL) AS t2

Upvotes: 3

Related Questions