[Koha] SQL Report-Circulation-- Help

SATISH lis4satish at gmail.com
Wed Feb 22 23:05:52 NZDT 2017


With Koha 16.5/Package Installation/ Ubuntu 14.04 Desktop /

I looking for Borrower's complete transactions report between "date range"
and "by patron category" and "by itemtype".
I reason for the report is: to keep the record in hard copy for entire
semester's transactions.

I am trying following report, with date range, am successful but when added
patron category and itemtype (last two line in WHERE section), it's failing.

Can someone help me to get this report or please correct me, if I am wrong
in the query??

SELECT borrowers.cardnumber, borrowers.surname,borrowers.firstname,
borrowers.streetnumber, borrowers.categorycode,items.barcode,
DATE_FORMAT(old_issues.issuedate, "%d %b %Y %h:%i %p") As issuedate,
DATE_FORMAT(old_issues.date_due,  "%d %b %Y %h:%i %p") As date_due,
DATE_FORMAT(old_issues.lastreneweddate, "%d %b %Y %h:%i %p") As
DATE_FORMAT(old_issues.returndate, "%d %b %Y %h:%i %p") As returndate,
EXTRACTVALUE( b.marcxml, '//datafield[@tag="245"]/subfield[@code>="a"]' )
items.itype, items.location, items.ccode,items.itemcallnumber, items.price
FROM old_issues
LEFT JOIN borrowers ON ( borrowers.borrowernumber =
old_issues.borrowernumber )
LEFT JOIN items ON ( items.itemnumber = old_issues.itemnumber )
LEFT JOIN biblioitems AS b ON ( items.biblioitemnumber = b.biblioitemnumber)
WHERE p.categorycode=<<categorycode|categorycode>> AND
WHERE items.itype=<<itemtypes|itemtypes>> AND
WHERE old_issues.issuedate BETWEEN <<Between Date (yyyy-mm-dd)|date>> and
<<and (yyyy-mm-dd)|date>> ORDER BY old_issues.issuedate

With Thanks
Satish MV
Govt. Engineering College, Hassan

More information about the Koha mailing list