none
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

Answers

  • 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 Saykao Friday, July 31, 2009 7:41 PM
    Saturday, February 07, 2009 5:50 AM
    Moderator

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 ASCas 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
    Moderator
  •  

    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 Saykao Friday, July 31, 2009 7:41 PM
    Saturday, February 07, 2009 5:50 AM
    Moderator