[Koha] Koha Sql Report Help

[Shafrizan Affendi]

Hi,

Recently I upgraded my Koha from ver 18 to 22.05. Some of my sql report are
not working anymore. For example to show a fine collection by staff id.
Here is my previous sql.

SELECT
     @rownum:=@rownum+1 No,
      student.borrowernumber,
      student.cardnumber AS Student_ID,
      student.surname AS Student_Name,
      format(db.amount,2) As Amount,
      accounttype,
      date(db.timestamp) as Date,
      time(db.timestamp) As Time,
      db.manager_id AS Staff_ID,
      staff.surname  AS Staff_Name,
      db.note As Nota
FROM
      (SELECT @rownum:=0) r,
      action_logs a, accountlines db
            INNER JOIN borrowers student ON db.borrowernumber =
student.borrowernumber
            INNER JOIN borrowers staff ON db.manager_id =
staff.borrowernumber
WHERE
           date(db.timestamp)  BETWEEN <<Between (YYYY-MM-dd)|date>> AND
<<and (YYYY-MM-DD|date>>
           AND module='fines' AND accounttype='pay'
GROUP BY
    db.timestamp
ORDER BY
  db.timestamp

I did read about the changes regarding table accountype but still not quite
understand. Anyone can help me to correct this sql?

Really appreciate your help

Thanks in advance