Min Ko Ko Min Ko Ko - 1 year ago 43
MySQL Question

show the result of two date range?

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

My query

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

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 Source

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?