Solved

I need help using a CASE statement in my SQL query

Posted on 2014-07-31
8
266 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 14

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
 

Author Comment

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

mrotor
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now