SQL Report-Circulation-- Help
Hi, 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 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 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 Librarian Govt. Engineering College, Hassan Karnataka. www.gechassan.ac.in --
Hello Satish, To add conditions to a SQL query you just need to separate them with the "AND" keyword, do not repeat "WHERE". So it's: SELECT fields FROM TABLE WHERE condition_a AND condition_b AND condition_c Regards, Jonathan On Wed, 22 Feb 2017 at 11:05 SATISH <lis4satish@gmail.com> wrote:
Hi,
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 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 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 Librarian Govt. Engineering College, Hassan Karnataka. www.gechassan.ac.in -- _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
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.
Dear All, Currently i am using Koha version 3.12.10.000. and i am having two branch libraries. I am looking in SQL report to generate the books details with the following details. 1. Title of the book 2. Author 3. Publisher 4. Place of Publication and year 5. Name of the vendor 6. Invoice No and Date 7. Price 8. Withdrawn status Kindly guide to get the report. Thanks Pandu. +91-8978002982 On Wed, Feb 22, 2017 at 3:35 PM, SATISH <lis4satish@gmail.com> wrote:
Hi,
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 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 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 Librarian Govt. Engineering College, Hassan Karnataka. www.gechassan.ac.in -- _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- M.Pandu Rangaswamy H.T.Parekh Library Institute For Financial Management and Research (IFMR) 24, Kothari Road, Chennai - 600 034 INDIA Tel - 91-44-2830 3498 Mobile: 08978002982
Hi Pandu Rangaswamy, hope this sql helps your requirements --------------------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT DISTINCT b.biblionumber, b.title, b.author, t.publishercode AS publisher, t.place AS 'place of publication', t.editionstatement AS 'edition', b.copyrightdate AS 'copyright year', i.booksellerid AS 'vendor', i.stocknumber AS 'invoice number', i.price, i.withdrawn AS 'withdrawn stuatus' FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) GROUP BY b.biblionumber ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ with thanks Satish MV Librarian Govt. Engineering College, Hassan Karnataka
Currently i am using Koha version 3.12.10.000. and i am having two branch libraries.
I am looking in SQL report to generate the books details with the following details.
1. Title of the book 2. Author 3. Publisher 4. Place of Publication and year 5. Name of the vendor 6. Invoice No and Date 7. Price 8. Withdrawn status
participants (3)
-
Jonathan Druart -
pandu rangaswamy -
SATISH