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 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 <<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


References:-https://wiki.koha-community.org/wiki/SQL_Reports_Library

No comments

Powered by Blogger.