Koha SQL Reports
1. Fine-wise Patron list & Item Information
SELECT
b.surname, b.firstname, b.email, bib.title, i.barcode,
a.amountoutstanding, ni.issuedate, ni.date_due,
IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate
FROM accountlines a
LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber )
LEFT JOIN items i ON ( a.itemnumber = i.itemnumber )
LEFT JOIN biblio bib ON ( i.biblionumber = bib.biblionumber )
LEFT JOIN ( SELECT * FROM issues UNION SELECT * FROM old_issues ) ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber )
WHERE
a.amountoutstanding > 0
GROUP BY a.description
ORDER BY b.surname, b.firstname, ni.timestamp DESC
SELECT
b.surname, b.firstname, b.email, bib.title, i.barcode,
a.amountoutstanding, ni.issuedate, ni.date_due,
IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate
FROM accountlines a
LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber )
LEFT JOIN items i ON ( a.itemnumber = i.itemnumber )
LEFT JOIN biblio bib ON ( i.biblionumber = bib.biblionumber )
LEFT JOIN ( SELECT * FROM issues UNION SELECT * FROM old_issues ) ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber )
WHERE
a.amountoutstanding > 0
GROUP BY a.description
ORDER BY b.surname, b.firstname, ni.timestamp DESC
2. List of all accounting details in the date range
SELECT
CASE accounttype
WHEN 'A' THEN 'Account management fee'
WHEN 'C' THEN 'Credit'
WHEN 'F' THEN 'Overdue Fine'
WHEN 'FOR' THEN 'Forgiven'
WHEN 'FU' THEN 'Overdue Fine Still Accruing'
WHEN 'L' THEN 'Lost Item'
WHEN 'LR' THEN 'Lost and Returned'
WHEN 'M' THEN 'Sundry'
WHEN 'N' THEN 'New Card'
WHEN 'PAY' THEN 'Payment'
WHEN 'W' THEN 'Writeoff'
ELSE accounttype END
AS transaction, SUM(amount)
FROM accountlines
WHERE DATE(timestamp) BETWEEN <> AND <>
GROUP BY accounttype
3. List of late items
SELECT b.title, s.serialseq, s.planneddate
FROM serial s LEFT JOIN biblio b USING (biblionumber)
WHERE s.planneddate < CURDATE()
4. Active Patrons list since a specific date
SELECT DISTINCT surname, firstname, cardnumber, email, address,
address2, city, state, zipcode
FROM borrowers
WHERE borrowernumber IN
(SELECT borrowernumber
FROM statistics
WHERE borrowernumber = borrowernumber
AND datetime >= <>)
ORDER BY surname, firstname
5. Count of new items between specific dates
SELECT monthname(timestamp) AS month, year(timestamp) AS year, count(itemnumber) AS count
FROM items
WHERE timestamp BETWEEN <> AND <>
GROUP BY year(timestamp), month(timestamp)
6. Find all items since a specific date
SELECT b.title, i.barcode, i.itemcallnumber,
IF(i.onloan IS NULL, '', 'checked out') AS onloan
FROM biblio b
LEFT JOIN items i USING (biblionumber)
WHERE datelastseen < <>
AND i.homebranch=<>
ORDER BY datelastseen DESC, i.itemcallnumber ASC
7. Accession Register Sorted by Bar-code/Accession Number
SELECT items.barcode, items.dateaccessioned, biblio.author, biblio.title, biblioitems.editionstatement, biblioitems.publishercode, biblio.copyrightdate, biblioitems.isbn, biblioitems.pages, items.itemcallnumber, items.price
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype= <- > AND items.barcode BETWEEN <
> AND <>
ORDER BY LPAD(items.barcode,30,' ') ASC
8. Complete Biblio Information List Searched by a Given Title
SELECT biblio.title AS 'Title', items.barcode AS 'Accession No.', biblio.author AS 'Author', biblioitems.editionstatement AS 'Edition', biblioitems.publishercode AS 'Publisher', biblioitems.place AS 'Publication Place', biblio.copyrightdate AS 'Publication Date', biblioitems.isbn AS 'ISBN No.', biblioitems.pages AS 'Pages', items.itemcallnumber AS 'Call Number', items.enumchron AS 'Volume', items.price AS 'Price', itemtypes.description AS 'Item Type'
FROM items
RIGHT JOIN itemtypes on (items.itype=itemtypes.itemtype)
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE biblio.title LIKE <>
ORDER BY biblio.title asc
9. List All Bibs with detail for the given author
SELECT biblio.author AS 'Author', biblio.title AS 'Title', items.barcode AS 'Accession No.', biblioitems.editionstatement AS 'Edition', biblioitems.publishercode AS 'Publisher', biblioitems.place AS 'Publication Place', biblio.copyrightdate AS 'Publication Date', biblioitems.isbn AS 'ISBN No.', biblioitems.pages AS 'Pages', items.itemcallnumber AS 'Call Number', items.enumchron AS 'Volume', items.price AS 'Price', itemtypes.description AS 'Item Type'
FROM items
RIGHT JOIN itemtypes on (items.itype=itemtypes.itemtype)
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE biblio.author LIKE <>
ORDER BY biblio.author asc
10. Count Unique item Titles
SELECT homebranch, count(DISTINCT biblionumber) AS bibs,
count(itemnumber) AS items
FROM items
GROUP BY homebranch
ORDER BY homebranch ASC
11. All checkout items with the borrower's list
SELECT issues.date_due, borrowers.surname, borrowers.firstname,
borrowers.phone, borrowers.email, biblio.title, biblio.author,
items.itemcallnumber, items.barcode, items.location
FROM issues
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
ORDER BY issues.date_due ASC
12. All Checkouts items in Date Range
select issues.issuedate, borrowers.cardnumber, borrowers.surname, biblio.title, biblio.author, items.barcode from issues
join items on(issues.itemnumber=items.itemnumber)
join biblio on(biblio.biblionumber=items.biblionumber)
join borrowers on(borrowers.borrowernumber=issues.borrowernumber)
union
select old_issues.issuedate, borrowers.cardnumber, borrowers.surname, biblio.title, biblio.author, items.barcode from old_issues
join items on(old_issues.itemnumber=items.itemnumber)
join biblio on(biblio.biblionumber=items.biblionumber)
join borrowers on(borrowers.borrowernumber=old_issues.borrowernumber)
where DATE(issuedate) BETWEEN <>
AND <>
13. Check-in Books in a Date Range
SELECT borrowers.cardnumber, borrowers.surname, items.barcode, biblio.title, biblio.author, old_issues.issuedate, old_issues.date_due, old_issues.returndate, (TO_DAYS(old_issues.returndate)-TO_DAYS(old_issues.date_due)) AS 'days overdue'
FROM borrowers
RIGHT JOIN statistics ON (borrowers.borrowernumber = statistics.borrowernumber)
LEFT JOIN old_issues ON (borrowers.borrowernumber=old_issues.borrowernumber)
LEFT JOIN items ON (old_issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (old_issues.returndate BETWEEN <>
AND <> AND type = 'return')
GROUP BY borrowers.cardnumber, old_issues.returndate
14. Top 10 Circulating Books for the last 6 months
SELECT count(s.datetime) AS circs, b.title, b.author,
i.ccode
FROM statistics s
JOIN items i ON (i.itemnumber=s.itemnumber)
LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber)
WHERE DATE(s.datetime) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH)
AND DATE(s.datetime)<=CURRENT_DATE() AND
s.itemnumber IS NOT NULL
GROUP BY b.biblionumber
ORDER BY circs DESC
LIMIT 10
15. Low Circulating Items in a specific period of time
SELECT biblio.title, biblio.author, items.barcode, items.itemcallnumber
FROM old_issues
LEFT JOIN items ON (items.itemnumber=old_issues.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
WHERE old_issues.issuedate BETWEEN <> AND <>
AND items.itype=<- >
GROUP BY old_issues.itemnumber HAVING COUNT(old_issues.issuedate) = <
>
ORDER BY biblio.title ASC
16. Barcode Search Report
SELECT CONCAT('',biblio.biblionumber,'') AS biblionumbers, items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate
FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.homebranch =<> AND items.barcode LIKE <>
ORDER BY LPAD(items.barcode,30,' ') ASC
17. List of all Patrons from a Single Branch with open Hold Requests
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, reserves.reservedate
AS 'date reserved', reserves.priority, biblio.title,
IF( LOCATE('', biblio_metadata.metadata,
LOCATE('', biblio_metadata.metadata,
LOCATE(' LOCATE(' ', biblio_metadata.metadata, LOCATE('', biblio_metadata.metadata, LOCATE('', biblio_metadata.metadata, LOCATE('', biblio_metadata.metadata,
LOCATE('', biblio_metadata.metadata,
LOCATE('> AND reserves.priority = 0
18. Top 10 Titles Placed on Hold in the Last 6 Months
SELECT count(*) AS holds, title, author, ccode
FROM (
SELECT biblio.title, biblio.author, items.ccode, biblio.biblionumber
FROM reserves
LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
LEFT JOIN items ON (biblio.biblionumber=items.biblionumber)
WHERE DATE(reserves.timestamp) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH)
AND DATE(reserves.timestamp) <=CURRENT_DATE()
UNION ALL
SELECT biblio.title, biblio.author, items.ccode, biblio.biblionumber
FROM old_reserves
LEFT JOIN biblio ON (old_reserves.biblionumber=biblio.biblionumber)
LEFT JOIN items ON (biblio.biblionumber=items.biblionumber)
WHERE DATE(old_reserves.timestamp) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH)
AND DATE(old_reserves.timestamp) <=CURRENT_DATE()
) AS myholds
GROUP BY biblionumber
ORDER BY holds DESC
LIMIT 10
19. Shows the total number of items circulated
SELECT count(*) AS total
FROM statistics
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber)
WHERE statistics.datetime BETWEEN <> AND <>
20. Overdue items List due from more than 30 Days
SELECT borrowers.surname, borrowers.firstname, borrowers.phone, borrowers.cardnumber,
borrowers.address, borrowers.city, borrowers.zipcode, issues.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', items.itype,
items.itemcallnumber, items.barcode, items.homebranch, biblio.title, biblio.author
FROM borrowers
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > '30' AND issues.branchcode = <>
ORDER BY borrowers.surname ASC, issues.date_due ASC
21. Date Wise List of Checked Out Books
SELECT DATE_FORMAT(c.issuedate, "%d %b %Y %h:%i %p") AS Issue_Date, DATE_FORMAT(c.date_due, "%d %b %Y") AS Due_Date,
i.barcode AS Barcode,
b.title AS Title,
b.author AS Author,
p.cardnumber AS Card_No,
p.firstname AS First_Name,
p.surname AS Last_Name
FROM issues c
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE c.issuedate
BETWEEN <> AND <> ORDER BY c.issuedate DESC
22. List Active Patrons since a specific date
SELECT DISTINCT surname, firstname, cardnumber, email, address,
address2, city, state, zipcode
FROM borrowers
WHERE borrowernumber IN
(SELECT borrowernumber
FROM statistics
WHERE borrowernumber = borrowernumber
AND datetime >= <>)
ORDER BY surname, firstname
23. List of patrons expired in a specific year
SELECT borrowers.surname, borrowers.firstname, borrowers.borrowernumber
FROM borrowers
WHERE YEAR(borrowers.dateexpiry) = <>
24. New Patrons by Category in Date Range
SELECT categorycode, COUNT(borrowernumber) AS 'new patrons'
FROM (SELECT borrowernumber, categorycode, dateenrolled FROM borrowers
UNION ALL
SELECT borrowernumber, categorycode, dateenrolled FROM deletedborrowers) AS patrons
WHERE dateenrolled BETWEEN <> AND <>
GROUP BY categorycode
25. List of patron's expiry date with details
SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname, p.dateexpiry
FROM borrowers p
WHERE p.dateexpiry < NOW()
ORDER BY p.dateexpiry ASC
26. List all restricted patrons
SELECT cardnumber, surname, firstname,
debarred, debarredcomment
FROM borrowers
WHERE branchcode=<