Min Ko Ko Min Ko Ko - 3 months ago 10
MySQL Question

show the result of two date range?

I want the result available rooms between two dates for both rooms.

My query

SELECT *
FROM `roomcalendar`
WHERE day BETWEEN '2016-08-29' and '2016-08-31'


Result
enter image description here

This query does not show any result.

select *
FROM roomcalendar
WHERE day between '2016-08-30' and '2016-08-31'
AND avaroom != 0 having count(*) = datediff('2016-08-31','2016-08-30')


I can use where roomname='Room-A' but I don't want to use it. Any other method to get the both available rooms between two dates with room name?

Answer

If I understand your question correctly what you need might be something like:

SELECT r.roomname, SUM(r.avaroom) as availableRooms
FROM roomcalendar r
WHERE DAY BETWEEN '2016-08-29' AND '2016-08-31'
GROUP BY r.roomname

It I'll give you:

+----------+----------------+
| roomname | availableRooms |
+----------+----------------+
| Room-A   |              5 |
| Room-B   |              4 |
+----------+----------------+

Is this what you want?