SQL REPORTS
1. Accession Register:
SELECT
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)
ORDER BY
items.barcode ASC;
2. Accession Number Sorted by Barcode Number:
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>>
ORDER BY
LPAD(items.barcode, 40, ' ') ASC;
3. Accession Register with Keyword/Subject:
SELECT
items.barcode,
items.dateaccessioned,
items.itemcallnumber,
biblio.author,
biblio.title,
ExtractValue(metadata, '//datafield[@tag="650"]/subfield[@code="a"]') AS Keyword,
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)
JOIN
biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
ORDER BY
LPAD(items.barcode, 40, ' ') ASC;
4. Accession Register with Price:
SELECT
items.barcode,
items.dateaccessioned,
items.itemcallnumber,
biblioitems.isbn,
biblio.author,
biblio.title,
biblioitems.pages,
biblioitems.publishercode,
biblioitems.place,
biblio.copyrightdate,
items.price
FROM
items
LEFT JOIN
biblioitems ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
LEFT JOIN
biblio ON (biblioitems.biblionumber = biblio.biblionumber)
ORDER BY
items.barcode ASC;
5. Accession/Barcode Number Search:
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;
6. Catalogue by Itemtype:
SELECT
COALESCE(homebranch, '*GRAND TOTAL*') AS homebranch,
IFNULL(itype, '') AS itype,
COUNT(itype) AS count
FROM
items
WHERE
dateaccessioned < <<Added before (yyyy-mm-dd)|date>>
GROUP BY
homebranch, itype
WITH ROLLUP;
7. Check In List of Books (Date Wise):
SELECT
old_issues.returndate,
items.barcode,
biblio.title,
biblio.author,
borrowers.firstname,
borrowers.surname,
borrowers.cardnumber,
borrowers.categorycode
FROM
old_issues
LEFT JOIN
borrowers 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 <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY
old_issues.returndate DESC;
8. Circulation Report by Date:
SELECT
issues.issuedate,
items.barcode,
biblio.title,
author,
borrowers.firstname,
borrowers.surname
FROM
issues
LEFT JOIN
borrowers ON borrowers.borrowernumber = issues.borrowernumber
LEFT JOIN
items ON issues.itemnumber = items.itemnumber
LEFT JOIN
biblio ON items.biblionumber = biblio.biblionumber
WHERE
issues.issuedate BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY
issues.issuedate;
9. Circulation - All Checked Out Books:
SELECT
issues.issuedate,
issues.date_due,
borrowers.categorycode,
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)
WHERE
issues.issuedate BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY
issues.issuedate;
10. Date Wise List of Books:
SELECT
items.dateaccessioned,
items.barcode,
items.itemcallnumber,
biblio.author,
biblio.title,
biblioitems.publishercode
FROM
items
LEFT JOIN
biblioitems ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
LEFT JOIN
biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE
items.dateaccessioned BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY
items.barcode DESC;
11. 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;
12. Duplicate Titles (Using Title and ISBN):
SELECT
GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers,
b.title,
b.author,
GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns
FROM
biblio b
LEFT JOIN
biblioitems i ON (i.biblionumber = b.biblionumber)
GROUP BY
CONCAT(substr(b.title, 0, 9), "/", i.isbn)
HAVING
COUNT(CONCAT(substr(b.title, 0, 9), "/", i.isbn)) > 1;
13. Exporting of Checkout Entries:
SELECT
issues.issuedate,
borrowers.cardnumber,
items.barcode
FROM
issues
LEFT JOIN
borrowers ON borrowers.borrowernumber = issues.borrowernumber
LEFT JOIN
items ON issues.itemnumber = items.itemnumber
LEFT JOIN
biblio ON items.biblionumber = biblio.biblionumber
ORDER BY
issues.issuedate DESC;
14. Highest Number of Book's Readers:
SELECT
cardnumber AS 'Lib Card No',
surname AS Name,
borrowernotes AS Department,
COUNT(*) AS Reading
FROM
borrowers b
JOIN
statistics s ON (b.borrowernumber = s.borrowernumber)
WHERE
DATE(datetime) BETWEEN <<From|date>> AND <<Until|date>>
GROUP BY
b.borrowernumber
ORDER BY
Reading DESC;
15. Items Currently Checked Out:
SELECT
issues.issuedate,
issues.date_due,
items.barcode,
biblio.title,
author,
borrowers.firstname,
borrowers.surname,
borrowers.cardnumber
FROM
issues
LEFT JOIN
borrowers ON borrowers.borrowernumber = issues.borrowernumber
LEFT JOIN
items ON issues.itemnumber = items.itemnumber
LEFT JOIN
biblio ON items.biblionumber = biblio.biblionumber
ORDER BY
issues.issuedate DESC;
16. Items with List of Collection Code:
SELECT
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 = 'SMWCTE' AND items.ccode LIKE 'FIC';
17. List of Serials:
SELECT
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">', biblio.biblionumber, '</a>') AS biblionumber,
CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=', subscription.subscriptionid, '\">', subscription.subscriptionid, '</a>') AS subscriptionid,
biblio.title,
ExtractValue(metadata, '//datafield[@tag="022"]/subfield[@code="a"]') AS ISSN,
GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings
FROM
subscription
JOIN
biblio ON (subscription.biblionumber = biblio.biblionumber)
JOIN
biblio_metadata ON (subscription.biblionumber = biblio_metadata.biblionumber)
LEFT JOIN
serial ON (subscription.subscriptionid = serial.subscriptionid)
WHERE
(subscription.closed = 0)
GROUP BY
biblio.biblionumber
ORDER BY
biblio.title;
18. Null Barcodes:
SELECT
items.dateaccessioned,
items.ccode,
items.itemcallnumber,
items.itype,
biblio.author,
biblio.title,
biblio.copyrightdate
FROM
biblio
JOIN
items USING(biblionumber)
WHERE
(items.barcode IS NULL OR items.barcode = '');
19. Overdue List:
SELECT
borrowers.surname,
borrowers.firstname,
issues.date_due,
(TO_DAYS(curdate()) - TO_DAYS(date_due)) AS 'days overdue',
items.itemcallnumber,
items.barcode,
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
ORDER BY
borrowers.surname ASC,
issues.date_due ASC;
20. Patron Full List:
SELECT
borrowers.borrowernumber, borrowers.cardnumber, borrowers.surname, borrowers.firstname,
borrowers.title, borrowers.othernames, borrowers.initials, borrowers.streetnumber,
borrowers.streettype, borrowers.address, borrowers.address2, borrowers.city,
borrowers.state, borrowers.zipcode, borrowers.country, borrowers.email, borrowers.phone,
borrowers.mobile, borrowers.fax, borrowers.emailpro, borrowers.phonepro,
borrowers.B_streetnumber, borrowers.B_streettype, borrowers.B_address, borrowers.B_address2,
borrowers.B_city, borrowers.B_state, borrowers.B_zipcode, borrowers.B_country,
borrowers.B_email, borrowers.B_phone, borrowers.dateofbirth, borrowers.branchcode,
borrowers.categorycode, borrowers.dateenrolled, borrowers.dateexpiry, borrowers.date_renewed,
borrowers.gonenoaddress, borrowers.lost, borrowers.debarred, borrowers.debarredcomment,
borrowers.contactname, borrowers.contactfirstname, borrowers.contacttitle,
borrowers.borrowernotes, borrowers.relationship, borrowers.sex, borrowers.password,
borrowers.flags, borrowers.userid, borrowers.opacnote, borrowers.contactnote,
borrowers.sort1, borrowers.sort2, borrowers.altcontactfirstname, borrowers.altcontactsurname,
borrowers.altcontactaddress1, borrowers.altcontactaddress2, borrowers.altcontactaddress3,
borrowers.altcontactstate, borrowers.altcontactzipcode, borrowers.altcontactcountry,
borrowers.altcontactphone, borrowers.smsalertnumber, borrowers.sms_provider_id,
borrowers.privacy, borrowers.privacy_guarantor_fines, borrowers.privacy_guarantor_checkouts,
borrowers.checkprevcheckout, borrowers.updated_on, borrowers.lastseen, borrowers.lang,
borrowers.login_attempts, borrowers.overdrive_auth_token, borrowers.anonymized,
borrowers.autorenew_checkouts
FROM
borrowers;
21. Patron List by Category:
SELECT
borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.initials,
borrowers.dateenrolled
FROM
borrowers
WHERE
branchcode = <<Enter patrons library|branches>> AND categorycode LIKE <<Enter Category borrowers|categorycode>>;
22. Patron with Fine:
SELECT
CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=', b.borrowernumber, '\">', b.surname, ', ', b.firstname, '</a>') AS Patron,
FORMAT(SUM(amountoutstanding), 2) AS 'Outstanding',
(SELECT COUNT(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'
FROM
accountlines a, borrowers b
WHERE
(SELECT SUM(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber) > '0.00'
AND a.borrowernumber = b.borrowernumber
GROUP BY
a.borrowernumber
ORDER BY
b.surname, b.firstname, Outstanding ASC;
23. Patron with Fine Date Range:
SELECT
datetime AS "Date",
cardnumber AS "Card number",
categorycode AS "Category code",
surname AS "Last name",
firstname AS "First name",
CASE type
WHEN 'issue' THEN "Check out"
WHEN 'localuse' THEN "In house use"
WHEN 'return' THEN "Check in"
WHEN 'renew' THEN "Renew"
WHEN 'writeoff' THEN "Amnesty"
WHEN 'payment' THEN "Payment"
ELSE "Other"
END AS "Transaction",
CASE value
WHEN '0' THEN "-"
ELSE value
END AS "Amount",
barcode AS "Barcode",
biblio.title AS "Title",
author AS "Author",
items.homebranch,
items.holdingbranch
FROM
statistics
JOIN
borrowers ON statistics.borrowernumber = borrowers.borrowernumber
LEFT JOIN
items ON statistics.itemnumber = items.itemnumber
LEFT JOIN
biblio ON items.biblionumber = biblio.biblionumber
WHERE
DATE(statistics.datetime) BETWEEN <<From Date|date>> AND <<To Date|date>>;
24. Records Without Items:
SELECT
b.title AS Title,
CONCAT('<a href=\"', IF(CHAR_LENGTH(systempreferences.value), CONCAT('http://', systempreferences.value), ''), '/cgi-bin/koha/opac-detail.pl?biblionumber=', b.biblionumber, '\">', b.biblionumber, '</a>') AS OPAC,
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', b.biblionumber, '\">', b.biblionumber, '</a>') AS Edit
FROM
systempreferences, biblio AS b
LEFT JOIN
items AS i ON b.biblionumber = i.biblionumber
WHERE
i.itemnumber IS NULL AND systempreferences.variable = 'OPACBaseURL';
25. Report Between Accession Numbers:
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 BETWEEN <<From Acc. No.>> AND <<To Acc. No.)>>
ORDER BY
LPAD(items.barcode, 30, ' ') ASC;
26. Report with Volumes 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;
27. Total Fines & Fees, Payments Made, Outstanding, Written Off, and Forgiven Amounts Between a Specified Date Range:
SELECT
T1.*, T2.'Total Fines/Fees', T3.'Total O/S', T4.'Paid / Credited', T5.'Written off', T6.'Forgiven'
FROM
(SELECT (@FromDate:=<<From date|date>>) AS 'From (y-m-d)', (@ToDate:=<<To date|date>>) AS 'To (y-m-d)') AS T1,
(SELECT IFNULL(ROUND(SUM(accountlines.amount), 2), "0.00") AS 'Total Fines/Fees'
FROM accountlines
WHERE accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T2,
(SELECT IFNULL(ROUND(SUM(accountlines.amountoutstanding), 2), "0.00") AS 'Total O/S'
FROM accountlines
WHERE accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T3,
(SELECT IFNULL(REPLACE(ROUND(SUM(amount), 2), "-", ""), "0.00") AS 'Paid / Credited'
FROM accountlines
WHERE accounttype IN ('PAY', 'C') AND description NOT LIKE "%Reversed%" AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T4,
(SELECT IFNULL(REPLACE(ROUND(SUM(amount), 2), "-", ""), "0.00") AS 'Written off'
FROM accountlines
WHERE accounttype='W' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T5,
(SELECT IFNULL(REPLACE(ROUND(SUM(amount), 2), "-", ""), "0.00") AS 'Forgiven'
FROM accountlines
WHERE accounttype='FOR' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T6;
27. Total Price of Books:
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;
28. Total Record Count:
SELECT
COUNT(biblionumber) AS Count
FROM
biblio;
29. Total Stock:
SELECT
items.barcode, items.itemcallnumber, biblio.title
FROM
items
LEFT JOIN
biblioitems ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
LEFT JOIN
biblio ON (biblioitems.biblionumber = biblio.biblionumber);
30. Report for Creating Spine Labels:
SELECT
ExtractValue(metadata, '//datafield[@tag="082"]/subfield[@code="a"]') AS ClassNo,
ExtractValue(metadata, '//datafield[@tag="082"]/subfield[@code="b"]') AS BookNo,
items.barcode
FROM
items
LEFT JOIN
biblioitems ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
LEFT JOIN
biblio ON (biblioitems.biblionumber = biblio.biblionumber)
JOIN
biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
WHERE
items.dateaccessioned; -- Specify condition for dateaccessioned
31. Date-Wise Catalogued Books:
SELECT
items.dateaccessioned, items.barcode, items.itemcallnumber,
biblio.author, biblio.title, biblioitems.publishercode
FROM
items
LEFT JOIN
biblioitems ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
LEFT JOIN
biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE
items.dateaccessioned BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY
items.barcode DESC;
32. Title-Wise Report (Unique):
SELECT
bi.isbn, b.author, b.title, bi.editionstatement, bi.place, bi.publishercode,
b.copyrightdate, bi.pages
FROM
items AS i
JOIN
biblio AS b ON i.biblionumber = b.biblionumber
JOIN
biblioitems AS bi ON i.biblionumber = bi.biblionumber
GROUP BY
i.homebranch, b.biblionumber
ORDER BY
b.title ASC;
