Changes between Version 18 and Version 19 of Напредни извештаи од базата (SQL и складирани процедури)

Timestamp:

02/13/25 11:26:35 (9 months ago)

Author:

222039

Comment:

--

Напредни извештаи од базата (SQL и складирани процедури)

@@ -155 +155 @@
     AVG(l.ReturnDate - l.LoanDate) DESC;
 }}}
+
+=== Извештај за активност на членови ===
+{{{#!sql
+WITH DateRanges AS (
+    SELECT
+        generate_series(
+            date_trunc('year', CURRENT_DATE) - interval '5 year',
+            date_trunc('year', CURRENT_DATE),
+            interval '1 year'
+        ) as start_date,
+        generate_series(
+            date_trunc('year', CURRENT_DATE) - interval '4 year',
+            date_trunc('year', CURRENT_DATE),
+            interval '1 year'
+        ) as end_date
+)
+SELECT 
+    u.FirstName || ' ' || u.LastName as MemberName,
+    m.Membership_Status,
+    d.start_date::date as PeriodStart,
+    d.end_date::date as PeriodEnd,
+    COUNT(DISTINCT l.LoanID) as TotalLoans,
+    COUNT(DISTINCT CASE WHEN l.ReturnDate IS NULL THEN l.LoanID END) as ActiveLoans,
+    COUNT(DISTINCT f.FineID) as TotalFines,
+    COALESCE(SUM(f.FineAmount), 0) as TotalFineAmount,
+    COALESCE(SUM(fp.PaymentAmount), 0) as TotalPaidAmount
+FROM 
+    DateRanges d
+    CROSS JOIN Member m
+    JOIN Users u ON m.UserID = u.UserID
+    LEFT JOIN Loan l ON m.MemberID = l.MemberID 
+        AND l.LoanDate >= d.start_date 
+        AND l.LoanDate < d.end_date
+    LEFT JOIN Fine f ON l.LoanID = f.LoanID
+    LEFT JOIN FinePayment fp ON f.FineID = fp.FineID
+GROUP BY 
+    u.FirstName, u.LastName, m.Membership_Status, d.start_date, d.end_date
+ORDER BY 
+    d.start_date DESC, TotalLoans DESC;
+}}}