Post reply

Subquery: need to retrieve comparison result for additional use

  • September 9, 2004 at 7:59 am

    #63425

    Simplified version of problem, new to T-SQL, using VB:

    During a subquery, I need to perform comparisons on ranges to find the correct one and return the corresponding Adjustment for that range.

     

    TableID          LowA        HighA      AdjA      LowB      HighB     AdjB

    11111             1             10          $5          11          20        $6

     

    So, if the user inputs 4, it would fall into the "A" range and I would return AdjA, $5.  I realize this isn't fully normalized, but is there any easy solution without changing the table structure?

  • September 9, 2004 at 8:45 am

    #522335

    You haven't said anything about What happens if both ranges are overlapped or similar  nor what is the pk of the table so under too many assumptions I am going to give you this quick and dirty way:

     

    select Q.Adj

    from

    ( select TableID, LowA as Low, HighA as High, AdjA as Adj

             from TableName

            union all

              select TableID, LowB, HighB, AdjA

             from TableName) Q

    where Q.TableID = 11111 and

    UserInPut between Q.Low and Q.High

     


    * Noel

  • September 9, 2004 at 8:47 am

    #522337

    Let me know if this does the trick.

    Select

     CASE WHEN @Value BETWEEN LowA AND HighA THEN AdjA

     WHEN @value BETWEEN LowB AND HighB THEN AdjB

     ELSE 0 END AS Return_Value

    FROM Table_Name_Here

  • September 9, 2004 at 7:35 pm

    #522427

    Thanks cowboy, this worked nicely

Viewing 4 posts - 1 through 3 (of 3 total)

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