Results 1 to 13 of 13
  1. #1
    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. #2
    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. #3
    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. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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. #5
    Join Date
    Aug 2003
    Posts
    4

    more info

    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. #6
    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. #7
    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. #8
    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. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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. #10
    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!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    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. #12
    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. #13
    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
  •