SQL query for finding a value in multiple ranges

Question

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!

morja · Accepted Answer · 2010-12-03 09:48:37Z

2

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.

answered Dec 3, 2010 at 9:48

morja

8,5703 gold badges42 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ronnis · Accepted Answer · 2010-12-03 10:32:04Z

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;

thank you. what do you mean by "not normalized"? and what is the "date" keyword doing?
The date keyword is a way of specifying a date literal. You tell the database that this string is to be treated as a date.

Collectives™ on Stack Overflow

SQL query for finding a value in multiple ranges

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related