Matching when numbers don't match

[tshad]

I am trying to find a way to match two sets of numbers by adding one set of number together to get the other set.

For example:

If OBJECT_ID('tempdb..#Stage') IS NOT NULL
	DROP TABLE #Stage


If OBJECT_ID('tempdb..#Ship') IS NOT NULL
	DROP TABLE #Ship


CREATE TABLE #Stage
(
	StageID int,
	ShipNumber varchar(10),
	PONumber varchar(10),
	iQty int
)

CREATE TABLE #Ship
(
	ShipID int,
	ShipmentNumber varchar(10),
	Units int
)

INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (20, 'MZ12345', 'A4735', 32)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (22, 'MZ12345', 'A4735', 46)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (23, 'MZ12345', 'A4735', 60)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (35, 'MZ12345', 'A4735', 18)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (36, 'MZ12345', 'A4735', 26)

Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (250, 'MZ12345', 12)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (251, 'MZ12345', 20)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (252, 'MZ12345', 10)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (253, 'MZ12345', 20)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (255, 'MZ12345', 16)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (300, 'MZ12345', 30)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (310, 'MZ12345', 30)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (320, 'MZ12345', 6)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (330, 'MZ12345', 12)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (340, 'MZ12345', 16)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (350, 'MZ12345', 5)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (360, 'MZ12345', 5)

In the above example, the numbers in #Stage are also in #Ship. I have them in order here but they don't have to be. 12 + 20 = 32, 10 + 20 + 16 = 46 etc.

I want to be able to come up with the ShipID's that when added together go with each #Stage row.

Not sure if this is possible but thought I would ask.

Thanks,

Tom

 

[gmmastros]

This problem can be quite complex. I think you are looking for an "Order Fulfillment Algorithm". Because of the complexity of this algorithm, you are probably better off handling this in a front end application.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Besides what George already said it seems you misunderstood adding together IDs.

If you have a number sequence (int identity) column as an ID, eg ShipID, then adding IDs by summing instead of concatenating yields a value that can also resul from very very many other ID combinations, so the final match is quite random. So are you sure you haven't reached an impasse?

Looking one more, what you add in your sample description 12 + 20 = 32, 10 + 20 + 16 = 46 are the Units and you want to compare them against iQty. That makes much more sense, but how do you get at some iQty? I assume there is a previous step in your application where you bundle a few Ship records to a Stage record, then you have the information which records are the source of the iQry. Otherwise what puzzles me is how you determine the iQty values beforehand, if not even sure, if you can bundle the Ship records in a way resulting in the sums.

In the end, this is not a task for the database, you can use the more powerful languages of frontend applications to combine sets/collections in a way to get your order fulfillment, that's really the topic here, isn't it?

Bye, Olaf.

 

[tshad]

You're right.

I didn't mean ShipID's, I meant comparing the Units with the Qtys.

Actually, this being done as an upload where I am comparing Units in the upload with Qtys in the tables and matching them up. They normally do match. Sometimes one side or the other sums the number (all the numbers) and that is taken care of as well. But when the summing is done to give 2 or more totals. I can't tell which goes with which.

Thanks,

Tom

 

[Olaf Doschke]

Well, the problem is still not quite clear to me, then. And I see no way solving that with a single query, even if the normal case is the sums result in the sum quantities.

Knowing a goal sum (iQty) and a set single summands (unit) your problem compares to the 3-SUM problem of finding 3 elements of a set summing to 0. That has a complexity of n^2. Instead of the 0 result you seek another certain value, which doesn'T change much of the complexity. Then you don't necessarioly need three elements, you rather have the N-SUM problem.

SQL is the wrong langauge to solve this even though SQL is working on sets.

Bye, Olaf.

 

[tshad]

That's OK.

I assumed there wouldn't be an easy way to get SQL to do what I want to do.

Thanks,

Tom