如果在输入的日期范围内不存在，请获取最近的日期

Question

Im doing a backend now, and I'm trying to make a list of number of rooms by date range.

DB Structure

id room_type_id room_count date_applied
1  1            2          2011-09-01
1  1            3          2011-09-05
1  1            1          2011-09-06

In the HTML:

If the user inputted From: 2011-Sept-01 To: 2011-Sept-06. This would be the display. (This is already OK)

Date          Room Count
2011-Sept-1   2
2011-Sept-2   2
2011-Sept-3   2
2011-Sept-4   2
2011-Sept-5   3
2011-Sept-6   1

And my problem now is that, when the user inputted into From and To is already out of range from the data stored in the database. Sample: From: 2011-09-07 To: 2011-09-09

The Output should be displayed in the HTML should be look like this.

Date          Room Count
2011-Sept-7   1
2011-Sept-8   1
2011-Sept-9   1

It will get the closest/last data in the database. For this sample it would be 2011-09-06

Thanks for any help.

Answer 1

For that you may need to run a sub query finding the immediate lowest date? and then start from that date to the end date.

Forexample.

SELECT * FROM tbl WHERE date BETWEEN (SELECT date FROM tbl WHERE date < lower_range LIMIT 1 ) AND upper_range