Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

06/27/25 01:09:25 (10 days ago)

Author:

221012

Comment:

--

AdvancedReports

@@ -154 +154 @@
 FROM Listing li 
 JOIN Lease l ON li.id = l.listing_id 
-WHERE li.available_from >= CURRENT_DATE - INTERVAL '365 days'
+WHERE li.available_from >= CURRENT_DATE - INTERVAL '365 days' AND l.start_date BETWEEN li.available_from AND li.available_to
 GROUP BY year, month 
 ORDER BY year, month;
 }}}
+
+=== 7. Извештај за број на барања за поправка/сервисирање на недвижност по град
+{{{
+SELECT
+  a.city,
+  COUNT(sr.id) AS request_count
+FROM ServiceRequest sr
+JOIN Lease l ON sr.lease_id = l.id
+JOIN Listing li ON l.listing_id = li.id
+JOIN Unit u ON li.unit_id = u.id
+JOIN Property p ON u.property_id = p.id
+JOIN Address a ON p.address_id = a.id
+WHERE EXTRACT(YEAR FROM sr.request_date) = EXTRACT(YEAR FROM CURRENT_DATE)
+GROUP BY a.city
+ORDER BY request_count DESC
+}}}