On Tue, May 19, 2009 at 12:54 PM, Owen Leonard <span dir="ltr"><<a href="mailto:oleonard@myacpl.org">oleonard@myacpl.org</a>></span> wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
As a cost-saving measure my library is considering sending printed<br>
overdue notices only to patrons who have checkouts exceeding a certain<br>
value. So, if you have more than $100-worth of checkouts, you'll<br>
receive a printed notice. I'm wondering if there's a way to do this<br>
which isn't really resource-intensive.<br>
<br>
By querying only a subset of patrons (patrons with overdues falling<br>
within a date range), one could limit the number of patrons to check.<br>
Then you could loop over that set and sum the replacementprice of<br>
items checked out to each one:<br>
<br>
select sum(replacementprice) from items,issues where items.itemnumber<br>
= issues.itemnumber and issues.borrowernumber = 20375<br>
<br>
...and collect the results that exceeded our limit. Or am I not seeing<br>
a more efficient way to do it?<br></blockquote></div><br>Try:<br><br>SELECT borrowernumber, sum(replacementprice) as value<br>FROM issues <br>LEFT JOIN items USING (itemnumber)<br>WHERE replacementprice IS NOT NULL<br>
GROUP BY borrowernumber<br>HAVING value > 100<br><br>Optionally, you can put the date ranges into the WHERE clause (e.g. "AND date_due > [start_date]").<br><br>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. <br>
<br>You could add another LEFT JOIN on borrowers USING (borrowernumber) to get the patron's name, address, etc. in the same query.<br>-- <br>Joe Atzberger<br>LibLime - Open Source Library Solutions<br>