question

KenFowler avatar image
KenFowler asked

Stored Procedure to return a result table showing all parties and items in a multiparty swap

I have a need to interrogate two tables where table 1 contains items that users want to receive and table 2 contains items that users want to give up. For each user receive entry in table 1 there must be a corresponding give-up entry in table 2. So if User 1 whishes to receive Item-A he must give up some thing like Item-B. Now, if there is no user that wishes to give-up Item-A and Receive Item-B but there is a user that wishes to receive Item-B and give up Item-C and a third user that wishes to receive Item-C and and give Up Item-B then the trade would be a 3 way trade and could take place. If the chain of trades cannot be complete such that each party receives an item given up by another party and gives up an item the is received by another party then the trand cannot continue. The size of the round-robin trade is only limited by the nunmber of items in the tables. This seem to be an ideal case for a recursive CTE but I haven't figured it out. Any help is appreciated. Stumped. I have reviewed the recursive procedures in the article sugested by FatherJack and they are similar to the ones I have in my reference materials. The problem is that all of the examples refer to bound relationships where every parent is bound to its child and visa vera. The problem I am trying to solve involves unbound relationships where the only rule is that each participant in the chain must give up 1 item from his Give list and receive 1 item from his Receive list. So this is not a hierarchical relationship but rather chain that forms a ring. The Receive and Give table would look something like this CREATE PROCEDURE [dbo].[PR_Test_PoolLoad] AS If Object_ID('DBO.Receive_ITEM') is not null drop table DBO.Receive_ITEM; If Object_ID('DBO.GiveBack_ITEM') is not null drop table DBO.GiveBack_ITEM; Create Table DBO.Receive_ITEM ( Dealer_ID char(6) not null, ITEM char (10) not null ); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR001', 'ITEM010'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR001', 'ITEM011'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR002', 'ITEM021'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR002', 'ITEM022'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR003', 'ITEM020'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR003', 'ITEM031'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR004', 'ITEM030'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR004', 'ITEM031'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR005', 'ITEM030'); insert into DBO.Receive_ITEM(Dealer_id, ITEM) values ('DLR005', 'ITEM021'); Create Table DBO.Giveback_ITEM ( Dealer_ID char(6) not null, ITEM char (10) not null ); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR001', 'ITEM021'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR001', 'ITEM030'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR002', 'ITEM010'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR002', 'ITEM031'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR003', 'ITEM021'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR003', 'ITEM010'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR004', 'ITEM021'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR004', 'ITEM011'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR005', 'ITEM031'); insert into DBO.GiveBack_ITEM(Dealer_id, ITEM) values ('DLR005', 'ITEM032); select * from Receive_ITEM Select * from GiveBack_ITEM The output put should be a list of all trades possible between 2 or more parties. The Result Set (or Temp table) should contain all of the possible trades (without the comments) REC-Dealer Item Give Dealer Comment DLR001 ITEM010 DLR002 2 party trade DLR002 ITEM021 DLR001 2 party trade DLR001 ITEM011 DLR004 3 party trade DLR005 ITEM030 DLR001 3 party trade DLR004 ITEM031 DLR005 3 party trade Here is my query that accomplishes the task for a two way trade (Not very complex compared to a 3, 4, 5 or more round robin swap) -- Description: A called proceure to return the first dealer that is able to perform -- a direct swap with the input dealer for the input ITEM. -- Test: EXEC PR_Test_PoolLookup -- ====================================================================================== ALTER PROCEDURE [dbo].[PR_Test_PoolLookup] -- @RecvDealer_ID char(10), -- @RecvITEM char(10) AS declare @GivebackDealer char(10) declare @GivebackITEM char(10) declare @Dealer_ID char(10) declare @RecvDealer_ID char(10) declare @RecvITEM char(10) Declare @Receive_CSR cursor set @Receive_CSR = cursor for Select Dealer_ID ,ITEM from Receive_ITEM Open @Receive_CSR Fetch Next From @Receive_CSR into @RecvDealer_ID, @RecvITEM While @@FETCH_STATUS = 0 BEGIN set @GivebackDealer = (Select top 1 GBT.Dealer_ID From Giveback_ITEM GBT Where GBT.ITEM = @RecvITEM And EXISTS (Select 1 from Receive_ITEM RT Where RT.Dealer_ID = GBT.Dealer_id And RT.ITEM in (Select ITEM From GiveBack_ITEM GBT2 Where GBT2.Dealer_ID = @RecvDealer_ID))) set @GivebackITEM = (Select top 1 RCV.ITEM From Receive_ITEM RCV Where RCV.Dealer_ID = @GivebackDealer And EXISTS (Select 1 from Giveback_ITEM GB Where GB.Dealer_ID = @RecvDealer_ID And GB.ITEM in (Select ITEM From Receive_ITEM RCV2 Where RCV2.Dealer_ID = @GivebackDealer))) set @GivebackDealer = isnull(@GivebackDealer,'NONE') if @GivebackDealer <> 'NONE' BEGIN -- These selects would be replaced by inserts into a solution table select @RecvDealer_ID as RecDlr ,@RecvITEM as RecITEM ,@GivebackDealer as GiveDlr select @GivebackDealer as RecDlr ,@GivebackITEM as RecITEM ,@RecvDealer_ID as GiveDlr END -- Delete the Give Table Entry so it cannot be used again delete from GiveBack_ITEM where Dealer_ID = @GivebackDealer and ITEM = @RecvITEM Fetch Next From @Receive_CSR into @RecvDealer_ID, @RecvITEM END CLOSE @Receive_CSR SELECT * FROM Receive_ITEM SELECT * FROM Giveback_ITEM
sql-server-2008t-sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@KenFowler - can you give us a headstart and supply us with table definitions, example data and example output? What query have you got so far that is not quite working?
2 Likes 2 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered
I have read this question a couple of times and have intended to write up a sample and build a solution but havent had the chance and I cant see I will get one any time soon so here is a link to what I would have started off with. Its a link to MSDN Books OnLine [ http://msdn.microsoft.com/en-us/library/ms175972.aspx][1]. It works on one table so you might have to re-jig your data or build a view over it to present it in the right way but I think this could be the start of the solution you are looking for. The example J is the most likely option but I am not sure whether it will directly transfer. [1]: http://msdn.microsoft.com/en-us/library/ms175972.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.