Room booking system - calculating room occupancy

  • Just for variety, here's an alternative that should work.

    WITH 
    filtered_and_adjusted AS
    (
    SELECT Room,
    Date_,
    adjusted_booking_start_time =CASE WHEN Booking_start_time <'08:30' THEN '08:30' ELSE Booking_start_time END,
    adjusted_booking_end_time =CASE WHEN Booking_end_time >'16:00' THEN '16:00' ELSE Booking_end_time END
    FROM Bookings
    WHERE Booking_start_time <'16:00'
    AND
    Booking_end_time >'08:30'
    )
    ,
    latest_and_second_latest_end AS
    (
    SELECT *,
    latest_end =MAX(adjusted_booking_end_time) OVER (PARTITION BY room, date_ ORDER BY adjusted_booking_start_time ASC ROWS UNBOUNDED PRECEDING),
    second_latest_end =MAX(adjusted_booking_end_time) OVER (PARTITION BY room, date_ ORDER BY adjusted_booking_start_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM filtered_and_adjusted
    )

    SELECT Room,Date_,
    total_booking_duration_minutes=SUM(DATEDIFF(MINUTE,CASE WHEN second_latest_end>adjusted_booking_start_time THEN second_latest_end ELSE adjusted_booking_start_time END,adjusted_booking_end_time))
    FROM latest_and_second_latest_end
    WHERE adjusted_booking_end_time=latest_end
    GROUP BY Room,Date_
    ORDER By Room,Date_;

    Cheers!

Viewing post 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply