Koha SQL Reports
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 <<Collected BETWEEN (yyyy-mm-dd)>> AND <<and (yyyyy-mm-dd)>>
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 >= <<Has activity since (YYYY-MM-DD)>>)
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 <<Between (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>>
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 < <<Last seen before (yyyy-mm-dd)>>
AND i.homebranch=<<Home branch|branches>>
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= <<Item type code|itemtypes>> AND items.barcode BETWEEN <<Barcode between>> AND <<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 <<Enter Book title (Use % For Wildcard)>>
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 <<Enter Author (Use % For Wildcard)>>
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 <<Between (yyyy-mm-dd) |date>>
AND <<and (yyyy-mm-dd)|date>>
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 <<Checked in BETWEEN (yyyy-mm-dd)>>
AND <<and (yyyy-mm-dd)>> 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
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 <<Between (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>>
AND items.itype=<<Item Type Code>>
GROUP BY old_issues.itemnumber HAVING COUNT(old_issues.issuedate) = <<Total Issues>>
ORDER BY biblio.title ASC
16. Barcode Search Report
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') 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 =<<Branch|branches>> AND items.barcode LIKE <<Enter Barcode>>
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('<datafield tag="020"', biblio_metadata.metadata) = 0 OR LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="020"', biblio_metadata.metadata)) = 0 OR LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="020"', biblio_metadata.metadata)) > LOCATE('</datafield>', biblio_metadata.metadata, LOCATE('<datafield tag="020"', biblio_metadata.metadata)), '',
SUBSTRING( biblio_metadata.metadata,
LOCATE('<subfield code="a">', biblio_metadata.metadata, LOCATE('<datafield tag="020"', biblio_metadata.metadata)) + 19,
LOCATE('</subfield>', biblio_metadata.metadata, LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="020"', biblio_metadata.metadata)) + 19) -(LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="020"', biblio_metadata.metadata)) + 19)))
AS ISBN FROM reserves, borrowers, biblio, biblio_metadata WHERE reserves.borrowernumber = borrowers.borrowernumber
AND reserves.biblionumber = biblio.biblionumber AND reserves.biblionumber = biblio_metadata.biblionumber
AND reserves.branchcode = <<Enter Brachcode>> 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 <<Between (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>>
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 = <<Issuing branch|branches>>
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 <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 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 >= <<Has activity since (YYYY-MM-DD)>>)
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) = <<Year>>
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 <<Added BETWEEN (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>>
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=<<Select your branch|branches>> AND debarred IS NOT NULL
ORDER BY surname ASC, firstname ASC
27. Online Purchase Suggestions for items
SELECT suggestions.title AS Title, suggestions.author AS Author, suggestions.copyrightdate AS Year, suggestions.isbn AS ISBN, suggestions.publishercode AS Publisher, suggestions.place AS 'Publication Place', suggestions.itemtype AS 'Type of Book', suggestions.note AS Notes, borrowers.surname AS 'Recommended By', suggestions.suggesteddate AS 'Suggestion Date', suggestions.STATUS
FROM suggestions
LEFT JOIN borrowers ON (suggestions.suggestedby=borrowers.borrowernumber)
28. Complete Call No. Wise Shelf list
SELECT items.price,items.replacementprice,biblio.title,biblio.author,items.itemcallnumber
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.homebranch=<<Home branch|branches>>
ORDER BY items.itemcallnumber ASC
29. Suggestions List of Items for final approval
SELECT suggestions.title AS Title, suggestions.author AS Author, suggestions.copyrightdate AS Year, suggestions.isbn AS ISBN, suggestions.publishercode AS Publisher, suggestions.place AS 'Publication Place', suggestions.itemtype AS 'Type of Book', suggestions.note AS Notes, suggestions.price AS 'List Price', suggestions.quantity AS 'Required no. of Copies', suggestions.total AS 'Total Price', borrowers.surname AS 'Recommended By'
FROM suggestions
LEFT JOIN borrowers ON (suggestions.suggestedby=borrowers.borrowernumber)
WHERE suggestions.STATUS LIKE 'ACCEPTED'
30. Missing Email Patrons List
SELECT cardnumber, surname, firstname, branchcode, debarred, dateexpiry
FROM borrowers
WHERE ' ' IN (email)
31. Patrons Without Image
SELECT cardnumber, borrowernumber, surname, firstname FROM borrowers
WHERE borrowernumber
NOT IN (SELECT borrowernumber FROM patronimage)
32. Shows the total serial received during the month
SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate,
IF( LOCATE('<datafield tag="310"', biblio_metadata.metadata) = 0 OR LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="310"', biblio_metadata.metadata)) = 0 OR LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="310"', biblio_metadata.metadata)) > LOCATE('</datafield>', biblio_metadata.metadata, LOCATE('<datafield tag="310"', biblio_metadata.metadata)), '',
SUBSTRING( biblio_metadata.metadata,
LOCATE('<subfield code="a">', biblio_metadata.metadata, LOCATE('<datafield tag="310"', biblio_metadata.metadata)) + 19,
LOCATE('</subfield>', biblio_metadata.metadata, LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="310"', biblio_metadata.metadata)) + 19) -(LOCATE('<subfield code="a">', biblio_metadata.metadata,
LOCATE('<datafield tag="310"', biblio_metadata.metadata)) + 19)))
AS FREQUENCY FROM serial, biblio,biblio_metadata
WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblio_metadata.biblionumber AND MONTH(planneddate) = 03 AND YEAR(planneddate)= 2011 AND (STATUS)=2
ORDER BY serial.subscriptionid ASC
33.Title wise list of items (Dropdown item type)
SELECT count(items.biblionumber), items.biblionumber,biblio.title,biblio.subtitle,biblio.author,biblioitems.editionstatement,biblioitems.publishercode,items.itype FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itype=<<itype|itemtypes>> GROUP BY items.biblionumber ORDER BY biblio.title asc
34. Lost Item List
Title wise list of items (Dropdown item type)
SELECT count(items.biblionumber), items.biblionumber,biblio.title,biblio.subtitle,biblio.author,biblioitems.editionstatement,biblioitems.publishercode,items.itype FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itype=<<itype|itemtypes>> GROUP BY items.biblionumber ORDER BY biblio.title asc
35. Withdrawn Item list
SELECT biblio.title,biblio.author,items.itemcallnumber,items.barcode,items.datelastborrowed, items.withdrawn
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.withdrawn != 0
ORDER BY biblio.title ASC
36. Count volume and titles
SELECT homebranch, items.itype, itemtypes.description, count(DISTINCT items.biblionumber) AS bibs,
count(items.itemnumber) AS items
FROM items, itemtypes
WHERE items.itype=itemtypes.itemtype AND items.barcode IS NOT NULL
GROUP BY items.itype
ORDER BY itemtypes.description
Refer:
Comments
Post a Comment