If we choose to delete records for items that are lost, withdrawn, etc., are the associated circulation statistics deleted from the database as well? Thanks, Cab Vinton, Director Sanbornton Public Library Sanbornton, NH
If we choose to delete records for items that are lost, withdrawn, etc., are the associated circulation statistics deleted from the database as well?
No, those items' transactions will remain in the statistics table. 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. -- Owen -- Web Developer Athens County Public Libraries http://www.myacpl.org
On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <oleonard@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
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@gmail.com> wrote:
On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <oleonard@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
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 * * * * Beverly Church PTFS Project Manager phone: 1-888-564-2457 ext. 717 email: bchurch@ptfs.com skype: beverlychurch 2010/5/12 Susan Bennett <susan.bennett@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@gmail.com> wrote:
On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <oleonard@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
* Church, Beverly (bchurch@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@ptfs.com skype: beverlychurch
2010/5/12 Susan Bennett <susan.bennett@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@gmail.com> wrote:
On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <oleonard@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list Koha@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
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@catalyst.net.nz>wrote:
* Church, Beverly (bchurch@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@ptfs.com skype: beverlychurch
2010/5/12 Susan Bennett <susan.bennett@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@gmail.com>
wrote:
On Wed, May 12, 2010 at 1:58 PM, Owen Leonard <
oleonard@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list Koha@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
participants (5)
-
Cab Vinton -
Chris Cormack -
Church, Beverly -
Owen Leonard -
Susan Bennett