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

Timestamp:

02/13/25 11:23:50 (9 months ago)

Author:

222039

Comment:

--

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

@@ -131 +131 @@
 ORDER BY total_loans DESC;
 }}}
+
+=== Извештај за анализа на времето за враќање ===
+{{{#!sql
+SELECT 
+    b.Title,
+    AVG(l.ReturnDate - l.LoanDate) as AvgDaysToReturn,
+    MIN(l.ReturnDate - l.LoanDate) as MinDaysToReturn,
+    MAX(l.ReturnDate - l.LoanDate) as MaxDaysToReturn,
+    COUNT(*) as TotalLoans,
+    COUNT(CASE WHEN f.FineID IS NOT NULL THEN 1 END) as LoansWithFines
+FROM 
+    Loan l
+    JOIN Book_Copies bc ON l.BookCopyID = bc.CopyID
+    JOIN Book b ON bc.BookID = b.BookID
+    LEFT JOIN Fine f ON l.LoanID = f.LoanID
+WHERE 
+    l.ReturnDate IS NOT NULL
+GROUP BY 
+    b.BookID, b.Title
+HAVING 
+    COUNT(*) >= 5  -- Only show books with at least 5 loans
+ORDER BY 
+    AVG(l.ReturnDate - l.LoanDate) DESC;
+}}}