YellowLarry YellowLarry - 1 year ago 56
SQL Question

single CASE for multiple columns data

Item with quantity and price are queried from SQL server for Excel and Crystal Report. The quantity and price are bulk quantity (pounds). I need to convert it to bag quantity and bag price. Pounds per bag data is not in SQL server and it is different on each item. My item is around 20 only. I cannot create permanent or temporary table to store pounds per bag data in SQL Server. I can use CASE in SQL to calculate bag quantity and price. But, it needs two CASEs. How can I use one CASE or other method which can simplify the SQL and keep it simple to maintain? My current SQL.

SELECT Item, Quantity, Price,
CASE item
WHEN ‘Item1’ THEN Quantity/32
WHEN ‘Item2’ THEN Quantity/33

ELSE Quantity
END AS QtyPerBag,
CASE item
WHEN ‘Item1’ THEN Price*32
WHEN ‘Item2’ THEN Quantity*33

ELSE Price
END AS PricePerBag
FROM MasterTable

Answer Source

DhruvJoshi's approach is a good approach. Using the VALUES() constructor, it is even simpler:

SELECT mt.Item, mt.Quantity, mt.Price,
       mt.Quantity/factors.Factor AS QtyPerBag, 
       Price * Factors.factor AS PricePerBag
FROM MasterTable mt LEFT JOIN 
     (VALUES ('Item1', 32), ('Item2', 33)
     ) factors(item, factor)
     ON factors.item = mt.item;

Note: If you quantity is stored as an integer, then you should use decimal points for the factors (unless you want integer division).