On Tue, May 19, 2009 at 12:54 PM, Owen Leonard <oleonard@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