Report Building: Occupancy calculations

4 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: July 5, 2019   (RSS)

By Toledoh - June 30, 2019 - edited: June 30, 2019

Hi Guys.

I'm trying to report on the occupancy rate of a hotel.  We have the bookings table which records check_in and check_out dates.  I've got the following:

SELECT DATE_FORMAT(check_in, '%M %Y') as 'Date',
COUNT(check_in) as 'Qty'
FROM <?php echo $TABLE_PREFIX ?>bookings
GROUP BY Date
ORDER BY YEAR(check_in) ASC, MONTH(check_in) ASC

This give me the number of check_in 's per month.  But I actually need the number of days between the check_in and the check_out.

To make this even more complex, if a check_in is in one month, and the check_out is in another month - how do we get the occupancy correct per month?

Maybe I need to break out all bookings into "occupancies" each with a single date.  Then group and report on those occupancies?

Cheers,

Tim (toledoh.com.au)

By daniel - July 2, 2019 - edited: July 2, 2019

Hey Tim,

Handily, MySQL offers a function for this exact purpose: DATEDIFF(). It will look something like this:

SELECT DATE_FORMAT(check_in, '%M %Y') as 'Date',
COUNT(check_in) as 'Qty',
DATEDIFF(check_out, check_in) as 'Duration'
FROM <?php echo $TABLE_PREFIX ?>bookings
GROUP BY Date
ORDER BY YEAR(check_in) ASC, MONTH(check_in) ASC

I can't guarantee how it will interact with the GROUP BY in your original query, but this will hopefully get you on the right track. You may need to use SUM(DATEDIFF(...)) to get the totals for each grouping.

Let me know how that works for you, or if you have any questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By Toledoh - July 4, 2019

Thanks Daniel.

I've realised now that I've asked the wrong question. I need to show every day, and the number of bookings on that day.  ie.

Joe Blogs:  1 Jan 2019 - 3 Jan 219
Fred Blogs:  2 Jan 2019 - 4 Jan 2019

So Joe is staying nights of 1 Jan, 2 Jan and leaves on 3 Jan

Fred staying nights of 2 Jan and 3 Jan and leaves on 4 Jan

How do I report:

1 Jan: 1 booking
2 Jan : 2 bookings
3 Jan: 1 booking

Cheers,

Tim (toledoh.com.au)