Best Fit Algorithm/Function

• gabrielr

  SSC Enthusiast

  Points: 135

  More actions

  January 10, 2011 at 2:31 pm

  #97629

  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

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  January 10, 2011 at 2:56 pm

  #1271631

  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

• Jeff Moden

  SSC Guru

  Points: 1004593

  More actions

  January 10, 2011 at 6:18 pm

  #1271683

  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)