MySQL: Count the total occupancy rate for a time period (month)

Recently I encountered a somewhat interesting MySQL/PHP task: determine occupancy for a reservation system. This entailed determining how many reservation days fall within a particular month. It was not possible to simply determine each reservation length (datediff from/to) and sum them because some reservations started before the month in question and other reservations lasted until the month was over.

Therefore, one solution is to get the reservation lengths then compare the start and end of the reservation to the start and end of the month. The following examples illustrate the evolution of my particular solution:

Example 1

Example 1

SELECT
res_hotel_booking_bookings.`id`,
res_hotel_booking_bookings.`from`,
DATEDIFF(res_hotel_booking_bookings.`from`,”2013-10-1″) AS fromBeginning,
res_hotel_booking_bookings.`to`,
DATEDIFF(“2013-10-31”,res_hotel_booking_bookings.`to`) AS fromEnd,
res_hotel_booking_bookings.`status`,
DATEDIFF(res_hotel_booking_bookings.`to`,res_hotel_booking_bookings.`from`) AS days
FROM
res_hotel_booking_bookings
WHERE res_hotel_booking_bookings.`status` <> “cancelled” AND res_hotel_booking_bookings.`status` <> “pending”
AND res_hotel_booking_bookings.to >= “2013-10-1” AND res_hotel_booking_bookings.`from` <= “2013-10-31”

Example 2

Example 2

SELECT
res_hotel_booking_bookings.`id`,
res_hotel_booking_bookings.`from`,
CASE
WHEN DATEDIFF(res_hotel_booking_bookings.`from`,”2013-10-1″) < 0
THEN DATEDIFF(res_hotel_booking_bookings.`from`,”2013-10-1″)
ELSE 0
END AS fromBeginning,
res_hotel_booking_bookings.`to`,
DATEDIFF(“2013-10-31”,res_hotel_booking_bookings.`to`) AS fromEnd,
CASE
WHEN DATEDIFF(“2013-10-31”,res_hotel_booking_bookings.`to`) < 0
THEN DATEDIFF(“2013-10-31”,res_hotel_booking_bookings.`to`)
ELSE 0
END AS fromEnd,
res_hotel_booking_bookings.`status`,
DATEDIFF(res_hotel_booking_bookings.`to`,res_hotel_booking_bookings.`from`) AS days
FROM
res_hotel_booking_bookings
WHERE res_hotel_booking_bookings.`status` <> “cancelled” AND res_hotel_booking_bookings.`status` <> “pending”
AND res_hotel_booking_bookings.to >= “2013-10-1” AND res_hotel_booking_bookings.`from` <= “2013-10-31”

At this point I was ready to do the final calculations in PHP, here is the pseudo code:
$reservationlength = $days + $fromEnd + $fromBeginning;

Looping through the MySQL result and adding all the reservation lengths together allowed me to get the total reservation day/unit count:
for each reservation, add $reservationlength to $totalreservations
$totalreservations == 677

Lastly, pseudo code showing how we get the occupancy percentage:
$maxreservation = 31 * 36;
677/1116 = $totalreservations / $maxreservation = 0.597

Thus we have an occupancy rate of 60% for the month!

I hope this page is helpful to you if you encounter a similar situation 🙂

2 comments

  • Marcos Gonzalez

    Hello J.D. Hodges,
    I recently had the need for something very similar for a personal website I was designing. After viewing your code above, I came up with the following:

    select Data.RoomID AS RoomID, Data.Period AS Period, Data.Month AS Month, sum(Data.Days) AS Days, ((sum(Data.Days) / dayofmonth(last_day(Data.Guest_Checkin))) * 100) AS Percent from
    (select RoomID AS RoomID,
    year(Guest_Checkout) AS Period,
    month(Guest_Checkout) AS Month,
    (Guest_Checkout – interval (dayofmonth(Guest_Checkout) – 1) day) AS Guest_Checkin,
    Guest_Checkout AS Guest_Checkout,
    (to_days(Guest_Checkout) – to_days((Guest_Checkout – interval (dayofmonth(Guest_Checkout) – 1) day))) AS Days
    from Guest
    where ((month(Guest_Checkin) month(Guest_Checkout)) and (Active = 1))
    union all
    select RoomID AS RoomID,
    least(year(Guest_Checkin),
    year(Guest_Checkout)) AS Period,
    month(Guest_Checkin) AS Month,
    Guest_Checkin AS Guest_Checkin,
    least(Guest_Checkout,last_day(Guest_Checkin)) AS Guest_Checkout,
    (to_days(least(Guest_Checkout,last_day(Guest_Checkin))) – to_days(Guest_Checkin)) AS Days
    from Guest
    where ((Active = 1) and (Guest_Checkout <= curdate()))) AS Data
    group by Data.Period, Data.Month
    order by Data.Period, Data.Month

    Since you shared your code which the world and it was very helpful to me, I in turn share with you my alternative solution. Thank you again for your help. By the way I saved this code as a view and I can query which ever time period I need.

Leave a Reply

Your email address will not be published. Required fields are marked *