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,
WHEN ‘Item1’ THEN Quantity/32
WHEN ‘Item2’ THEN Quantity/33
END AS QtyPerBag,
WHEN ‘Item1’ THEN Price*32
WHEN ‘Item2’ THEN Quantity*33
END AS PricePerBag
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).