Best Fit Algorithm/Function

  • I'm searching for some code or a function that can help me with the following problem.

    I have a set of records (multiple tags of part A with different quantities). For example:

    tagkey quantity

    ----------- -----------

    31884118 3

    31889428 4

    31889067 5

    31889154 5

    31889173 5

    31889209 7

    31888419 9

    31870754 10

    31889073 11

    31878409 14

    31872736 24

    31872758 24

    31870748 28

    31870752 28

    31887433 28

    31887438 28

    31887439 28

    31887440 28

    31887441 28

    31888417 28

    31888418 28

    31889069 28

    31889070 28

    31889071 28

    31889072 28

    31889158 28

    31889169 28

    31889427 28

    31870201 29

    31872499 150

    I recieve a particular order for part A for 300 pieces. I'm trying to develop a function (or algorithm for a procedure) that will select the tags above that add up to 300.

    Can someone help? Has anyone built something similar?

    Many thanks,

    Gabe

  • There's a number of solutions out there, but the problem is you have to know what you're looking for.

    For example, the First-In First-Out logic (FIFO)

    Google: SQL FIFO Inventory

    This is from a recent speed phreak challenge:

    http://ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem

    or if you were trying to remove the smallest, here's an article I found that did it from VB that you can probably adapt the above date ordering into a volume order instead:

    http://bytes.com/topic/access/answers/864842-hopefully-sql-can-handle-processing-alternative-part-numbers

    There's also Last In First Out, working on the theory that the most recently dropped into the warehouse is closest to the front, and thus easier to get (amongst other things).

    So, there are a number of ways to approach it. Take a look through those articles and hit us back up if you've got further questions and can show your code. Check out the first link in my sig for help with posting DDL/sample data/code properly if you need it. The stuff above, while visually helpful, is not consumable and thus nearly useless.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Gabe,

    I agree with Craig... which tag keys do you want to use up first? Also, what do you want to do if you don't have an exact match for 300 and need to use a partial amount from one of the tag keys?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply