# How to interpolate number by using data from two tables?

### Question

•

Here is my data let say:

In Products table has a cost field and data = 102

In Pricing table has a range from low to high cost fields = 12, 45, 110, 175, 200 with respect to a range of sell_Price fields 20, 50, 200, 220, 230

If anyone can get me a simple query string that will be a great help?

Friday, February 06, 2009 9:56 PM

• OK - but what kind of logic are you using?  It's not immediately intuitive why you would link 110 to 200, 175 to 220, and 200 to 230.  What "rule" are you trying to implement?
Aaron Alton | thehobt.blogspot.com
• Marked as answer by Friday, July 31, 2009 7:41 PM
Saturday, February 07, 2009 5:50 AM

### All replies

• Saykao,

Your question isn't very clear.  Are you looking to associate a given product with the next highest cost tier in the pricing table?  If so, you could use this:

 CREATE TABLE Products ( ProductID varchar(20), Cost decimal(15,3) ) CREATE TABLE Pricing ( MaxPrice decimal(15,3) ) INSERT Products(ProductID, Cost) SELECT 'WidgetA', 100 UNION ALL SELECT 'WidgetB', 20 INSERT Pricing(MaxPrice) SELECT 20 UNION ALL SELECT 60 UNION ALL SELECT 110 UNION ALL SELECT 200 GO SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY pr.MaxPrice ASC) as RowNumber FROM Products AS p LEFT OUTER JOIN Pricing AS pr ON p.Cost <= pr.MaxPrice) AS a WHERE a.RowNumber = 1

Aaron Alton | thehobt.blogspot.com
Friday, February 06, 2009 10:43 PM
•

Aaron Alton,

Thanks for posted a reply so quick.

I’m sure did not make myself clear here.

What I want is that the Query should return a sell price correspond to the product cost.

The sell price can fall in between any two sell_price data in the pricing table, and this value make it harder to form a query string.

Example:

Cost    sell_price

12                20

45                            50

102          ?? need this number and it is not necessary in the Pricing table.

110                        200

175                        220

200                        230

Hope this help clear my question.

Friday, February 06, 2009 11:13 PM
• OK - but what kind of logic are you using?  It's not immediately intuitive why you would link 110 to 200, 175 to 220, and 200 to 230.  What "rule" are you trying to implement?
Aaron Alton | thehobt.blogspot.com
• Marked as answer by Friday, July 31, 2009 7:41 PM
Saturday, February 07, 2009 5:50 AM