27/08/2020
Koha Reports – SQL Reports for Koha 20.05, 20.11 & 21.05
Koha Reports: You can easily add customized SQL reports in Koha Report Module.
Accession Register – Sorted by Barcode
SELECT CONCAT('',biblio.biblionumber,'') AS biblionumbers,
items.barcode, 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 LPAD
(items.barcode,40,' ') ASCDetailed Accession Register
SELECT oo.dateaccessioned AS 'Date', oo.barcode AS 'Acc. No./Barcode', ooo.title AS 'Title',
ooo.author AS 'Author/Editor', concat_ws(' , ', o.editionstatement, oo.enumchron)
AS 'Edition/Vol.', concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher',
ooo.copyrightdate AS 'Year', o.pages AS 'Pages', ooooooo.name AS 'Vendor/Source',
oo.itemcallnumber AS 'Full Call Number', concat_ws(', ?',
concat(' ', ooooo.symbol, oooo.listprice)) AS 'Cost/Price',
concat_ws(' , ', oooooo.invoicenumber, oooooo.shipmentdate) AS 'Bill No. & Date',
'' AS 'Withdrawn Date', '' AS 'Remarks' FROM biblioitems o
LEFT JOIN items oo ON oo.biblioitemnumber=o.biblioitemnumber
LEFT JOIN biblio ooo ON ooo.biblionumber=o.biblionumber
LEFT JOIN aqorders oooo ON oooo.biblionumber=o.biblionumber
LEFT JOIN currency ooooo ON ooooo.currency=oooo.currency
LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid=oo.booksellerid
LEFT JOIN aqbooksellers ooooooo ON ooooooo.id=oo.booksellerid ORDER BY oo.barcode ASC
Catalogue-List of New Items
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 DESC
Items Currently Checked-out
SELECT issues.issuedate,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
All Circulation Transactions on Date Range with Patron & Item Details
SELECT datetime AS "Date", cardnumber AS "Card number", 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 <> AND <>
Fines with Patron & Item Info
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
Patron List by Category
SELECT borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.initials,
borrowers.dateenrolled FROM borrowers WHERE branchcode=<>
AND categorycode LIKE <>
All Patron List
SELECT cardnumber, title,firstname, surname, streetnumber,
streettype, address, address2, city, state, zipcode, country,
email, phone, mobile, emailpro, phonepro, dateofbirth,
branchcode, categorycode, dateenrolled, dateexpiry, sex as gender,
userid, opacnote, sort1, sort2, smsalertnumber
FROM borrowers
ORDER BY firstname ASCFine Payment Details (Date Range)
SELECT CONCAT("View Transaction") AS 'Click
to view', T2.cardnumber AS 'Card No.'
, CONCAT(T2.firstname, " ", T2.surname) AS 'Name'
, T3.description AS 'Category'
, CONCAT(SUBSTRING(MONTHNAME(T1.timestamp), 1,3), " ", YEAR(T1.timestamp))
AS 'Billing Period'
, DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date'
, CONCAT("₹", LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid'
FROM
`accountlines` T1
LEFT JOIN borrowers T2 USING (borrowernumber)
LEFT JOIN categories T3 USING (categorycode)
WHERE
T1.accounttype="PAY"
AND
DATE(T1.timestamp) BETWEEN <> AND <>
ORDER BY DATE(T1.timestamp), CONCAT(T2.firstname, " ", T2.surname)Fine Payment Details (Date Range)
SELECT borrowers.borrowernumber, borrowers.cardnumber, accountlines.amount,
accountlines.date FROM accountlines, borrowers WHERE
borrowers.borrowernumber = accountlines.borrowernumber and
accounttype = 'pay' AND date BETWEEN <>
AND <>
Unique Titles Count
SELECT homebranch, count(DISTINCT biblionumber) AS bibs, count(itemnumber) AS items
FROM items GROUP BY homebranch ORDER BY homebranch ASC
Total Fines Paid Today
SELECT SUM(amount) FROM accountlines WHERE DATE(timestamp)=CURDATE()
AND (accounttype='PAY' OR accounttype='C')
Subject-wise Title Count Report
SELECT ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]') AS 'Subject' ,
COUNT(ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]')) AS 'No. of Titles',
COUNT(itemnumber) AS 'No. of copies' FROM biblio_metadata LEFT JOIN items USING (biblionumber)
GROUP BY ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]')
ORDER BY ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]')
Count of Items Added by Cataloger*
SELECT concat(p.firstname, ' ', p.surname) AS Name, concat(a.action, ' ', a.info) AS action,
count(a.timestamp) AS count FROM action_logs a LEFT JOIN borrowers p ON (a.user=p.borrowernumber)
WHERE a.module='CATALOGUING' AND a.timestamp BETWEEN <> AND <> AND a.info IN ('item','biblio') GROUP BY p.borrowernumber, concat(a.action, ' ', a.info)
Total Views: 2696