Room booking system - calculating room occupancy

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  March 24, 2020 at 4:25 pm

  #3736655

  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!