SQL Field Calculation based on CASE if then else

Question

I am trying to calculate a field. For example

select **sum(New)** as NewCST where WHERE PO ='L22411301' AND ItemNo IN('1730','1343').

Instead of the sum I have this whole case statement to implement. I have not done a formula calculation that has this many conditions. So any help is appreciated.

So far I got this:

SELECT *
FROM (SELECT 
    Case
        When  PO_SIZE like ‘%lb%’ Then  CONVERT(decimal(13,4), NET_COST / TOTAL_WEIGHT)
        When PO_SIZE like ‘%LB%’ Then  CONVERT(decimal(13,4), NET_COST / TOTAL_WEIGHT)
        Else  CONVERT(decimal(13,4), ADJ_EXT_NET_COST / LINE_QUANTITY)        
from [FirstStrike_Retail].[custom].[Whse_Line_Item] )End AS NewCst.
WHERE PO ='L22411301' AND ItemNo IN('1730','1343')

fyi: See [here](http://stackoverflow.com/questions/14962419/is-the-like-operator-case-sensitive-with-ms-sql-server) for case-sensitivy of `LIKE`. You can also combine `LIKE` tests, e.g. `when PO_Size like '%lb%' or PO_Size like '%oz%' then Net_Cost / Total_Weight` so that you don't need to repeat the calculation. — HABO, Sep 06 '13 at 18:11

score 3 · Answer 1 · answered Sep 06 '13 at 17:35

There is no need for the outer select statment. Also, you need to close the CASE statement with the END keyword before you get to the FROM clause.

Here is a tidied-up version of your code.

SELECT  Case    When PO_SIZE like ‘%lb%’ Then  CONVERT(decimal(13,4), NET_COST / TOTAL_WEIGHT)
                When PO_SIZE like ‘%LB%’ Then  CONVERT(decimal(13,4), NET_COST / TOTAL_WEIGHT)
                Else  CONVERT(decimal(13,4), ADJ_EXT_NET_COST / LINE_QUANTITY)
        End AS NewCst
from    [FirstStrike_Retail].[custom].[Whse_Line_Item] )
WHERE   PO ='L22411301' 
AND     ItemNo IN('1730','1343')

CRAFTY DBA · Answer 2 · 2013-09-06T18:19:29.233

The first solution definitely has syntax errors. I guess the question I have do you want a calculation at the row level (declan_k) or at the grouping level (sonam).

However, there is some redundancy in the above code (solutions). Also, a "Lb" for a PO_SIZE will not calculate correctly.

Let's have some fun!

I always like to create a sample database when doing these answers.

-- Sample table
CREATE TABLE #WHSE_LINE_ITEM
(
  ITEM_ID INT,
  PO_NUM VARCHAR(10),
  ITEM_NUM VARCHAR(10),
  NET_COST REAL,
  ADJ_EXT_NET_COST REAL,
  LINE_QUANTITY INT,
  TOTAL_WEIGHT REAL,
  PO_SIZE VARCHAR(10)
);

-- Sample data
INSERT INTO #WHSE_LINE_ITEM
VALUES
(1, 'L22411301', '1730', 200.00, 0.0, 3.0, 15.0, 'LB'),
(2, 'L22411301', '1730', 150.00, 0.0, 3.0, 30.0, 'lb'),
(3, 'L22411301', '1343', 100.00, 0.0, 4.0, 0.0, 'LN');

I did a calculation at the row level. I hope this is what your were looking for.

-- Calculate field using case stmt
SELECT 
  CASE
    WHEN (LOWER(PO_SIZE) like '%lb%') THEN 
        CAST((NET_COST / TOTAL_WEIGHT) AS DECIMAL (13,4))
    ELSE
        CAST((NET_COST / LINE_QUANTITY) AS DECIMAL (13,4))
  END AS NEWCST, 
  *
FROM 
  #WHSE_LINE_ITEM
WHERE 
  PO_NUM = 'L22411301' AND 
  ITEM_NUM IN('1730','1343');

When looking at code, I always try reduce the size to a minimum. If you are testing for all combinations of pound (Lb, lB, lb, or LB), why not convert to a lower case string before the pattern match?

The resulting query results are below.

enter image description here

Food for thought, if you are running this query many times over the day, you might want to have a persisted computed field.

Check out the MSDN entry for 2012. This will save the engine from having to calculate the field every time at the expense of storing the results on disk.

score 0 · Answer 3 · answered Sep 06 '13 at 17:40

select 
   sum(Case When  PO_SIZE like ‘%lb%’ Then  CONVERT(decimal(13,4), NET_COST / TOTAL_WEIGHT)
   When PO_SIZE like ‘%LB%’ Then  CONVERT(decimal(13,4), NET_COST / TOTAL_WEIGHT)
   Else  CONVERT(decimal(13,4), ADJ_EXT_NET_COST / LINE_QUANTITY)END)NewCst 
from 
   [FirstStrike_Retail].[custom].[Whse_Line_Item] 
WHERE 
   PO ='L22411301' AND ItemNo IN('1730','1343')

SQL Field Calculation based on CASE if then else

3 Answers3