Assuming you’re running 19.11 or later, accounttype got changed to credit_type_code and debit_type_code. SELECT CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber= <http://boraccount.pl/?borrowernumber=>", T1.borrowernumber, "\" target=\"_blank\">View Transaction</a>") AS 'Click to view' , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date' , T2.cardnumber AS 'Lib Card No.' , CONCAT(T2.surname) AS 'Students Name' , T3.description AS 'Category' , T2.borrowernotes AS 'Dapartment' , CONCAT(LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid Amount' FROM accountlines T1 LEFT JOIN borrowers T2 USING (borrowernumber) LEFT JOIN categories T3 USING (categorycode) WHERE T1.credit_type_code="PAYMENT" AND DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>> ORDER BY DATE(T1.timestamp) That should give you the results you need - for more information on this change, I suggest Bywater’s blog post about it: https://bywatersolutions.com/education/the-accountlines-table-in-19-11 <https://bywatersolutions.com/education/the-accountlines-table-in-19-11> Best, Marti Fuerst Systems Librarian Huntsville-Madison County Public Library 915 Monroe St, Huntsville, AL 35801 http://hmcpl.org/ — mfuerst@hmcpl.org
On Jan 17, 2021, at 6:06 PM, Elaine Bradtke <eb@efdss.org> wrote:
I'm a cut and paste report writer, I think you need someone who really understands SQL. Forwarding to the list. Elaine VWML <https://vwml.org>
---------- Forwarded message --------- From: MAMCET Library <mamcet.library10@gmail.com> Date: Sat, Jan 16, 2021 at 7:30 PM Subject: Re: [Koha] SQL Report Coding need. To: Elaine Bradtke <eb@efdss.org>
This report genereted but not out put ples help. Koha version 20.4
This report run Error in "T1.accounttype "
SELECT CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber= <http://boraccount.pl/?borrowernumber=>", T1.borrowernumber, "\" target=\"_blank\">View Transaction</a>") AS 'Click to view' , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date' , T2.cardnumber AS 'Lib Card No.' , CONCAT(T2.surname) AS 'Students Name' , T3.description AS 'Category' , T2.borrowernotes AS 'Dapartment' , CONCAT(LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid Amount' FROM `accountlines` T1 LEFT JOIN borrowers T2 USING (borrowernumber) LEFT JOIN categories T3 USING (categorycode) WHERE T1.accounttype="PAY" AND DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>> ORDER BY DATE(T1.timestamp)
On Wed, 13 Jan 2021, 11:49 pm Elaine Bradtke, <eb@efdss.org> wrote:
Do any of the reports here come close to what you need? https://wiki.koha-community.org/wiki/SQL_Reports_Patrons Elaine VWML <https://vwml.org>
On Tue, Jan 12, 2021 at 10:28 PM MAMCET Library < mamcet.library10@gmail.com> wrote:
Dear all good morning Please send the SQL Patrons Fine payment date wise report coding. Koha version 20.5
-- Thanking you..
Librarian, MAMCET <http://mamcet.com/> Trichy-Chennai Trunk Road, Siruganur, Tiruchirappalli-621 105. _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha