Hi, Need help in refining the below query which is based on the query from SQL reports library. I need to add the following also to the query:
1. Using the below report Iam able to generate fine report for only returned items. I need to generate fine even for the items that are renewed and fine is generated. 2. Also in some cases the below query is not including the start date and end date. As far as I read about mysql help regarding BETWEEN function, it includes both start date and END date. *SELECT * * b.cardnumber as Emp_NO,TRIM(CONCAT( b.surname, b.firstname)) as Name, b.address as DEPT,bib.title, i.barcode as Acc_NO,DATE_FORMAT( a.timestamp, "%d %b %Y" ) as FineDate,* * a.amountoutstanding, DATE_FORMAT(ni.issuedate, "%d %b %Y" ) as Issue_date, DATE_FORMAT(ni.date_due, "%d %b %Y" ) as Due_date,* * IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate, IF ( ni.lastreneweddate IS NULL , " ", ni.lastreneweddate ) AS lastreneweddate * *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 * *DATE_FORMAT( ni.returndate, "%m" )=<<Enter Month MM>> AND DATE_FORMAT( ni.returndate, "%Y" )=<<Enter Year YYYY>>* *and* *((itype='BK' OR itype='REP' ) AND amountoutstanding >'5.00' OR (itype='JNL' OR itype='STD') AND amountoutstanding >'1.50') and returndate is not null* *ORDER BY returndate asc* If there is any other report in the reports library which helps me to generate fines report between two dates for the items that are returned/renewed, please let me know. -- Regards T. Suresh Kumar _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
