[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

I need help using a CASE statement in my SQL query

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
mainrotor
Asked:
mainrotor
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
Vikas GargBusiness Intelligence DeveloperCommented:
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
 
Harish VargheseProject LeaderCommented:
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
 
ste5anSenior DeveloperCommented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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

mrotor
0
 
Harish VargheseProject LeaderCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
awking00Commented:
Can you describe the three tables? What attribute defines what is women, men, or kids?
0
 
awking00Commented:
>>What attribute defines what is women, men, or kids?<<
Does an attribute such as category or itemcategory even exist?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now