Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

01/29/25 15:13:15 (3 weeks ago)

Author:

215010

Comment:

--

AdvancedReports

@@ -34 +34 @@
  
 {{{
+SELECT 
+    hb.building_id AS Building,
+    hb.city AS City,
+    COUNT(r.room_number) AS Total_Rooms,
+    COUNT(CASE 
+        WHEN res.start_date <= '2025-01-15' AND 
+             (res.end_date >= '2025-01-01' OR res.end_date IS NULL) 
+        THEN 1 END) AS Occupied_Rooms,
+    ROUND(
+        (COUNT(CASE 
+            WHEN res.start_date <= '2025-01-15' AND 
+                 (res.end_date >= '2025-01-01' OR res.end_date IS NULL) 
+            THEN 1 END) / COUNT(r.room_number)) * 100, 
+        2
+    ) AS Utilization_Percentage
+FROM 
+    Hotel_Building hb
+LEFT JOIN Room r ON hb.building_id = r.building_id
+LEFT JOIN Reservation res ON r.room_number = res.room_number 
+                          AND r.building_id = res.building_id
+                          AND (res.start_date <= '2025-01-15' AND 
+                               (res.end_date >= '2025-01-01' OR res.end_date IS NULL))
+GROUP BY 
+    hb.building_id, hb.city
+ORDER BY 
+    Utilization_Percentage DESC;
+
 
 }}}