[Koha] SQL Report-Circulation-- Help

[Jonathan Druart]

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 at 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 at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>