Allocate Stock by Part/Customer

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 2005 Forums

Transact-SQL (2005)

Allocate Stock by Part/Customer

Author

Topic

ct.craig
Starting Member

1 Post

Posted - 2008-09-04 : 03:06:53

Hi, i've run into a problem that I am going round in circles trying to resolve.

I'm looking to create an SQL script that will run through data on a table that will allocate stock of a part to a customers sales order and give a stock balance before moving on to the next row.

My starting data looks something like this:

PART CUSTOMER SALES STOCK ALLOCATED STOCK BALANCE
Part A Customer 1 15 100 0 0
Part A Customer 2 80 100 0 0
Part A Customer 3 50 100 0 0
Part A Customer 4 75 100 0 0
Part B Customer 2 25 50 0 0
Part B Customer 4 75 50 0 0
Part C Customer 1 100 75 0 0
Part C Customer 3 50 75 0 0

and i'm looking to end with data that looks like this:

PART CUSTOMER SALES STOCK ALLOCATED STOCK BALANCE
Part A Customer 1 15 100 15 85
Part A Customer 2 80 100 80 5
Part A Customer 3 50 100 5 -45
Part A Customer 4 75 100 0 -120
Part B Customer 2 25 50 25 25
Part B Customer 4 75 50 25 -50
Part C Customer 1 100 75 75 -25
Part C Customer 3 50 75 0 -75

Any help or suggestions would be very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 03:27:47

put your initial query results onto a temporary table with an identity and then do update like below:-

UPDATE t1
SET t1.ALLOCATED=CASE WHEN COALESCE(t2.BalSum,t1.STOCK)>=t1.SALES THEN t1.SALES ELSE 
CASE WHEN COALESCE(t2.BalSum,t1.STOCK)>0 THEN COALESCE(t2.BalSum,t1.STOCK) ELSE 0 END 
END,
t1.BALANCE=COALESCE(t2.BalSum,t1.STOCK)-t1.[SALES]
FROM #Temp t1
CROSS APPLY(SELECT TOP 1 BALANCE AS BalSum
            FROM #Temp
            WHERE Part=t1.Part
            AND ID <t1.ID
            ORDER BY ID DESC)t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources