CODEWITHSUNDEEP
mysqlselect
Johrn
Johrn

Reputation: 1440

MySQL - How do I get a range from the point closest to start?

Table structure-

int PrimaryKey
DateTime Date
int Price

What I would like to do is pass in a start and end date, and get the range

From:

The greatest date that is less than or equal to start UNLESS there is no such date, in which case the start of the range should be the lowest date available.

To:

The end date.

My query so far is:

SELECT Date, Price
FROM table1
WHERE Date <= $end
AND Date >= 
(
  SELECT Date
  FROM table1
  WHERE Date <= $start
  ORDER BY Date DESC
  LIMIT 1
)

The subquery gets the largest date that is less than or equal to the start value, which is then used as the low end of the range for the main query. Any suggestions for getting the 'UNLESS' clause in there?

For example, given a table with dates:

Jan 1, 2009
Feb 1, 2009
Mar 1, 2009
Apr 1, 2009

I want a query with $start='Feb 18, 2009' and $end='Apr 30, 2009' to return the three rows Feb 1, Mar 1, Apr 1 (Feb 1, 2009 is the largest date that is less than or equal to $start).

But if I provide $start='Dec 1, 2009' and $end='Apr 30, 2009' I want all four rows returned (There is no date that is less than or equal to $start, so the range starts at the lowest date)

Upvotes: 1

Views: 372

Answers (2)

longneck
longneck

Reputation: 12226

you don't need the "UNLESS there is no such date, in which case the start of the range should be the lowest date available" logic because if you simply select all dates after $start in the case of there not being a previous date, you automatically get the next latest date anyway. and you already know there are no records between $start and the next latest date, so don't bother trying to exclude them!

WHERE Date BETWEEN coalesce((select max(date)
                               from table1
                              where date <= $start)
                            , $start) and $end

Upvotes: 1

mck89
mck89

Reputation: 19241

Why don't you compare it with the $start date? 

SELECT Date, Price
FROM table1
WHERE Date <= $end
AND Date >=$start

I've not tested it but it seems to work for me (if i've understood what you want to do)

Upvotes: 0

Related Questions