LINUX, FOSS AND LIBRARY TECHNOLOGY ENTHUSIAST

Thursday, August 5, 2021

All Circulation Transactions on Date Range with Patron & Item Details

1 comments


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>>


Courtesy 

Dr. Vimal Kumar V. Reference Assistant Mahatma Gandhi University Library Kottayam, Kerala-686560

1 comment: