MySQL query date ranges

Question

I have googled but did not find anything related. I have a MySQL table like this:

+++++++++++++++++++++++++++++++
| roomID |    date    | price |
+++++++++++++++++++++++++++++++
|  1     | 2012-10-10 |   10  |
|  1     | 2012-10-11 |   10  |
|  1     | 2012-10-12 |   10  |
|  1     | 2012-10-13 |   12  |
|  2     | 2012-10-10 |   15  |
|  2     | 2012-10-11 |   15  |
|  2     | 2012-10-12 |   15  |
|  2     | 2012-10-13 |   16  |
|  2     | 2012-10-14 |   16  |
|  2     | 2012-10-15 |   16  |
+++++++++++++++++++++++++++++++

I need to get periods based on price and roomID:

++++++++++++++++++++++++++++++++++++++++++++
| roomID |    from    |    till    | price |
++++++++++++++++++++++++++++++++++++++++++++
|    1   | 2012-10-10 | 2012-10-12 |   10  |
|    1   | 2012-10-13 | 2012-10-13 |   12  |
|    2   | 2012-10-10 | 2012-10-12 |   15  |
|    2   | 2012-10-13 | 2012-10-15 |   16  |
++++++++++++++++++++++++++++++++++++++++++++

Thank you!

juergen d · Accepted Answer · 2012-10-13 12:26:23Z

6

select roomid,
       min(date) as from,
       max(date) as till,
       price
from periods
group by price
order by price

answered Oct 13, 2012 at 12:26

juergen d

205k40 gold badges304 silver badges377 bronze badges

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

Comments

Shubhanshu Mishra · Accepted Answer · 2012-10-13 12:31:27Z

4

You can try using the following query:

SELECT roomid, MIN(date) AS `from`, MAX(date) AS `till`, price
FROM tableName
GROUP BY price
ORDER BY price

answered Oct 13, 2012 at 12:31

Shubhanshu Mishra

6,7506 gold badges25 silver badges24 bronze badges

1 Comment

Ravinder Reddy Over a year ago

+1 for using backticks ( ` ) over key words as column/expression names.

Collectives™ on Stack Overflow

MySQL query date ranges

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related