Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Store inventory/Liquidations and purchases Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2014 5:09 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:01 AM
Points: 656, Visits: 3,960
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
Post #1564477
Posted Wednesday, April 23, 2014 7:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 63, Visits: 365
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



Post #1564486
Posted Wednesday, April 23, 2014 8:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,179, Visits: 15,777
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?
Post #1564495
Posted Thursday, April 24, 2014 8:22 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:01 AM
Points: 656, Visits: 3,960
Thanks Matt. I'm studying your solution.

Sorry about the incorrect desired results as Douglas H pointed out.
Post #1564669
Posted Friday, April 25, 2014 8:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,179, Visits: 15,777
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?
Post #1565273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse