Looking up value in a range 1

[v100bhoy]

I have a table:

MilesFrom  MilesTo  Charge
0          2        2.5
3          100      0.5
101        500      0.25

I am wanting to lookup a value in the ranges and return the charge.
Ie a value of 10 would match to 2 x 2.5 and then 8 x 0.5.

I can't get my head around how to do this in CF, any suggestions much appreciated.

 

[LyndonOHRC]

Try:

<cfquery name="getCharge" datasource="MyDataSource">
 Select Charge 
 From Mytable
 Where MyVal>=MilesFrom  
 And MyVal<=MilesTo
</cfquery>

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[v100bhoy]

Lyndon.
I knew there was a simple thing I was missing.
thanks for the help

 

[LyndonOHRC]

No problem; I'm glad to help.

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfSearching]

Where MyVal>=MilesFrom
And MyVal<=MilesTo

Though it works either way, IMO it is a bit more standard to place the variable on the right side of the operators. At least in this type of query. (Obviously substitute cfqueryparam ...)

WHERE MilesFrom <= #someValue#
AND MilesTo >= #someValue#

----------------------------------

http://cfsearching.blogspot.com/

 

[LyndonOHRC]

cfSearching, you caught me. I'm left-handed. LOL

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfSearching]

LOL. Now that is a _great_ response!

----------------------------------

http://cfsearching.blogspot.com/