[Koha] SQL Report-Circulation-- Help

[SATISH]

Hi Jonathan,

I did a mistake with WHERE Coditions, like;
p.categorycode=<<categorycode|categorycode>> INSTEAD OF
borrowers.categorycode
(http://schema.koha-community.org/16_05/tables/old_issues.html )

and this was throwing error : Unknown column 'p.categorycode' in 'where
clause'

Now, I have corrected my query with the syntax you had mentioned, as It
works now!  Thank you, here is the corrected SQL Query.


Title: Borrower's complete transactions report between "date range" + "by
patron category" + "by item type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
lastreneweddate,
DATE_FORMAT(old_issues.returndate, "%d %b %Y %h:%i %p") As returndate,
EXTRACTVALUE( b.marcxml, '//datafield[@tag="245"]/subfield[@code>="a"]' )
AS TITLE,
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 borrowers.categorycode=<<categorycode|categorycode>> AND
items.itype=<<itemtypes|itemtypes>> AND
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
Librarian
Govt. Engineering College, Hassan
Karnataka.