Store inventory/Liquidations and purchases

Question

Post reply

Store inventory/Liquidations and purchases

Chrissy321

SSCoach

Points: 15763
More actions
April 23, 2014 at 5:09 pm

#288166

Hello All,
See code and desired results below.
Given a groups of stores one of which is deemed the model I would like to compare all the non-model stores against the the model and identify any inventory actions needed which would make the inventory of all stores consistent.
/*
Desired results
Purchase,South,3
Liquidate,East, 4
Purchase,West,1
Liquidate,West, 5
Liquidate,West, 6
*/
CREATE TABLE #Stores
(
StoreID VARCHAR(8)
)
INSERT INTO #Stores
SELECT 'North' UNION
SELECT 'South' UNION
SELECT 'East' UNION
SELECT 'West'
CREATE TABLE #StoreInventory
(
StoreID VARCHAR(8),
ProductID int
)
INSERT INTO #StoreInventory
--North
SELECT 'North', 1 UNION
SELECT 'North', 2 UNION
SELECT 'North', 3 UNION
--South
SELECT 'South', 1 UNION
SELECT 'South', 2 UNION
--East
SELECT 'East', 1 UNION
SELECT 'East', 2 UNION
SELECT 'East', 3 UNION
SELECT 'East', 4 UNION
--West
SELECT 'West', 2 UNION
SELECT 'West', 3 UNION
SELECT 'West', 4 UNION
SELECT 'West', 5 UNION
SELECT 'West', 6
DECLARE @ModelStore VARCHAR(8)
SET @ModelStore = 'North'
--SELECT * FROM #Stores
--SELECT * FROM #StoreInventory
SELECT *
FROM #StoreInventory
WHERE
StoreID <> @ModelStore
DROP TABLE #Stores
DROP TABLE #StoreInventory

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DouglasH SSCommitted Points: 1669 More actions · Answer 1

Shouldn't the desired results be as follows?

Purchase,South,3

Liquidate,East, 4

Purchase,West,1

Liquidate,West,4

Liquidate,West, 5

Liquidate,West, 6

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

DECLARE @ModelStore VARCHAR(8)

SET @ModelStore = 'North'

;with nonmodelStores as (select storeID from #Stores where StoreID<>@ModelStore),

nonmodelinv as (select storeID, productID from #StoreInventory where StoreID<>@ModelStore),

modelinv as (select storeID, productID from #StoreInventory where StoreID=@ModelStore),

IdealizedInv as (select nms.storeid,mi.productID from modelinv mi cross join nonmodelStores nms)

(

select storeid,productID, 'purchase' actionname from IdealizedInv

except

select storeid,productID, 'purchase' actionname from nonmodelinv)

union

(

select storeid,productID, 'liquidate' actionname from nonmodelinv

except

select storeid,productID, 'liquidate' actionname from IdealizedInv)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Chrissy321 SSCoach Points: 15763 More actions · Answer 3

Thanks Matt. I'm studying your solution.

Sorry about the incorrect desired results as Douglas H pointed out.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Sure - just fire on back if you have questions.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?