Multiplication of Case Column in SQL

Question

I have a query where i have two column based on Case, i wish to multiply Qty_Sold * UnitPrice to get Result

SELECT CASE 
        WHEN RPT_ITM_D.F1034 = 3
            THEN F64
        ELSE 0
        END AS Qty_Sold
    ,CASE 
        WHEN [POS_TAB].F81 = 1
            THEN Cast(CONVERT(DECIMAL(10, 3), F65 / NULLIF(F64, 0)) AS NVARCHAR) * cast(1.15 AS NUMERIC(10, 3))
        ELSE Cast(CONVERT(DECIMAL(10, 3), F65 / NULLIF(F64, 0)) AS NVARCHAR)
        END AS UnitPrice
FROM [dbo].[RPT_ITM_D]
LEFT OUTER JOIN [dbo].[POS_TAB] ON (RPT_ITM_D.F01 = POS_TAB.F01)
LEFT OUTER JOIN [dbo].SDP_TAB ON (POS_TAB.F04 = SDP_TAB.F04)
LEFT OUTER JOIN [dbo].DEPT_TAB ON (SDP_TAB.F03 = DEPT_TAB.F03)
WHERE RPT_ITM_D.F1034 IN (
        3
        ,3012
        )
    AND RPT_ITM_D.F254 = convert(VARCHAR, getdate(), 101)

I have tried This Example but it not working.

Please advice

Note that i will be needing other tables in the Outer Join as well

You need another level. Wrap the case part up in a derived table (or cte). — jarlh, Sep 15 '17 at 07:56

score 4 · Accepted Answer · answered Sep 15 '17 at 07:57

You can try to use CTE as:

With CTE as
(SELECT CASE 
        WHEN RPT_ITM_D.F1034 = 3
            THEN F64
        ELSE 0
        END AS Qty_Sold
    ,CASE 
        WHEN [POS_TAB].F81 = 1
            THEN Cast(CONVERT(DECIMAL(10, 3), F65 / NULLIF(F64, 0)) AS NVARCHAR) * cast(1.15 AS NUMERIC(10, 3))
        ELSE Cast(CONVERT(DECIMAL(10, 3), F65 / NULLIF(F64, 0)) AS NVARCHAR)
        END AS UnitPrice
FROM [dbo].[RPT_ITM_D]
LEFT OUTER JOIN [dbo].[POS_TAB] ON (RPT_ITM_D.F01 = POS_TAB.F01)
LEFT OUTER JOIN [dbo].SDP_TAB ON (POS_TAB.F04 = SDP_TAB.F04)
LEFT OUTER JOIN [dbo].DEPT_TAB ON (SDP_TAB.F03 = DEPT_TAB.F03)
WHERE RPT_ITM_D.F1034 IN (3,3012)
    AND RPT_ITM_D.F254 = convert(VARCHAR, getdate(), 101)
)

SELECT
 (Qty_Sold * UnitPrice) AS result
FROM CTE

score 2 · Answer 2 · answered Sep 15 '17 at 07:57

2

Just wrap your query into a subquery, in this way:

SELECT t.*,
       Qty_Sold * UnitPrice As my_result
FROM (
    SELECT CASE  .....
    ..... your query goes here

) t

answered Sep 15 '17 at 07:57

krokodilko

35,300
7
55
79

The old [KISS principal](https://en.wikipedia.org/wiki/KISS_principle) works every time. – Paul Sep 15 '17 at 08:02

Multiplication of Case Column in SQL

2 Answers2