CODEWITHSUNDEEP
sqlsql-server-2008t-sql
TelJanini
TelJanini

Reputation: 835

Determining if value is between two other values in the same column

I'm currently working on a project that involves using a user-provided charge table to calculate fees.

The table looks like: 

MaxAmount Fee  
 10.00    1.95
 20.00    2.95
 30.00    3.95
 50.00    4.95

As seen in the table above, any MaxAmount up to 10.00 is charged a 1.95 fee. Any MaxAmount between 10.01 and 20.00 is charge a 2.95 fee, etc. Finally, any MaxAmount above 50.00 is charged 4.95.

I'm trying to come up with a sql query that will return the correct fee for a given MaxAmount. However, I'm having trouble doing so. I've tried something similar to the following (assuming a provided MaxAmt of 23.00):

SELECT Fee FROM ChargeTable WHERE 23.00 BETWEEN MaxAmt AND MaxAmt

Of course, this doesn't give me the desired result of 3.95.

I'm having trouble adapting SQL's set-based logic to this type of problem.

Any and all help is greatly appreciated!

Upvotes: 1

Views: 1998

Answers (2)

TheBassMan
TheBassMan

Reputation: 115

You could try something like: 

SELECT min(Fee) FROM Fees WHERE 23<=MaxAmount

Have a look here for an example: http://sqlfiddle.com/#!2/43f2a/5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If the MaxAmount behaves as the table suggests, then you can use:

select top 1 fee
from ChargeTable ct
where @Price <= MaxAount
order by MaxAmount desc

As you describe it, you really want another row:

MaxAmount Fee  
  0.00    1.95
 10.00    1.95
 20.00    2.95
 30.00    3.95
 50.00    4.95

Your original table does not have enough values. When you have 4 break points, you actually need 5 values -- to handle the two extremes.

With this structure, then you can do:

select top 1 fee
from ChargeTable ct
where @Price >= MaxAount
order by MaxAmount desc

Upvotes: 1

Related Questions