Reverse Rounding Method? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reverse Rounding Method?

Author

Topic

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-10 : 14:39:55

Hello Math God,

A mere mortal seeks your assistance with a problem he is struggling with. The following datastructure will be the basis for my issue:

CREATE TABLE dbo.Item
(
ItemPK tinyint NOT NULL PRIMARY KEY,
ItemTypeFK tinyint NOT NULL,
Quantity tinyint NOT NULL ,
PriceForLot decimal(18, 2) NOT NULL
)

CREATE TABLE dbo.ItemType
(
ItemTypePK tinyint NOT NULL PRIMARY KEY,
PricePerUnit decimal(18, 6) NOT NULL
)

INSERT ItemType
(
ItemTypePK,
PricePerUnit
)
VALUES (
1,
0.001
)

INSERT Item
(
ItemPK,
ItemTypeFK,
Quantity,
PriceForLot
)
SELECT DT1.ItemPK,DT1.ItemTypeFK,DT1.Quantity,ROUND(DT1.Quantity*IT.PricePerUnit,2)
FROM
(
SELECT 1 ItemPK,1 ItemTypeFK,1 Quantity UNION ALL
SELECT 2,1,2 UNION ALL
SELECT 3,1,3 UNION ALL
SELECT 4,1,4 UNION ALL
SELECT 5,1,5 UNION ALL
SELECT 6,1,6 UNION ALL
SELECT 7,1,7 UNION ALL
SELECT 8,1,8 UNION ALL
SELECT 9,1,9 UNION ALL
SELECT 10,1,10
) DT1
JOIN ItemType it
ON DT1.ItemTypeFK = it.ItemTypePK

SELECT i.ItemTypeFK,SUM(i.PriceForLot)
FROM Item I
GROUP BY i.ItemTypeFK

Now, the issue I'm faced with is if a customer pays us a different amount than the 0.06 this totaled. I need find an efficient method to determine what a valid PricePerUnit would be if we received .12 or .20 or 1.25. Or even just trying to find the PricePerUnit with the 0.06. I'm having trouble because you can't just take the total quantity and divide by the price because the Quantity changes on each Item and the resulting product is then rounded.

Is this clear? Any thoughts?

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-10 : 16:48:34

Does anyone even have an idea of where I can start? Is there a mathematical concept I should explore?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 17:07:30

so you need to somehow divide the extra payed money among the products in relation with their quatity?

if that's not it then i don't quite understand what you need...

Go with the flow & have fun! Else fight the flow

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-10 : 17:17:36

Expressed simply:

ROUND(X*Y,2) = Z

If I know X and I know Z, how do you determine the possible range of Y?

Once I get this piece, I have to figure out Y for the whole set of data, where X changes N times and I only have the sum of Z.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-10 : 17:20:42

You are correct Spirit1.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 17:30:03

possible range for Y is
Y +- 0.001

basically it's plus or minus 1 digit more than the precision.

Go with the flow & have fun! Else fight the flow

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-10 : 17:57:07

Hmm... that doesn't get me much closer to figuring out Y:

ROUND(1*Y,2)
+ ROUND(2*Y,2)
+ ROUND(3*Y,2)
+ ROUND(4*Y,2)
+ ROUND(5*Y,2)
+ ROUND(6*Y,2)
+ ROUND(7*Y,2)
+ ROUND(8*Y,2)
+ ROUND(9*Y,2)
+ ROUND(10*Y,2)
= 0.01

0.01 / 55 = .000181818 which would give me a range of -0.000819 to 0.001181. The actual range is from 0.000500 to 0.000555. I suppose I could take a set of the range of price values, cross join it to the set of items and group by the PricePerUnit, summing up the calculated rounded product of Quantity * PricePerUnit with a havings clause specifying the calculation is equal to the desired result. I could then take the MIN of that resulting column of PricePerUnit and have my value...

Will this work for all cases and is there a more elegant approach? I will have to cross join 2000 values against my items set and perform two aggregates to get my desired result. My items set can be well over 100,000, which means SQL will be performing a lot of work to do this...

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-11 : 17:26:24

I'm still searching high and low for some kind of mathematical equation, method, or function that I can plug the "Payment" into along with any other needed information and get back the range of possible values for PricePerUnit.

For instance, if I plug in 0.01 and 55, I am looking for 0.000500 as my lower bound and 0.000555 as my higher bound.

I took the liberty of graphing the range difference for a given item set and found it to be alarmingly symetrical. This leads me to believe that there has to be a mathematical way to determine my values.

No takers?

KevinMunro
Starting Member

7 Posts

Posted - 2004-12-12 : 07:10:06

quote:
Originally posted by lazerath

I'm still searching high and low for some kind of mathematical equation, method, or function that I can plug the "Payment" into along with any other needed information and get back the range of possible values for PricePerUnit.

I haven't taken the time to understand the post but it sounds like you are looking for a monte carlo analysis? Do a google for this and see if it helps in anyway and apologies if I have missed the point

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-12 : 12:09:28

This is what I came up with... don't know if its the right idea or not though


Declare @x float,
	@y float,
	@z float,
	@p int

Set @p = 2

Select @x = 55.0000, @z = .0100

--Round(x*y,p)=z

Select 
	z = @z, 
	y_LBound = convert(nvarchar,convert(float,@z-5/convert(float,power(10,@p+1)))/@x),
	y_UBound = convert(nvarchar,convert(float,@z+5/convert(float,power(10,@p+1)))/@x)

Corey

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-12 : 16:01:50

Corey, thanks for taking the time to look into this. I appreciate it. Your formula is beautiful and it gives me a piece of the puzzle I didn't have before. By chance, could you explain how it works?

In any case, I've still got a few pieces of the puzzle left. Lets say I have 0.01. My desired outcome is to apply that money to the rightful packages. With Corey's formula, I am now able to determine the lower bound and upper bound of a single range if I have both X and Z. My problem (and goal) is now determining Z, as all I have is the sum of Z. In the example with 0.01, if I divide it into 55 and use the result of .000182 to determine Z for each package, I will get incorrect results.

I think I may need to apply "leftover" pennies to the packages with the most items and iterating down or something like that.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-12 : 23:41:02

well as far as my formula...

i took the upper and lower bound of the rounded value (in your case .01 would be (.005 <==> .015) and divide that by the x value to undo the multiplication.

As far as the rest of your problem... show a few examples where you know all of the values and what you are trying to do with a clearer example. I'm still having trouble grasping the application of this issue.

Corey

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-13 : 11:01:14

Thanks for the explaination. Its simplicity shames me. Sometimes you stare at a problem long enough...

What I'm trying to do is reconcile payment. You see, in our business, we have a system that requires extensive configuration with an end result of requesting payment. However, there are a ton of factors that can reduce or increase that payment, so in reality we can only produce 'best guesses'. The problem is that we don't get enough detail information back from the customer to really apply differences, so I'm looking at ways to apply the payment back to the items. The decimal precision of the Per Unit Price and the subsequent rounding proved to make this a little more challenging that I had anticipated.

As an example of what I'm trying to do, take 30 'packages'. Each has a quantity of items equal to its sequence (X), so a total of 465 items (SUM of X).

Given the formula ROUND(X*Y,2) = Z and the total package value (SUM of Z) of 0.22, find Z for each package. (X = Quantity, Y=PerUnitPrice, Z=Package Value)

This is the answer:[CODE]X Z
------- -------
1 .000000
2 .000000
3 .000000
4 .000000
5 .000000
6 .000000
7 .000000
8 .000000
9 .000000
10 .010000
11 .010000
12 .010000
13 .010000
14 .010000
15 .010000
16 .010000
17 .010000
18 .010000
19 .010000
20 .010000
21 .010000
22 .010000
23 .010000
24 .010000
25 .010000
26 .010000
27 .010000
28 .010000
29 .010000
30 .020000
[/CODE]

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-13 : 11:08:57

This is the answer when given the SUM of Z = 0.51:[CODE]X Z
------- -------
1 .000000
2 .000000
3 .000000
4 .000000
5 .010000
6 .010000
7 .010000
8 .010000
9 .010000
10 .010000
11 .010000
12 .010000
13 .010000
14 .020000
15 .020000
16 .020000
17 .020000
18 .020000
19 .020000
20 .020000
21 .020000
22 .020000
23 .030000
24 .030000
25 .030000
26 .030000
27 .030000
28 .030000
29 .030000
30 .030000
[/CODE]

Edited to add:
BTW, I have employed my above stated technique of cross joining against a range of 1000 possible Y values, using the approximate produced by (SUM of Z / SUM of X) +- .000500. I believe this is the 'Monte Carlo Analysis' mentioned by another poster (or close to it - I call it the 'brute force method'). I just think there is a more elegant solution...

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-12-13 : 17:08:46

Well, I developed a more elegant solution, but it isn't purely math-based. Instead of cross joining against 1000 possible values, I now cross join against 5 rows, 11 times for a total of 56 rows. I'm basically using a method similar to a binary sort. It allows me to search 4^11 possible combinations very fast.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-14 : 03:37:31

so you're using "dummy" rows??

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources