Koha Reports – SQL Reports for Koha 20.05, 20.11 & 21.05

 

Koha Reports – SQL Reports for Koha 20.05, 20.11 21.05 & 21.11

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,' ') ASC

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

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