[Koha] Circ stats & deleting records

[Susan Bennett]

Thanks Bev and Chris! I'll see what I can work out.
Susan Bennett
ILS System Administrator
Geauga County Public Library
440 286-6811 x 125
440 286-7419 FAX


On Thu, May 13, 2010 at 5:05 PM, Chris Cormack <chrisc at catalyst.net.nz>wrote:

> * Church, Beverly (bchurch at ptfs.com) wrote:
> >    Susan,
> >
> >    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.
> >    Bev
>
> Actually this is where the UNION syntax comes in handy.
> You can do something like this
>
> 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
> UNION
> SELECT deleteditems.ccode AS Collection, COUNT( statistics.itemnumber )
> AS Count, Branch AS BRANCH FROM statistics LEFT JOIN deleteditems ON
> (statistics.itemnumber = deleteditems.itemnumber) WHERE statistics.type
> IN ('issue')  AND year(statistics.datetime) = '__YEAR__' and
> month(statistics.datetime) = '__MONTH__' GROUP BY branch,
> deleteditems.ccode;
>
> Which will give the union of the 2 queries result sets
>
> Hope this helps
>
> Chris
>
> >
> >    * * * *
> >    Beverly Church
> >    PTFS Project Manager
> >
> >    phone: 1-888-564-2457 ext. 717
> >    email: bchurch at ptfs.com
> >    skype: beverlychurch
> >
> >    2010/5/12 Susan Bennett <susan.bennett at geaugalibrary.info>
> >
> >      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?
> >
> >      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
> >
> >      Any help would be appreciation!
> >
> >      Susan Bennett
> >      ILS System Administrator
> >      Geauga County Public Library
> >      440 286-6811 x 125
> >      440 286-7419 FAX
> >
> >      On Wed, May 12, 2010 at 2:20 PM, Cab Vinton <bibliwho at gmail.com>
> wrote:
> >
> >        On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <
> oleonard at myacpl.org>
> >        wrote:
> >
> >        > What you'll need to remember when tracking those statistics is
> that
> >        if
> >        > you want to pull information about those deleted items along
> with
> >        the
> >        > statistical data (e.g. how many DVDs you circulated in 2006),
> you'll
> >        > have to query both the items table *and* the deleteditems table.
> >
> >        Oof. That doesn't sound like much fun. So we might end up having
> to
> >        modify a slew of different reports.
> >
> >        Sounds like we're better off using the WITHDRAWN, LOST, etc. item
> >        statuses ...
> >
> >        Thank you, Owen.
> >        Cab Vinton, Director
> >        Sanbornton Public Library
> >        Sanbornton, NH
> >        _______________________________________________
> >        Koha mailing list
> >        Koha at lists.katipo.co.nz
> >        http://lists.katipo.co.nz/mailman/listinfo/koha
> >
> >      _______________________________________________
> >      Koha mailing list
> >      Koha at lists.katipo.co.nz
> >      http://lists.katipo.co.nz/mailman/listinfo/koha
>
> > _______________________________________________
> > Koha mailing list
> > Koha at lists.katipo.co.nz
> > http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
> --
> Chris Cormack
> Catalyst IT Ltd.
> +64 4 803 2238
> PO Box 11-053, Manners St, Wellington 6142, New Zealand
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20100514/ed0ee5eb/attachment.htm