Substracting a list of items from a stock table

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

General SQL Server Forums

New to SQL Server Programming

Substracting a list of items from a stock table

Author

Topic

sbayeta
Starting Member

3 Posts

Posted - 2010-05-21 : 18:41:34

Hi,

I'd like to know if is there's a way to achieve the following using SQL.

Suppose I have one table containing recipes like this:

Recipe Ingredient Quantity
rec_A apple 2
rec_A banana 5
rec_A peach 1
rec_B banana 2
rec_B plum 4

And I have another table containing the stock of ingredients:

Ingredient Stock
apple 30
banana 24
peach 32
plum 12

I'd like to perform two different operations:

1. Check the maximum number of portions of one recipe I can make with the stock on hand
1. Substract n times one recipe from the stock

I know I can do this by code in my app (VB.NET application for stock control in PCB's manufacturing), but the 'recipes' (Bill Of Materials) contain thousands of items and I will be substracting every few seconds, so I'm guessing performance will be better if the DB engine does the hard work.

Thanks in advance for any help on this.

Best regards,
Santiago

josephj1989
Starting Member

7 Posts

Posted - 2010-05-22 : 17:22:45

declare @recipe varchar(10);
set @recipe='recb';
declare @units int;
set @units=
(select
min(a.stock/b.quantity) units
from
stock a
inner join ing b
on a.ingredient=b.ingredient
where b.recipe=@recipe);
select 'Number of Units = '+ cast(@units as varchar(10))

update stock
set stock.stock =(
select stock.stock-b.quantity*@units
from ing b
where stock.ingredient=b.ingredient
and b.recipe=@recipe)
where stock.ingredient in(
select ingredient from ing b where b.recipe=@recipe)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources