[Koha] Report by value of checked-out items
ohiocore at gmail.com
Wed May 20 05:30:31 NZST 2009
On Tue, May 19, 2009 at 12:54 PM, Owen Leonard <oleonard at myacpl.org> wrote:
> As a cost-saving measure my library is considering sending printed
> overdue notices only to patrons who have checkouts exceeding a certain
> value. So, if you have more than $100-worth of checkouts, you'll
> receive a printed notice. I'm wondering if there's a way to do this
> which isn't really resource-intensive.
> By querying only a subset of patrons (patrons with overdues falling
> within a date range), one could limit the number of patrons to check.
> Then you could loop over that set and sum the replacementprice of
> items checked out to each one:
> select sum(replacementprice) from items,issues where items.itemnumber
> = issues.itemnumber and issues.borrowernumber = 20375
> ...and collect the results that exceeded our limit. Or am I not seeing
> a more efficient way to do it?
SELECT borrowernumber, sum(replacementprice) as value
LEFT JOIN items USING (itemnumber)
WHERE replacementprice IS NOT NULL
GROUP BY borrowernumber
HAVING value > 100
Optionally, you can put the date ranges into the WHERE clause (e.g. "AND
date_due > [start_date]").
Performance is moderately intensive, since we do a lot of sum adding only to
later filter out most of them with the HAVING clause, but there's no way
around it. And it's more efficient than the 2-pass approach you described.
I get through a 10,000 row issues table in about 0.2 seconds.
You could add another LEFT JOIN on borrowers USING (borrowernumber) to get
the patron's name, address, etc. in the same query.
LibLime - Open Source Library Solutions
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Koha