Sliding Scale Solution
-
This solution to a real world problem just seems too easy to be foolproof, so I thought I would see if anyone can show a problem with it. Say I have the same item in two sizes, one cost $2.00, and the other $2.25. The summary tables used for reporting have only fields for the total quantity sold: 137, and the total cost: $286.50. The customer would like a monthly report/export showing quantity by price. The first approach was pulling the inforation directly from the transaction tables which contain millions of records. Though this approach provided the seperation, the performance hit made me search for a better solution. It made sense to me that only one correct combination of (x * 2.00) + (y * 2.25) could equal 286.50. So, how to get the correct combination? Examples in vbscript for simplicity/proof of concept.
totalQuantity = 137
wantedResult = 286.5
premiumPrice = 2.25
standardPrice = 2.00
'x is always the higher priced quantity
for x = totalQuantity to 0 step -1
y = totalQuantity - x
if (x * premiumPrice) + (y * standardPrice) = wantedResult then exit for
next
'x and y should now be the correct valuesThis approach, even it the raw form, is much, much quicker than querying the transaction tables. The following revision finds the correct answer in 6 iterations. When I tested it for an extreme case of 1 million total (999,999 of standard price and 1 premium) it found the answer in 25 guesses.
totalQuantity = 137
wantedResult = 286.5
premiumPrice = 2.25
standardPrice = 2.00
success = 1
'recursive halving for speed
currentSplit = Int(totalQuantity / 2)
a = currentSplit
b = totalQuantity - a
counter = 0
currentresult = (a * premiumPrice) + (b * standardPrice)
counter = counter + 1
Do While currentresult <> wantedResult
if currentSplit > 1 then
currentSplit = Int(currentSplit / 2)
end if
if counter > totalQuantity then
'safety net
msgbox "result not found"
success = -1
exit do
end if
If currentresult < wantedResult Then
a = a + currentSplit
Else
a = a - currentSplit
End If
counter = counter + 1
b = totalQuantity - a
currentresult = (a * premiumPrice) + (b * standardPrice)
Loop
if success = 1 then
MsgBox a & " at " & premiumPrice & " and " & b & " at " & standardPrice & " in " & counter & " guesses"
end ifThe point is, this function may keep me (or the customer) from having to ask the database vendor for a change to their s
-
This solution to a real world problem just seems too easy to be foolproof, so I thought I would see if anyone can show a problem with it. Say I have the same item in two sizes, one cost $2.00, and the other $2.25. The summary tables used for reporting have only fields for the total quantity sold: 137, and the total cost: $286.50. The customer would like a monthly report/export showing quantity by price. The first approach was pulling the inforation directly from the transaction tables which contain millions of records. Though this approach provided the seperation, the performance hit made me search for a better solution. It made sense to me that only one correct combination of (x * 2.00) + (y * 2.25) could equal 286.50. So, how to get the correct combination? Examples in vbscript for simplicity/proof of concept.
totalQuantity = 137
wantedResult = 286.5
premiumPrice = 2.25
standardPrice = 2.00
'x is always the higher priced quantity
for x = totalQuantity to 0 step -1
y = totalQuantity - x
if (x * premiumPrice) + (y * standardPrice) = wantedResult then exit for
next
'x and y should now be the correct valuesThis approach, even it the raw form, is much, much quicker than querying the transaction tables. The following revision finds the correct answer in 6 iterations. When I tested it for an extreme case of 1 million total (999,999 of standard price and 1 premium) it found the answer in 25 guesses.
totalQuantity = 137
wantedResult = 286.5
premiumPrice = 2.25
standardPrice = 2.00
success = 1
'recursive halving for speed
currentSplit = Int(totalQuantity / 2)
a = currentSplit
b = totalQuantity - a
counter = 0
currentresult = (a * premiumPrice) + (b * standardPrice)
counter = counter + 1
Do While currentresult <> wantedResult
if currentSplit > 1 then
currentSplit = Int(currentSplit / 2)
end if
if counter > totalQuantity then
'safety net
msgbox "result not found"
success = -1
exit do
end if
If currentresult < wantedResult Then
a = a + currentSplit
Else
a = a - currentSplit
End If
counter = counter + 1
b = totalQuantity - a
currentresult = (a * premiumPrice) + (b * standardPrice)
Loop
if success = 1 then
MsgBox a & " at " & premiumPrice & " and " & b & " at " & standardPrice & " in " & counter & " guesses"
end ifThe point is, this function may keep me (or the customer) from having to ask the database vendor for a change to their s
Why not just use a system of linear equations?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
Why not just use a system of linear equations?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
The algebra is a bit rusty...I gave up on an elegant one equation solution, and found it easier to go for a brute force attack. Thanks to your suggestion, I tried to form the equation again, realizing that it should be formed with just one variable instead of two and ended up with: 2a + 2.25(137 - a) = 286.5 resulting in: 2a + (308.25 - 2.25a) = 286.5 resulting in: 2a - 2.25a = -21.75 resulting in: -.25a = -21.75 resulting in: a = 87 which is correct! Now, getting this into code:
totalQuantity = 137
totalCost = 286.50
a = 0' number of items at the standard price
b = 0' number of items at the premium price or (totalQuantity - a)
standardPrice = 2.00
premiumPrice = 2.25a = (totalCost - (premiumPrice * totalQuantity)) / (standardPrice - premiumPrice)
b = totalQuantity - a
'correctly computes to a = 87 and b = 50It's a bit alarming how quickly I had given up on getting the solution correctly in favor of something way too complicated!...and looking at it now, I realize this is probably a 5th grade math problem! (hanging head)
"Go forth into the source" - Neal Morse
-
The algebra is a bit rusty...I gave up on an elegant one equation solution, and found it easier to go for a brute force attack. Thanks to your suggestion, I tried to form the equation again, realizing that it should be formed with just one variable instead of two and ended up with: 2a + 2.25(137 - a) = 286.5 resulting in: 2a + (308.25 - 2.25a) = 286.5 resulting in: 2a - 2.25a = -21.75 resulting in: -.25a = -21.75 resulting in: a = 87 which is correct! Now, getting this into code:
totalQuantity = 137
totalCost = 286.50
a = 0' number of items at the standard price
b = 0' number of items at the premium price or (totalQuantity - a)
standardPrice = 2.00
premiumPrice = 2.25a = (totalCost - (premiumPrice * totalQuantity)) / (standardPrice - premiumPrice)
b = totalQuantity - a
'correctly computes to a = 87 and b = 50It's a bit alarming how quickly I had given up on getting the solution correctly in favor of something way too complicated!...and looking at it now, I realize this is probably a 5th grade math problem! (hanging head)
"Go forth into the source" - Neal Morse
Excellent! That is the same answer I found as well.
kmoorevs wrote:
I realize this is probably a 5th grade math problem!
In this day and age, in the fifth grade they do begin to introduce this type of problem. My kids were faced with the same type of problem just before junior high school. At the pace they are trying to teach kids math now, it is not of surprise seeing high school sophomores or juniors in calculus courses.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
Excellent! That is the same answer I found as well.
kmoorevs wrote:
I realize this is probably a 5th grade math problem!
In this day and age, in the fifth grade they do begin to introduce this type of problem. My kids were faced with the same type of problem just before junior high school. At the pace they are trying to teach kids math now, it is not of surprise seeing high school sophomores or juniors in calculus courses.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
Paul Conrad wrote:
At the pace they are trying to teach kids math now, it is not of surprise seeing high school sophomores or juniors in calculus courses.
It's just too bad they don't make the kids learn what they're teaching. Looking at why my younger siblings are learning, the schools are making kids more reliant on calculators and computers and less capable of doing the problems by hand.
-
Paul Conrad wrote:
At the pace they are trying to teach kids math now, it is not of surprise seeing high school sophomores or juniors in calculus courses.
It's just too bad they don't make the kids learn what they're teaching. Looking at why my younger siblings are learning, the schools are making kids more reliant on calculators and computers and less capable of doing the problems by hand.
Bert Mitton wrote:
the schools are making kids more reliant on calculators and computers and less capable of doing the problems by hand
That is sad. When I did teach high school math a while back, I tried to get the students to be less reliant on calculators, and it did work with a lot of the kids to regain confidence that they could solve the problems without a calculator. In college, my Physics professor did not allow calculators at all. He wanted to see how we derived our solution, and could have cared less about punching a bunch of buttons on a calculator to come up with an answer.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
This solution to a real world problem just seems too easy to be foolproof, so I thought I would see if anyone can show a problem with it. Say I have the same item in two sizes, one cost $2.00, and the other $2.25. The summary tables used for reporting have only fields for the total quantity sold: 137, and the total cost: $286.50. The customer would like a monthly report/export showing quantity by price. The first approach was pulling the inforation directly from the transaction tables which contain millions of records. Though this approach provided the seperation, the performance hit made me search for a better solution. It made sense to me that only one correct combination of (x * 2.00) + (y * 2.25) could equal 286.50. So, how to get the correct combination? Examples in vbscript for simplicity/proof of concept.
totalQuantity = 137
wantedResult = 286.5
premiumPrice = 2.25
standardPrice = 2.00
'x is always the higher priced quantity
for x = totalQuantity to 0 step -1
y = totalQuantity - x
if (x * premiumPrice) + (y * standardPrice) = wantedResult then exit for
next
'x and y should now be the correct valuesThis approach, even it the raw form, is much, much quicker than querying the transaction tables. The following revision finds the correct answer in 6 iterations. When I tested it for an extreme case of 1 million total (999,999 of standard price and 1 premium) it found the answer in 25 guesses.
totalQuantity = 137
wantedResult = 286.5
premiumPrice = 2.25
standardPrice = 2.00
success = 1
'recursive halving for speed
currentSplit = Int(totalQuantity / 2)
a = currentSplit
b = totalQuantity - a
counter = 0
currentresult = (a * premiumPrice) + (b * standardPrice)
counter = counter + 1
Do While currentresult <> wantedResult
if currentSplit > 1 then
currentSplit = Int(currentSplit / 2)
end if
if counter > totalQuantity then
'safety net
msgbox "result not found"
success = -1
exit do
end if
If currentresult < wantedResult Then
a = a + currentSplit
Else
a = a - currentSplit
End If
counter = counter + 1
b = totalQuantity - a
currentresult = (a * premiumPrice) + (b * standardPrice)
Loop
if success = 1 then
MsgBox a & " at " & premiumPrice & " and " & b & " at " & standardPrice & " in " & counter & " guesses"
end ifThe point is, this function may keep me (or the customer) from having to ask the database vendor for a change to their s
kmoorevs wrote:
only opinions on the soundness of the concept.
I seriously doubt that is going to work. Price1: 2.00 Price2: 2.25 Total price: 18.00 Solution1: 9 items at price1 Solution2: 8 items at price2 Scenario 2 Total price: 36.00 Solution1: 18 items at price1 Solution2: 16 items at price2 Solution3: 9 items at price1 and 8 items at price2
kmoorevs wrote:
The first approach was pulling the inforation directly from the transaction tables which contain millions of records.
No indexes? What about creating a daily summary table locally. Your app pulls daily in the background. You use those local data stores to build your monthly report.
-
kmoorevs wrote:
only opinions on the soundness of the concept.
I seriously doubt that is going to work. Price1: 2.00 Price2: 2.25 Total price: 18.00 Solution1: 9 items at price1 Solution2: 8 items at price2 Scenario 2 Total price: 36.00 Solution1: 18 items at price1 Solution2: 16 items at price2 Solution3: 9 items at price1 and 8 items at price2
kmoorevs wrote:
The first approach was pulling the inforation directly from the transaction tables which contain millions of records.
No indexes? What about creating a daily summary table locally. Your app pulls daily in the background. You use those local data stores to build your monthly report.
the total number of items is also known, so there are two linear equations and two unknown values, yielding as it should zero, one or an infinite number of solutions depending on the values. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
the total number of items is also known, so there are two linear equations and two unknown values, yielding as it should zero, one or an infinite number of solutions depending on the values. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Not two unknown values...that's what confused me at first until I realized that the second unkonwn can be expressed as a relationship to the first unknown, in this case, (137 - a), thus giving the equation: (2.00 * a) + 2.25 * (137 - a) = 386.5 this works out to a = 87, so b = 50. the equation in code from an earlier post is this:
totalQuantity = 137
totalCost = 286.50
a = 0' number of items at the standard price
b = 0' number of items at the premium price or (totalQuantity - a)
standardPrice = 2.00
premiumPrice = 2.25a = (totalCost - (premiumPrice * totalQuantity)) / (standardPrice - premiumPrice)
b = totalQuantity - a
'correctly computes to a = 87 and b = 50This is really starting to remind of aa algebra problem with apples, oranges, and farmer Brown!
"Go forth into the source" - Neal Morse
-
Not two unknown values...that's what confused me at first until I realized that the second unkonwn can be expressed as a relationship to the first unknown, in this case, (137 - a), thus giving the equation: (2.00 * a) + 2.25 * (137 - a) = 386.5 this works out to a = 87, so b = 50. the equation in code from an earlier post is this:
totalQuantity = 137
totalCost = 286.50
a = 0' number of items at the standard price
b = 0' number of items at the premium price or (totalQuantity - a)
standardPrice = 2.00
premiumPrice = 2.25a = (totalCost - (premiumPrice * totalQuantity)) / (standardPrice - premiumPrice)
b = totalQuantity - a
'correctly computes to a = 87 and b = 50This is really starting to remind of aa algebra problem with apples, oranges, and farmer Brown!
"Go forth into the source" - Neal Morse
there really is no need for you to explain me an elementary math problem, you are the one who had a problem with two unknown values (count1 and count2) and two linear equations (totalCount and totalPrice), not me. And from your reply, it is clear to me you now know how to get to a solution without really understanding the general methodology. Maybe you should now try and figure out the counts of tree types of objects (apples, oranges, pears) with known prices (1,2,3) and weights (2,3,5), when the total item count (247), total price (472) and total weight (801) are known. :|
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
there really is no need for you to explain me an elementary math problem, you are the one who had a problem with two unknown values (count1 and count2) and two linear equations (totalCount and totalPrice), not me. And from your reply, it is clear to me you now know how to get to a solution without really understanding the general methodology. Maybe you should now try and figure out the counts of tree types of objects (apples, oranges, pears) with known prices (1,2,3) and weights (2,3,5), when the total item count (247), total price (472) and total weight (801) are known. :|
Luc Pattyn [My Articles] Nil Volentibus Arduum
Sorry Luc, I certainly did not mean to offend. I admit, reposting the solution was a bit overboard. While getting the solution through whatever means was my original intent, getting a solution based on pure math was my goal, understanding the methodology would be a bonus. It's not often that I have to solve these kinds of problems, but it has been refreshing. I could not resist the challenge, though I must admit it through brute force. I really would like to see the correct mathematical approach to this. Apples: 104 : Oranges: 61 : Pears: 82
"Go forth into the source" - Neal Morse
-
Sorry Luc, I certainly did not mean to offend. I admit, reposting the solution was a bit overboard. While getting the solution through whatever means was my original intent, getting a solution based on pure math was my goal, understanding the methodology would be a bonus. It's not often that I have to solve these kinds of problems, but it has been refreshing. I could not resist the challenge, though I must admit it through brute force. I really would like to see the correct mathematical approach to this. Apples: 104 : Oranges: 61 : Pears: 82
"Go forth into the source" - Neal Morse
No problem. And your result is correct; you should have done it by hand though in order to learn something. There basically are two approaches (although they do in essence the same thing): 1. Manually, write down the three (or N) equations. Then replace one by a linear combination of (some of) the others in such a way that one variable disappears; if you do that for all but one of the original equations, always getting rid of the same variable, you end up with n-1 equations in n-1 unknown values (and one of the original equations); you solve that in the same way, so iteratively you get at a single equation, a single unknown. 2. Theoretically, and progrsammatically, you would formulate the set of N equations using a N*N matrix (in the example the coefficients would be 1,1,1, 1,2,3, 2,3,5) and a size-N array with the known values. Then solve that with one of the known algorithms, Gauss-Seidel is one way, LU-decomposition another (it basically does what the manual approach does, with the added benefit that the intermediate steps get recorded in the L- and U-triangle you obtain. Using those, you could quickly solve any similar problem having the same matrix coefficients but different known values. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum