# Thread: Is there a set based solution?

1. Registered User
Join Date
Aug 2003
Posts
4

## Unanswered: 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
Code:
```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.

Code:
```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.

Last edited by ranaomypud; 08-25-03 at 17:46.

2. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
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.

3. Registered User
Join Date
Oct 2001
Location
England
Posts
426
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

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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

5. Registered User
Join Date
Aug 2003
Posts
4

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.

6. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
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?

7. Registered User
Join Date
Oct 2001
Location
England
Posts
426
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.

8. Registered User
Join Date
Aug 2003
Posts
4
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.

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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

10. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
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!

11. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
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!

12. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
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
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 - a word for the day for you.

13. Registered User
Join Date
Aug 2003
Posts
4
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•