By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Subtracting sequentially from multiple sources

P: 3
i have a simplified table with "BoxID, Item, Quanity" and I want to subtract the Quanity of the ProductID from the BoxID sequentially. i.e. the lowest BoxID first. My current code is as follows:

update boxes
set quanity = (quanity - itemshipped.qtyshipped )
from itemshipped
where (item = boxes.itemno) and seqid in (select min(seqid) from boxes where (quanity > 0)) and (pickseq = boxes.location) and (boxes.quanity > 0) and not exists(select shippedid from itemshippedcomplete where (shippedid = itemshipped.shippedid))

The problem that I am having is that if I have a quanity sold greater than what is left in the first box then I am left with a negative quanity. What I need it to do is subtract until quanity is 0 then subtract the remaining quanity from the next box quanity.

So if sold a quanity of 4 of 'item A' then my table should go from:

box 1, item A, qty 2
box 2, item A, qty 10


box 1, item A, qty 0
box 2, item A, qty 8

instead of:

box1, item A, qty -2
box2, item A, qty 10
Feb 23 '09 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 2,878
I was trying to think of a single (or group of) T-SQL that can handle your requirement. However, the challenge is if this is in a multi-user environment, that would mean your item could be coming from non-sequential boxes. In this case, your only choice could be to handle it using a loop from your front-end and keep finding boxes with available item.

-- CK
Feb 23 '09 #2

P: 3
The quanity sold is coming from a trigger in another table. This part of it is essentially single user. The boxes get reduced when the items are shipped.
Feb 23 '09 #3

Expert 2.5K+
P: 2,878
Loop might be your only choice.

-- CK
Feb 24 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.