Overdue report/count at year-end
Hello everyone, We have implemented one of the overdue reports from the Report Library and that gives us a snapshot of the overdue count at the point in time when the report is run, but is there any way to get a count (by month, for example) of the number of items that were overdue throughout a year? Thanks! Steve
Hi Steve, I think I'd start with comparing the date_due and the returndate in old_issues to figure out which items were returned late and go from there. Hope this helps, Katrin Am 01.08.2014 um 18:02 schrieb Steven Nickerson:
Hello everyone,
We have implemented one of the overdue reports from the Report Library and that gives us a snapshot of the overdue count at the point in time when the report is run, but is there any way to get a count (by month, for example) of the number of items that were overdue throughout a year?
Thanks!
Steve
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Thanks for the pointer/suggestion Katrin! With your comment and a little help Googling, I came up with the following that seems to produce the monthly count I was looking for: SELECT monthname(returndate) AS `MONTH`, COUNT(*) AS `OVERDUES` FROM old_issues WHERE returndate > date_due and returndate > "2014-01-01 00:00:00" GROUP BY month(returndate); For now I hard-coded the 1/1/2014 starting date but that could easily be made a "variable" as well and then an end date of 12/31/<year> could be added to make it runnable for any given year, not just 2014. -----Original Message----- From: Katrin Fischer [mailto:Katrin.Fischer.83@web.de] Sent: Saturday, August 02, 2014 8:13 AM To: koha@lists.katipo.co.nz Subject: Re: [Koha] Overdue report/count at year-end Hi Steve, I think I'd start with comparing the date_due and the returndate in old_issues to figure out which items were returned late and go from there. Hope this helps, Katrin Am 01.08.2014 um 18:02 schrieb Steven Nickerson:
Hello everyone,
We have implemented one of the overdue reports from the Report Library and that gives us a snapshot of the overdue count at the point in time when the report is run, but is there any way to get a count (by month, for example) of the number of items that were overdue throughout a year?
Thanks!
Steve
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
I decided to go ahead and try to implement prompting for the year inside of the Koha report and here's the SQL I came up with (enter the year twice when prompted): SELECT month(returndate) AS `MONTH`, COUNT(*) AS `OVERDUES` FROM old_issues WHERE returndate > date_due AND returndate > concat(<<START YEAR (yyyy)>>,'-01-01 00:00:00') AND returndate < concat(<<END YEAR (yyyy)>>,'-12-31 23:59:59') GROUP BY month(returndate) WITH ROLLUP; Perhaps this may be a useful report to others..."Number of overdues per month". You can actually enter a different "start year" and "end year" and it still shows the number of overdues by month...maybe you'll see some trends where patrons are consistently returning items late in one particular month??? Steve
participants (2)
-
Katrin Fischer -
Steven Nickerson