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

Timestamp:

02/24/25 16:19:07 (3 months ago)

Author:

222039

Comment:

--

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

@@ -240 +240 @@
 }}}
 
-* π MemberName, Membership_Status, PeriodStart, PeriodEnd, TotalLoans, ActiveLoans, TotalFines, TotalFineAmount, TotalPaidAmount
-* (σ (DateRanges ⨯ Member ⨝ Users ⨝ (Loan ⨝ Fine ⨝ FinePayment))
-* (γ MemberName, Membership_Status, PeriodStart, PeriodEnd; COUNT(LoanID) AS TotalLoans, COUNT(CASE WHEN ReturnDate IS NULL THEN LoanID END) AS ActiveLoans, COUNT(FineID) AS TotalFines, SUM(FineAmount) AS TotalFineAmount, SUM(PaymentAmount) AS TotalPaidAmount)
-* (ORDER BY PeriodStart DESC, TotalLoans DESC)
+π_{MemberName, Membership_Status, PeriodStart, PeriodEnd, TotalLoans, ActiveLoans, TotalFines, TotalFineAmount, TotalPaidAmount}  
+(τ_{PeriodStart DESC, TotalLoans DESC}  
+ (γ_{FirstName, LastName, Membership_Status, start_date, end_date}  
+    (σ_{Loan.LoanDate ≥ start_date ∧ Loan.LoanDate < end_date}  
+      (DateRanges × Member ⨝_{Member.UserID = Users.UserID} Users  
+        ⟕_{Member.MemberID = Loan.MemberID} Loan  
+        ⟕_{Loan.LoanID = Fine.LoanID} Fine  
+        ⟕_{Fine.FineID = FinePayment.FineID} FinePayment
+      )  
+    )  
+    COUNT(DISTINCT Loan.LoanID) → TotalLoans,  
+    COUNT(DISTINCT σ_{Loan.ReturnDate IS NULL} (Loan.LoanID)) → ActiveLoans,  
+    COUNT(DISTINCT Fine.FineID) → TotalFines,  
+    COALESCE(SUM(Fine.FineAmount), 0) → TotalFineAmount,  
+    COALESCE(SUM(FinePayment.PaymentAmount), 0) → TotalPaidAmount  
+  )  
+ )
 
 {{{#!sql