Quick(er) Allocation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Quick(er) Allocation

Author

Topic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-15 : 16:30:35

I inherited a process that needs to do some allocation. It is easy to do in a looping fashion, but I was wondering if anyone has an slicker ideas that's perform better.

The basic idea is that a product has a delta - Some amount that can be consumed (positive) or some amount that needs to be allocated (negative). The amount to be consumed can only consume from a product with a higher/larger version starting with the lowest available. If there are enough deltas to consume, it'll take all it can. If it runs out, it moves up to the next highest.

Given the sample data below:
1. For a given Family (Foo) ,Version 1999 has 3 deltas (-3) that it needs to consume.
2. Go to the next highest Version (2003) and consume 3 of those. (1999 is now satisfied)
3. 2000 needs to consume 5 deltas. We go to 2003, but there is only 1 left. So, consume it and move to 2010. There are now 4 deltas left to consume so "take" 4 of 2010 10 deltas.
4 ..and so on.

Some assumptions:
1. No, you can't rely on the order of the ProductID.
2. Yes, there are multiple Families (I left that out of my sample)
3. I don't care if the "source" table is updated or not. Meaning hte process can be destructive.

I'm kind of in the forest so I might not be able to see the trees, so feel free to ask questions if something isn't clear.

Here is some sample data and expected results:

DECLARE @Table TABLE
(
    ProductID INT NOT NULL -- Pk
    ,Family VARCHAR(20) NOT NULL
    ,Version INT NOT NULL
    ,Delta INT NOT NULL
)


INSERT @Table (ProductID, Family, Version, Delta) VALUES
(1, 'Foo', 2010, 10),
(2, 'Foo', 2007, -2),
(3, 'Foo', 2003, 4),
(4, 'Foo', 2000, -5),
(5, 'Foo', 1999, -3)



-- Desired Results
DECLARE @Result TABLE
(
    ProductID INT NOT NULL
    ,ConsumedProductID INT NOT NULL
    ,ConsumedDelta INT NOT NULL
)

INSERT @Result (ProductID, ConsumedProductID, ConsumedDelta) VALUES
(5, 3, 3),
(4, 3, 1),
(4, 1, 4),
(2, 1, 2)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-28 : 21:15:04

other than doing it in C++...would this help at all?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124410

If you don't have the passion to help people, you have no passion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-29 : 00:50:43

Will this satisfy your needs?

SELECT		i.Family,
		MIN(i.ProductID) AS ProductID,
		MIN(o.ProductID) AS ConsumedProductID,
		COUNT(*) AS ConsumedDelta
FROM		(
			SELECT		t.Family,
					t.ProductID,
					t.Delta,
					ROW_NUMBER() OVER (PARTITION BY t.Family ORDER BY t.Version, v.Number) AS Yak,
					t.Version
			FROM		@Table AS t
			INNER JOIN	master..spt_values AS v ON v.type = 'P'
						AND v.number BETWEEN 1 AND ABS(t.Delta)
			WHERE		t.Delta < 0
		) AS i
INNER JOIN	(
			SELECT		t.Family,
					t.ProductID,
					t.Delta,
					ROW_NUMBER() OVER (PARTITION BY t.Family ORDER BY t.Version, v.Number) AS Yak,
					t.Version
			FROM		@Table AS t
			INNER JOIN	master..spt_values AS v ON v.type = 'P'
						AND v.number BETWEEN 1 AND t.Delta
			WHERE		t.Delta > 0
		) AS o ON o.Family = i.Family
			AND o.Yak = i.Yak
GROUP BY	i.Family,
		i.Version,
		i.Delta,
		o.Version,
		o.Delta
ORDER BY	i.Family,
		i.Version,
		o.Version

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources