SQL query for finding a value in multiple ranges

i have MySQL db that contains event's date and 3 ranges, i.e from1-to1, from2-to2, from3-to3

each range has different price, i.e from1-to1 rate1 , from2-to2 rate2, ...

so that's 3 columns for each range: from, to and 开发者_运维技巧rate.

i'm trying to find the query that returns the rate for a given month, meaning finds the range that the month is in and returns the rate of that range.

any ideas?

thanks!

If you make an extra table just for the ranges you would keep your schema in normal form and you could easy select the right rate: TABLE range, COLUMNS from, to, rate. With a foreign key linking to your original table. Then you could SELECT rate FROM range WHERE 'date' >= from AND 'date' <= to.

It seems like your data model is not normalized. You should consider morjas suggestion about creating an additional table.

Below is a really ugly query that checks whether a date is in any of the three ranges, and then returns the matching rate.

select case 
        when date '2010-12-05' between range1_from and range1_to then range1_rate
        when date '2010-12-05' between range2_from and range2_to then range2_rate
        when date '2010-12-05' between range3_from and range3_to then range3_rate
       end as rate
  from events
 where date '2010-12-05' between range1_from and range1_to
    or date '2010-12-05' between range2_from and range2_to
    or date '2010-12-05' between range3_from and range3_to;