ดึงรายงาน จากระบบจองห้องแลป

 SELECT 
  r.id, r.name AS room_name,
COUNT(CASE WHEN YEAR(b.begin) = 2023 THEN b.id END) AS bookings_2023,
 COUNT(CASE WHEN YEAR(b.begin) = 2023 AND b.status = 1 THEN 1 END) AS approved_2023, COUNT(CASE WHEN YEAR(b.begin) = 2023 AND b.status = 0 THEN 1 END) AS pending_2023, COUNT(CASE WHEN YEAR(b.begin) = 2024 THEN b.id END) AS bookings_2024, 
COUNT(CASE WHEN YEAR(b.begin) = 2024 AND b.status = 1 THEN 1 END) AS approved_2024, COUNT(CASE WHEN YEAR(b.begin) = 2024 AND b.status = 0 THEN 1 END) AS pending_2024 FROM booking_rooms r LEFT JOIN booking_reservation b ON b.room_id = r.id WHERE r.published = 1 GROUP BY r.id, r.name ORDER BY r.name;