[Koha] Report by value of checked-out items

[Joe Atzberger]

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

Try:

SELECT borrowernumber, sum(replacementprice) as value
FROM issues
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.
-- 
Joe Atzberger
LibLime - Open Source Library Solutions
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20090519/aeb2cd68/attachment-0001.htm