[Koha] Circ stats & deleting records

[Chris Cormack]

* 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