From Koha version 19.11 onwards few reports related to circulations are not working, we need to either modify the SQL statement or you can use this query for the list of all circulation transactions on a specific date range, with patron information (card number & name), transaction type and amount (if any), and item information (barcode, title & author)
SELECT
datetime AS "Date",
cardnumber AS "Card number",
surname AS "Last name",
CASE type
WHEN 'issue' THEN "Check out"
WHEN 'localuse' THEN "In house use"
WHEN 'return' THEN "Check in"
WHEN 'renew' THEN "Renew"
WHEN 'writeoff' THEN "Amnesty"
WHEN 'payment' THEN "Payment"
ELSE "Other" END
AS "Transaction",
CASE value
WHEN '0' THEN "-"
ELSE value END
AS "Amount",
barcode AS "Barcode",
biblio.title AS "Title",
author AS "Author",
items.homebranch,
items.holdingbranch
FROM statistics
JOIN borrowers ON statistics.borrowernumber= borrowers.borrowernumber
LEFT JOIN items ON statistics.itemnumber=items. itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio. biblionumber
WHERE DATE (statistics.datetime) BETWEEN <<From Date|date>> AND <<To Date|date>>
datetime AS "Date",
cardnumber AS "Card number",
surname AS "Last name",
CASE type
WHEN 'issue' THEN "Check out"
WHEN 'localuse' THEN "In house use"
WHEN 'return' THEN "Check in"
WHEN 'renew' THEN "Renew"
WHEN 'writeoff' THEN "Amnesty"
WHEN 'payment' THEN "Payment"
ELSE "Other" END
AS "Transaction",
CASE value
WHEN '0' THEN "-"
ELSE value END
AS "Amount",
barcode AS "Barcode",
biblio.title AS "Title",
author AS "Author",
items.homebranch,
items.holdingbranch
FROM statistics
JOIN borrowers ON statistics.borrowernumber=
LEFT JOIN items ON statistics.itemnumber=items.
LEFT JOIN biblio ON items.biblionumber=biblio.
WHERE DATE (statistics.datetime) BETWEEN <<From Date|date>> AND <<To Date|date>>
Courtesy
Dr. Vimal Kumar V. Reference Assistant Mahatma Gandhi University Library Kottayam, Kerala-686560
Super sir.
ReplyDelete