[Koha] Help regarding fine calculation report

[Suresh Kumar Tejomurtula]

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