# I need help using a CASE statement in my SQL query

Hi Experts,
I created the following query in order to apply a 50% buffer to my company's On-hand inventory.  The formula for the buffer was applied to our entire inventory, using

the following query:

SELECT S.ItemNo, S.ALTItemNo,S.DESCRIPTION
ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .50), 0) AS OnHandInventory
FROM dbo.Inventory AS R
INNER JOIN ItemSKU AS S ON R.ItemNo = S.ItemNo
LEFT OUTER JOIN LOT AS L ON S.ItemNo = L.ItemNo
GROUP BY S.ItemNo, S.ALTItemNo, S.DESCRIPTION
ORDER BY S.ItemNo

Now I have been tasked with separating our inventory into 3 categorys (women's wear, men's wear, and kids wear), and apply a separate formula to each of the categories.

How can I do this?  Do I use a single query with CASE statements?  Please provide a syntax sample.  Thanks in advance.

Here are the formulas for each category:
women's wear:  ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .75), 0) AS OnHandInventory
NOTE: if OnHandInventory is less than 15 then OnHandInventory = 0

men's wear: ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .50), 0) AS OnHandInventory
NOTE: if OnHandInventory is less than 50 then OnHandInventory = 0

kids wear: ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .70), 0) AS OnHandInventory
NOTE: if OnHandInventory is less than 30 then OnHandInventory = 0

mrotor
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi,

You can apply CASE in single statement

``````SELECT S.ItemNo, S.ALTItemNo,S.DESCRIPTION,
CASE CATEGORY
WHEN 'womens wear'
THEN
CASE WHEN (ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .75), 0)) < 15 THEN 0 ELSE
ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .75), 0) END
WHEN 'mens wear'
THEN
CASE WHEN (ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .50), 0)) < 50 THEN 0 ELSE
ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .50), 0) END
WHEN 'KIDS wear'
THEN
CASE WHEN (ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .70), 0)) < 30 THEN 0 ELSE
ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * .70), 0) END
END
AS OnHandInventory
FROM dbo.Inventory AS R
INNER JOIN ItemSKU AS S ON R.ItemNo = S.ItemNo
LEFT OUTER JOIN LOT AS L ON S.ItemNo = L.ItemNo
GROUP BY S.ItemNo, S.ALTItemNo, S.DESCRIPTION
ORDER BY S.ItemNo
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

You can make it simpler using a Common Table Expression (CTE). Here we can split various components of your update into different columns in the defining SELECT and then use them as required in the final SELECT/UPDATE statement. Following query assumes that you have a column named ItemCategory in some table. You may convert the final SELECT into an UPDATE statement:
``````;WITH base AS
(SELECT S.ItemNo, S.ALTItemNo,S.DESCRIPTION,
ItemCategory,
CASE ItemCategory When 'Women' Then 0.75
When 'Men' Then 0.50
When 'Kids' Then 0.70
End AS BufferPercentage,
SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) as BalanceQty,
CASE ItemCategory When 'Women' Then 15
When 'Men' Then 50
When 'Kids' Then 30
End AS MinQtyOnHand
FROM dbo.Inventory AS R
INNER JOIN ItemSKU AS S ON R.ItemNo = S.ItemNo
LEFT OUTER JOIN LOT AS L ON S.ItemNo = L.ItemNo
WHERE ItemCategory in ('Women', 'Men', 'Kids')
GROUP BY S.ItemNo, S.ALTItemNo, S.DESCRIPTION, ItemCategory
ORDER BY S.ItemNo
)
Select B.ItemNo, B.ItemNo, B.ALTItemNo, B.DESCRIPTION, B.ItemCategory,
B.BufferPercentage, B.BalanceQty, B.MinQtyOnHand,
Case When FLOOR (B.BalanceQty * B.BufferPercentage) >= B.MinQtyOnHand
Then FLOOR (B.BalanceQty * B.BufferPercentage)
Else 0 End As OnHandInventory
from base B
``````
0
Senior DeveloperCommented:
Or
``````SELECT  S.ItemNo ,
S.ALTItemNo ,
S.[DESCRIPTION] ,
ISNULL(FLOOR(SUM(L.QTY - L.QTYALLOCATED - L.QTYONHOLD) * CASE ItemCategory
WHEN 'Women' THEN 0.75
WHEN 'Men' THEN 0.50
WHEN 'Kids' THEN 0.70
ELSE 1.0
END), 0) AS OnHandInventory
FROM    dbo.Inventory AS R
INNER JOIN ItemSKU AS S ON R.ItemNo = S.ItemNo
LEFT OUTER JOIN LOT AS L ON S.ItemNo = L.ItemNo
GROUP BY S.ItemNo ,
S.ALTItemNo ,
S.[DESCRIPTION]
ORDER BY S.ItemNo;
``````

p.s. always use a fallback.
0
Author Commented:
Quick question for all of you.  Can i use a temp table in a view?

mrotor
0
No, temp tables are not allowed in views. You can use permanent tables in tempdb if required. There may be alternatives if you can state your requirement.
0
Senior DeveloperCommented:
btw, are the ItemCategory values hard-coded or are they retrieved from a table? In the second case: Use a table to store the factors and JOIN the factor table.
0
Commented:
Can you describe the three tables? What attribute defines what is women, men, or kids?
0
Commented:
>>What attribute defines what is women, men, or kids?<<
Does an attribute such as category or itemcategory even exist?
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.