Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

08/28/26 00:18:25 (5 days ago)

Author:

181201

Comment:

--

AdvancedReports

@@ -220 +220 @@
         b.owner_id,
         s.type AS service_type,
-        COUNT(bs.service_id) AS times_booked,
-        ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(bs.service_id) DESC) as rank_num
-    FROM bookings b
-    JOIN booking_services bs ON b.booking_id = bs.booking_id
-    JOIN services s ON bs.service_id = s.service_id
+        COUNT(b.service_id) AS times_booked,
+        ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(b.service_id) DESC) as rank_num
+    FROM bookings b
+    JOIN services s ON b.service_id = s.service_id
     JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
     GROUP BY b.owner_id, s.type
@@ -301 +300 @@
   owner_id := b.owner_id;
   service_type := s.type;
-  times_booked := COUNT(bs.service_id)
-(
-  (
-    (bookings b ⨝ (b.booking_id = bs.booking_id) booking_services bs)
-    ⨝ (bs.service_id = s.service_id) services s
-  )
+  times_booked := COUNT(b.service_id)
+(
+  (bookings b ⨝ (b.service_id = s.service_id) services s)
   ⨝ (b.date_from ≥ p.start_date ∧ b.date_from < p.end_date) Params p
 )
@@ -373 +369 @@
 )
 }}}
+
+== Version history
+
+[https://develop.finki.ukim.mk/projects/petsitter/wiki/AdvancedReports_v01 v01]
+* Initial version
+
+[https://develop.finki.ukim.mk/projects/petsitter/wiki/AdvancedReports v02]
+* Current version, based on version [https://develop.finki.ukim.mk/projects/petsitter/wiki/ERModel v03] of the ER model
+* In '''Highest paying customers''' report, {{{service_counts}}} now joins {{{services}}} directly on {{{b.service_id}}} and counts {{{b.service_id}}}
+* Updated the relational algebra of the '''ServiceCounts''' relation accordingly; it is now a single join between {{{bookings}}} and {{{services}}}