January 10, 2011 at 2:31 pm
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
January 10, 2011 at 2:56 pm
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:
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.
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
January 10, 2011 at 6:18 pm
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy