Solved

I need help using a CASE statement in my SQL query

Posted on 2014-07-31
8
285 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
[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
  • Learn & ask questions
  • 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 34

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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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 34

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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