Report Building: Occupancy calculations

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,

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

Hi Tim,

This is a bit more of a complicated case!

I could provide you with a query to get the occupancy for a specific date, but I can't think of any pure-MySQL solution for the entire report. The difficulty lies in the fact that all of the days that need reporting on don't necessarily exist in the database. For example, in the dates you give, if Fred Blogs doesn't start his stay until Jan 3, then Jan 2 doesn't "exist" in the data at all, and I wasn't able to find a way to extrapolate it in this way. It could be possible if there was a secondary database that just contained the dates for the report, but that would require a fair bit of manual work.

Likely your best bet is with a customized version of the Report Builder plugin to produce this report; this sort of calculation is much better suited to PHP scripts. As always, we're happy to provide an estimate if you need any help with this!

Thanks,