Post reply

Finding a value by interpolation

  • April 14, 2016 at 11:01 am

    #313297

    I need to find a value based on a number in another table with values when the record does not exist.

    For example my data record:

    Code Count value

    5 20.7 25,000.00

    Value table

    Code Number Value

    5 10.00 1.9201

    5 15.00 1.9100

    5 20.00 1.8937

    5 25.00 1.8589

    5 30.00 1.8700

    How would I write this to find the two records it falls between and do the interpolation? for both tables the data type is code =char number = float and value = money.

    Thanks

  • April 14, 2016 at 11:21 am

    #1872096

    not sure if this is what you are after, this was my first guess

    /*--Results

    ClosestUnder ClosestOver

    20 25

    */

    ;WITH MyCTE([Code],[Number],[Value])

    AS

    (

    SELECT '5',convert(float,'10.00'),convert(money,'1.9201') UNION ALL

    SELECT '5','15.00','1.9100' UNION ALL

    SELECT '5','20.00','1.8937' UNION ALL

    SELECT '5','25.00','1.8589' UNION ALL

    SELECT '5','30.00','1.8700'

    )

    SELECT MAX(ClosestUnder) AS ClosestUnder,max(ClosestOver) AS ClosestOver

    FROm (

    SELECT MAX([Number]) As ClosestUnder,NULL ClosestOver FROM

    MyCTE T1

    WHERE [Number] <=20.7

    UNION

    SELECT NULL As ClosestUnder,MIN([Number]) ClosestOver

    FROM MyCTE T2

    WHERE [Number] > 20.7

    ) MyAlias

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply