[Koha] SQL reports - Fine generating with itemtype filter

[Suresh Kumar Tejomurtula]

Dear all,

I need help in generating a report related to fines.

Using the below report iam able to generate the fines. I would like to add
a filter to this report "itemtype" for fine report as our circulation rules
are slightly different for books and journals and the complete fine reports
sometimes not exactly what iam looking for.

I tried adding this (SELECT itype FROM items t WHERE t.itype = 'BK') AS
'ITEMTYPE') and added items t at "from statement", but not working. As iam
not much aware of sql reports, requesting help in generating report with
itemtype as a filter, I would like to generate fine report on a monthly
basis with itemtypes.

_________________________________________________________________________________________________________________
SELECT
    (SELECT CONCAT('<a href=\"/cgi-bin/koha/members/
boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ',
b.firstname,'</a>')
    FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron,
    format(sum(amountoutstanding),2) AS 'Outstanding',
    (SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber =
i.borrowernumber) AS 'Checkouts'
FROM
    accountlines a, borrowers b
WHERE
    (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE
a2.borrowernumber = a.borrowernumber)  > '0.00'
    AND a.borrowernumber = b.borrowernumber
AND a.timestamp BETWEEN <<FINE Between (yyyy-mm-dd)|date>> AND <<and
(yyyy-mm-dd)|date>>
GROUP BY
    a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC
_________________________________________________________________________________________________________________


-- 
Regards
T. Suresh Kumar