Solved

I need help using a CASE statement in my SQL query

Posted on 2014-07-31
8
278 Views
Last Modified: 2014-08-07
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


thanks in advance,
mrotor
0
Comment
Question by:mainrotor
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 15

Accepted Solution

by:
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

Open in new window

0
 
LVL 12

Assisted Solution

by:Harish Varghese
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

Open in new window

0
 
LVL 33

Assisted Solution

by:ste5an
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;

Open in new window


p.s. always use a fallback.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

mrotor
0
 
LVL 12

Expert Comment

by:Harish Varghese
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

by:ste5an
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

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

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
awk and Pythagoras? 5 19
How to fix Datetime in MySQL? 4 49
Index and Stats Management-Specific tables 8 22
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question