Need to spread quantities across requirements

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)

Need to spread quantities across requirements

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2011-12-21 : 09:39:53

I've been given a task here at year end to take lot quantities and spread them (allocate them?) across requirements. I think I can loop through it but I'm hoping somone can show me a better way.

Here's some background -
There are multiple kits.
Each kit requires multiple components.
Each component may have multiple lots.
Some lot quantities may be more than required by one kit, some less.

Please see if this makes any sense. I greatly appreciate any suggestions.

Thanks,

Kevin

DECLARE @Kits TABLE
(
KitNumber INT IDENTITY(1,1)
,KitStockCode CHAR(30)
,Component INT
,ComponentQtyReqd DECIMAL(10,3)
,ComponentQtyAllocated DECIMAL(10,3)
,Complete CHAR(1)
)

DECLARE @Lots TABLE
(
LotNumber INT IDENTITY(1,1)
,Component INT
,LotQty DECIMAL(10,3)
,LotQtyAllocated DECIMAL(10,3)
)

INSERT INTO @Kits
(KitStockCode, Component, ComponentQtyReqd, ComponentQtyAllocated, Complete)
VALUES
('A', 1, 100, 0, 'N')

INSERT INTO @Kits
(KitStockCode, Component, ComponentQtyReqd, ComponentQtyAllocated, Complete)
VALUES
('B', 1, 100, 0, 'N')

INSERT INTO @Kits
(KitStockCode, Component, ComponentQtyReqd, ComponentQtyAllocated, Complete)
VALUES
('C', 1, 100, 0, 'N')

SELECT * FROM @Kits

INSERT INTO @Lots
(Component, LotQty, LotQtyAllocated)
VALUES
(1, 150, 0)

INSERT INTO @Lots
(Component, LotQty, LotQtyAllocated)
VALUES
(1, 75, 0)

SELECT * FROM @Lots

--Need to produce something like this
UPDATE @Kits
SET Complete = 'Y'
,ComponentQtyAllocated = ComponentQtyReqd
WHERE KitNumber IN (1, 2)
AND Component = 1

UPDATE @Kits
SET ComponentQtyAllocated = 25
WHERE KitNumber IN (3)
AND Component = 1

SELECT * FROM @Kits

--And put this in a table
--KitNumber KitStockCode Component Lot LotQtyAllocated
--1 A 1 1 100
--2 B 1 1 50
--2 B 1 2 50
--3 C 1 2 25

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:13:10

sorry didnt understand what you're asking for
is it a update procedure that you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2011-12-21 : 12:44:38

The main thing I'm trying to get is a table which contains the following -

--And put this in a table
--KitNumber KitStockCode Component Lot LotQtyAllocated
--1 A 1 1 100
--2 B 1 1 50
--2 B 1 2 50
--3 C 1 2 25

In other words, I've got to record that qty of 100 from lot 1 will go to kit 1 and the remaining qty 50 will go to kit 2. Qty 50 of lot 2 will go to kit 2 and the remaining qty 25 will go to kit 3.

I just thought the updates to Complete and ComponentQtyAllocated in the Kits table would help keep track of what kits and components are still requiring qty to be allocated.

Does that make it clearer?

Kevin

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2011-12-21 : 12:58:45

I also included a column named LotQtyAllocated in the @Lots table thinking it would also be helpful to keep track of what lots have been completely allocated to kits. I just didn't show an update to that column in my example.

There may be a better way to do this. I just thought I would see if it's possible to do it without looping through everything.

Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources