Is there a set based solution?

basically I am trying to figure out if there is a set based solution for the following situation.

When we import mainframe data to the sql server the table will look like this

PartNumber release demandQty BalOnHandQty ShortQty
A 1 10 23 0
A 2 5 23 0
A 3 13 23 0
A 4 2 23 0
A 5 12 23 0
B 1 11 14 0
B 2 7 14 0
B 3 20 14 0
B 4 6 14 0



After we run the query the table should look like ie amotize the balonhandqty and update the shortqty.

PartNumber release demandQty BalOnHandQty ShortQty
A 1 10 13 0
A 2 5 8 0
A 3 13 -5 5
A 4 2 -7 2
A 5 12 -19 12
B 1 11 3 0
B 2 7 -4 4
B 3 20 -24 20
B 4 6 -30 6

The only way I can up with a solution is by using a cursor which takes a long time. I was wondering if there is a set based or any other quicker solution.Whoever comes up with the answer deserves to be called a Guru...or a Grand Poobah :)

But out of curiosity, can you give us the formula? If nothing else you'll get a kick out of looking at how people can attempt to resolve this.|||update tbl
set BalOnHandQty = BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) ,
ShortQty = case when BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) < 0 then
BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release) * -1 else 0 end
from tbl t1

maybe better is

update tbl
set BalOnHandQty = a.BalOnHandQty ,
ShortQty = case when a.BalOnHandQty < 0 then a.BalOnHandQty * -1 else 0 end
from
tbl t1
join
(select PartNumber, Release, BalOnHandQty = BalOnHandQty - (select sum(demandQty) from tbl t2 where t1.PartNumber = t2.PartNumber and t2.Release <= t1.Release)
from tbl t1) a
on a.PartNumber = t1.PartNumber
and a.Release = t1.Release|||Heck, I don't know that solving this problem is such a complicated task, but I give Kudos to Nigelrivett if he was able to figure out what the problem was based on the sparse information provided! Now THAT takes genious.

There is definitely a career for you in helpdesk support if you ever want it, Nigelrivett! ;)

blindman|||Originally posted by rdjabarov
Whoever comes up with the answer deserves to be called a Guru...or a Grand Poobah :)

But out of curiosity, can you give us the formula? If nothing else you'll get a kick out of looking at how people can attempt to resolve this.

Initially all the records for each partnumber will have the same BalOnHandQty. Then we get start subtracting demandQty from BalOnHandQty for each release for every partnumber. When the BalOnHandQty goes negative we update the shortQty.

I hope everybody can understand the problem!

I'am sorry I should have given more information when I first posted this problem.|||Heck, I don't know that solving this problem is such a complicated task
I didn't see you taking a shot at it!

nigelrivett, - that was quick! I just don't get it why the blind guy thinks you're good only for helpdesk support?|||Bit concerned about the design though.
This updates every balance so that it cannot be run twice.
It would probably be better if the rec with the lowest release (or maybe a new release 0 with the balance and demand qty 0) held the beginning balance and everything worked off that. Then you could run the update multiple times and get the same result.|||nigelrivett thanks for your replies. Iam not that good at set based stuff I am still trying to understand your queries. Once I get back to work I will try to run these queries on some test data and may then I can understand it better.|||rjabberon,

niggelrivett's solution is very simple and concise. That's what makes it good. "Inside every large program there is a small program screaming to get out." From what I've seen of your solutions, you'd do well to take heed.

From what I could see, the challenge in this issue lay in uncovering the requirements. That's pretty common in many I.T. projects, as you'll find out.

And by the way, the word for the day is "sarcasm". It's a light-hearted form of humor. Look it up: www.dictionary.com

blindman|||Originally posted by blindman
rjabberon,


Obviously is recalling a nick name more difficult than understanding the problem. Or was it sarcasm? Anyway, I bed you came up with the solution if niggelrivett (bravo!) wasn't quicker than you!|||Thanks for support, DoktorBlue! The blind guy is obviously not only blind, but also a stuck-up arrogant AH (I looked all this up following your link, bm). And I also like this phrase that I found there more and more (in fact, with every post of yours that is intended to insult me), - BLOW ME!|||Originally posted by blindman
rjabberon,

...From what I could see, the challenge in this issue lay in uncovering the requirements. That's pretty common in many I.T. projects, as you'll find out.
blindman

How many of those "I.T." projects have you participated, and how many have you actually pulled through yourself, and how many of those did you manage not to bring to a complete total disaster?
Originally posted by blindman
And by the way, the word for the day is "sarcasm". It's a light-hearted form of humor. Look it up: www.dictionary.com

blindman

"sarcasm" is not a "light-hearted form of humor". Check your own link...but since you're too busy fixing your "brilliant" solutions, I'll give you the right definition here:

www.dictionary.com (http://dictionary.reference.com/search?q=sarcasm)
A cutting, often ironic remark intended to wound.
A form of wit that is marked by the use of sarcastic language and is intended to make its victim the butt of contempt or ridicule.

Hypothetical (http://dictionary.reference.com/search?q=Hypothetical) - a word for the day for you.|||Originally posted by nigelrivett
Bit concerned about the design though.
This updates every balance so that it cannot be run twice.
It would probably be better if the rec with the lowest release (or maybe a new release 0 with the balance and demand qty 0) held the beginning balance and everything worked off that. Then you could run the update multiple times and get the same result.

Thanks a lot nigelrivett!

Your update query works great and is much quicker than my cursor based solution.