Solved

I need help using a CASE statement in my SQL query

Posted on 2014-07-31
8
270 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

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

Suggested Solutions

Title # Comments Views Activity
Help Required 2 33
SQL Server 2012 r2 - Sum totals 2 25
Get number of Files in Directory and Sub Directories 2 42
Problem when I run a simple storeproc - help 4 17
In this article I will describe the Copy Database Wizard 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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