Solved

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

Posted on 2014-07-31
283 Views
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
0
Question by:mainrotor
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +2

LVL 15

Accepted Solution

Vikas Garg earned 167 total points
ID: 40233437
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

LVL 12

Assisted Solution

Harish Varghese earned 167 total points
ID: 40233514
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

LVL 33

Assisted Solution

ste5an earned 166 total points
ID: 40233644
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 Comment

ID: 40234676
Quick question for all of you.  Can i use a temp table in a view?

mrotor
0

LVL 12

Expert Comment

ID: 40235975
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

LVL 33

Expert Comment

ID: 40236012
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

LVL 32

Expert Comment

ID: 40236191
Can you describe the three tables? What attribute defines what is women, men, or kids?
0

LVL 32

Expert Comment

ID: 40236193
>>What attribute defines what is women, men, or kids?<<
Does an attribute such as category or itemcategory even exist?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Introduction to Processes