Thanks Bev and Chris! I'll see what I can work out.<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 Thu, May 13, 2010 at 5:05 PM, Chris Cormack <span dir="ltr"><<a href="mailto:chrisc@catalyst.net.nz">chrisc@catalyst.net.nz</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 class="im">* Church, Beverly (<a href="mailto:bchurch@ptfs.com">bchurch@ptfs.com</a>) wrote:<br>
</div><div class="im">> Susan,<br>
><br>
> I don't think there is an easy way to include both the "items" and<br>
> "deleteditems" table in the same query. Since you are using a "left join"<br>
> in the query, the results should include a few blank values. These blank<br>
> values represent items without collection codes or deleted items.<br>
> Bev<br>
<br>
</div>Actually this is where the UNION syntax comes in handy.<br>
You can do something like this<br>
<div class="im"><br>
SELECT items.ccode AS Collection, COUNT( statistics.itemnumber ) AS<br>
Count, Branch AS BRANCH FROM statistics LEFT JOIN items ON<br>
(statistics.itemnumber = items.itemnumber) WHERE statistics.type IN<br>
('issue') AND year(statistics.datetime) = '__YEAR__' and<br>
month(statistics.datetime) = '__MONTH__' GROUP BY branch,items.ccode<br>
</div>UNION<br>
SELECT deleteditems.ccode AS Collection, COUNT( statistics.itemnumber )<br>
AS Count, Branch AS BRANCH FROM statistics LEFT JOIN deleteditems ON<br>
(statistics.itemnumber = deleteditems.itemnumber) WHERE statistics.type<br>
<div class="im">IN ('issue') AND year(statistics.datetime) = '__YEAR__' and<br>
</div>month(statistics.datetime) = '__MONTH__' GROUP BY branch, deleteditems.ccode;<br>
<br>
Which will give the union of the 2 queries result sets<br>
<br>
Hope this helps<br>
<br>
Chris<br>
<br>
><br>
> * * * *<br>
<div><div></div><div class="h5">> Beverly Church<br>
> PTFS Project Manager<br>
><br>
> phone: 1-888-564-2457 ext. 717<br>
> email: <a href="mailto:bchurch@ptfs.com">bchurch@ptfs.com</a><br>
> skype: beverlychurch<br>
><br>
> 2010/5/12 Susan Bennett <<a href="mailto:susan.bennett@geaugalibrary.info">susan.bennett@geaugalibrary.info</a>><br>
><br>
> This explains some of the results I've been getting for month end circ<br>
> reports! Any suggestions on how to modify the following SQL to query<br>
> items and deleteditems? What would the JOIN be?<br>
><br>
> SELECT items.ccode AS Collection, COUNT( statistics.itemnumber ) AS<br>
> Count, Branch AS BRANCH FROM statistics LEFT JOIN items ON<br>
> (statistics.itemnumber = items.itemnumber) WHERE statistics.type IN<br>
> ('issue') AND year(statistics.datetime) = '__YEAR__' and<br>
> month(statistics.datetime) = '__MONTH__' GROUP BY branch, items.ccode<br>
><br>
> Any help would be appreciation!<br>
><br>
> Susan Bennett<br>
> ILS System Administrator<br>
> Geauga County Public Library<br>
> 440 286-6811 x 125<br>
> 440 286-7419 FAX<br>
><br>
> On Wed, May 12, 2010 at 2:20 PM, Cab Vinton <<a href="mailto:bibliwho@gmail.com">bibliwho@gmail.com</a>> wrote:<br>
><br>
> On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <<a href="mailto:oleonard@myacpl.org">oleonard@myacpl.org</a>><br>
> wrote:<br>
><br>
> > What you'll need to remember when tracking those statistics is that<br>
> if<br>
> > you want to pull information about those deleted items along with<br>
> 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>
> 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<br>
> statuses ...<br>
><br>
> Thank you, Owen.<br>
> Cab Vinton, Director<br>
> Sanbornton Public Library<br>
> Sanbornton, NH<br>
> _______________________________________________<br>
> Koha mailing list<br>
> <a href="mailto:Koha@lists.katipo.co.nz">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>
> _______________________________________________<br>
> Koha mailing list<br>
> <a href="mailto:Koha@lists.katipo.co.nz">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>
> _______________________________________________<br>
> Koha mailing list<br>
> <a href="mailto:Koha@lists.katipo.co.nz">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>
<br>
</div></div><font color="#888888">--<br>
Chris Cormack<br>
Catalyst IT Ltd.<br>
+64 4 803 2238<br>
PO Box 11-053, Manners St, Wellington 6142, New Zealand<br>
</font></blockquote></div><br>