Changes between Version 3 and Version 4 of izvestai

Timestamp:

01/29/25 17:28:53 (11 months ago)

Author:

213209

Comment:

--

izvestai

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL и складирани процедури)
 
-== Извештај за откжани резервации по ресторан. 
+== Извештај за број на откжани, успешни резервации по ресторан
 
 {{{
-select
-    count(rh.cancellation_reason),
+SELECT 
     au.first_name, 
     au.last_name, 
     au.membership_level, 
     au."role", 
-    rh.cancellation_reason, 
-    rh.restaurant_id 
-from reservation_history rh  
-inner join app_user au on rh.customer_id = au.id 
-where rh.cancellation_reason = 'Canceled by user'
-group by au.first_name, 
-    au.last_name, 
-    au.membership_level, 
-    au."role", 
-    rh.cancellation_reason, 
-    rh.restaurant_id 
-order by rh.cancellation_reason
-}}}
+    rh.restaurant_id,
+    
+    COUNT(CASE WHEN rh.cancellation_reason = 'Canceled by user' THEN 1 END) AS canceled_by_user_per_restaurant,
+
+    COUNT(CASE WHEN rh.cancellation_reason = '/' THEN 1 END) AS succesfull_by_user_per_restaurant,
+    
+    COUNT(CASE WHEN rh.cancellation_reason = 'Canceled by restaurant' THEN 1 END) AS canceled_by_restaurant,
+
+    COUNT(rh.cancellation_reason) AS total_cancellations_per_restaurant
+
+FROM reservation_history rh  
+INNER JOIN app_user au ON rh.customer_id = au.id 
+
+WHERE rh.cancellation_reason IS NOT NULL  
+
+GROUP BY au.first_name, 
+         au.last_name, 
+         au.membership_level, 
+         au."role", 
+         rh.restaurant_id 
+
+ORDER BY canceled_by_user_per_restaurant DESC;}}}