<font size="2"><font face="verdana,sans-serif">Susan,<br><br>I don't think there is an easy way to include both the "items" and "deleteditems" table in the same query. Since you are using a "left join" in the query, the results should include a few blank values. These blank values represent items without collection codes or deleted items.<br clear="all">
</font></font><br>Bev<br><br>* * * * <br>Beverly Church <br>PTFS Project Manager<br><br>phone: 1-888-564-2457 ext. 717 <br>email: <a href="mailto:bchurch@ptfs.com" target="_blank">bchurch@ptfs.com</a><br>skype: beverlychurch<br>
<br>
<br><br><div class="gmail_quote">2010/5/12 Susan Bennett <span dir="ltr"><<a href="mailto:susan.bennett@geaugalibrary.info" target="_blank">susan.bennett@geaugalibrary.info</a>></span><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
This explains some of the results I've been getting for month end circ reports! Any suggestions on how to modify the following SQL to query items and deleteditems? What would the JOIN be?<br><br>SELECT items.ccode AS Collection, COUNT( statistics.itemnumber ) AS Count, Branch AS BRANCH FROM statistics LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) WHERE statistics.type IN ('issue') AND year(statistics.datetime) = '__YEAR__' and month(statistics.datetime) = '__MONTH__' GROUP BY branch, items.ccode<br>
<br>Any help would be appreciation!<br><br clear="all">Susan Bennett<br>ILS System Administrator<br>Geauga County Public Library<br>440 286-6811 x 125<br>440 286-7419 FAX<br>
<br><br><div class="gmail_quote">On Wed, May 12, 2010 at 2:20 PM, Cab Vinton <span dir="ltr"><<a href="mailto:bibliwho@gmail.com" target="_blank">bibliwho@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <<a href="mailto:oleonard@myacpl.org" target="_blank">oleonard@myacpl.org</a>> wrote:<br>
<br>
> What you'll need to remember when tracking those statistics is that if<br>
> you want to pull information about those deleted items along with the<br>
> statistical data (e.g. how many DVDs you circulated in 2006), you'll<br>
> have to query both the items table *and* the deleteditems table.<br>
<br>
</div>Oof. That doesn't sound like much fun. So we might end up having to<br>
modify a slew of different reports.<br>
<br>
Sounds like we're better off using the WITHDRAWN, LOST, etc. item statuses ...<br>
<br>
Thank you, Owen.<br>
<div><br>
Cab Vinton, Director<br>
Sanbornton Public Library<br>
Sanbornton, NH<br>
_______________________________________________<br>
</div><div><div></div><div>Koha mailing list<br>
<a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
</div></div></blockquote></div><br>
<br>_______________________________________________<br>
Koha mailing list<br>
<a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
<br></blockquote></div><br>