Determine a value associated with a range

[omondragon]

I have a table that shows a range and a value associated with that range...see example below. I have another data source that has values. Is there any easy way to determine if my values fall within one of the low and high values and determine which label it is (e.g. if my datasource has a value of 7 it would give it a label of 1). Do i have to hardcode this or can it remain based on my table (in case they change i would rather just change the table rather than my code). Any ideas?

Label Low High
1 5 10
2 11 15

 

[PHV]

if my datasource has a value of 7
Which datasource ?
Sorry, crystal bowl in vacation and mind-reader power gone ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VRoscioli]

If you assume the ranges will never overlap, you can try:

SELECT tblDataSource.*, tblRanges.Label FROM tblDataSource LEFT JOIN tblRanges ON tblDataSource.Value <= tblRanges.High AND tblDataSource.Value >= tblRanges.Low;

-V

 

[omondragon]

Thanks V...that is exactly what i was looking for.

One other question, how does the statement change if I am looking at dates. i am now trying to assign a fiscal quarter based on a transaction date (e.g. if a transaction occurred on Feb 1st then it would assign it a Q1 label...i do have a table that describes the beginning and end date of each quarter).

 

[VRoscioli]

You should be able to use the same code.


-V