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

Timestamp:

01/11/25 16:16:10 (4 days ago)

Author:

222039

Comment:

--

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

@@ -20 +20 @@
 }}}
 
-=== Извештај за најпозајмени книги ===
+=== Извештај за најпозајмени книги по тримесечие ===
 {{{#!sql
-SELECT book.CoverImage, book.Title, book.Genre, author.FirstName, author.LastName, COUNT(loan.LoanID) AS loan_count FROM book
+SELECT 
+    book.CoverImage,
+    book.Title,
+    book.Genre,
+    author.FirstName,
+    author.LastName,
+    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
+    COUNT(loan.LoanID) AS loan_count
+FROM book
 INNER JOIN book_copies ON book.BookID = book_copies.BookID
 LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
 INNER JOIN book_author ON book.BookID = book_author.BookID
 INNER JOIN author ON book_author.AuthorID = author.AuthorID
-GROUP BY book.BookID, book.Title, book.CoverImage, book.Genre, author.FirstName, author.LastName
-ORDER BY loan_count DESC LIMIT 5;
+WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
+GROUP BY 
+    book.BookID,
+    book.Title,
+    book.CoverImage,
+    book.Genre,
+    author.FirstName,
+    author.LastName,
+    EXTRACT(QUARTER FROM loan.LoanDate)
+ORDER BY loan_count DESC
+LIMIT 5;
 }}}
 
-=== Извештај за најмалку позајмувани книги ===
+=== Извештај за најмалку позајмувани книги по тримесечие ===
 {{{#!sql
-SELECT book.CoverImage, book.Title, book.Genre, author.FirstName, author.LastName, COUNT(loan.LoanID) AS loan_count FROM book
+SELECT 
+    book.CoverImage,
+    book.Title,
+    book.Genre,
+    author.FirstName,
+    author.LastName,
+    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
+    COUNT(loan.LoanID) AS loan_count
+FROM book
 INNER JOIN book_copies ON book.BookID = book_copies.BookID
 LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
 INNER JOIN book_author ON book.BookID = book_author.BookID
 INNER JOIN author ON book_author.AuthorID = author.AuthorID
-GROUP BY book.BookID, book.Title, book.CoverImage, book.Genre, author.FirstName, author.LastName
-ORDER BY loan_count ASC LIMIT 5;
+WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
+GROUP BY 
+    book.BookID,
+    book.Title,
+    book.CoverImage,
+    book.Genre,
+    author.FirstName,
+    author.LastName,
+    EXTRACT(QUARTER FROM loan.LoanDate)
+ORDER BY loan_count ASC
+LIMIT 5;
 }}}