Re: [Koha] Looking for a bit of report help
This should solve #1 (and thus maybe #2?). I'm reading both as percentage of the items in this collection that actually moved during a date range. The major potential flaw in this report is that it counts the number of items CURRENTLY in the collection, so if I recently purchased or weeded a bunch of DVDs, the percentages from an older date range wouldn't really be meaningful. SELECT x.ccode, x.issues AS 'Items circulating', y.itemcount AS 'Collection total', (x.issues * 100 / y.itemcount) AS 'Percentage of total items' FROM (SELECT i.ccode, COUNT(DISTINCT itemnumber) AS 'issues' FROM statistics s JOIN items i USING (itemnumber) WHERE s.datetime BETWEEN Checked out BETWEEN (-mm-dd)|date AND and (-mm-dd)|date AND s.type='issue' GROUP BY i.ccode) x LEFT JOIN (SELECT ccode, COUNT(itemnumber) AS 'itemcount' FROM items GROUP BY ccode) y USING (ccode) Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 kbro...@ccpcs.org http://www.ccpcs.org/library/ On Thu, May 15, 2014 at 10:51 PM, Nicole Engard neng...@gmail.com wrote: I'm looking for some help with reports that require more math than my brain can handle. #1: circulation by % coll code within a date range [50% of DVDs circulated] I have this one: http://wiki.koha-community.org/wiki/SQL_Reports_Library#Percentage_of_circs_by_collection_codebut I'm not sure how to alter it to show the percentage of the DVDs in the collection instead of the percentage of all check outs. #2: DVDs circulating 50% of total DVDs where DVD is a collection code This might be the same as the above ... unless you all read it differently than me. Thanks in advance, Nicole ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Looking for a bit of report help
Thanks! I'll ask the library if this is what they meant/wanted. Nicole On Tue, May 20, 2014 at 2:49 PM, Katelyn Browne kbro...@ccpcs.org wrote: This should solve #1 (and thus maybe #2?). I'm reading both as percentage of the items in this collection that actually moved during a date range. The major potential flaw in this report is that it counts the number of items CURRENTLY in the collection, so if I recently purchased or weeded a bunch of DVDs, the percentages from an older date range wouldn't really be meaningful. SELECT x.ccode, x.issues AS 'Items circulating', y.itemcount AS 'Collection total', (x.issues * 100 / y.itemcount) AS 'Percentage of total items' FROM (SELECT i.ccode, COUNT(DISTINCT itemnumber) AS 'issues' FROM statistics s JOIN items i USING (itemnumber) WHERE s.datetime BETWEEN Checked out BETWEEN (-mm-dd)|date AND and (-mm-dd)|date AND s.type='issue' GROUP BY i.ccode) x LEFT JOIN (SELECT ccode, COUNT(itemnumber) AS 'itemcount' FROM items GROUP BY ccode) y USING (ccode) Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 kbro...@ccpcs.org http://www.ccpcs.org/library/ On Thu, May 15, 2014 at 10:51 PM, Nicole Engard neng...@gmail.com wrote: I'm looking for some help with reports that require more math than my brain can handle. #1: circulation by % coll code within a date range [50% of DVDs circulated] I have this one: http://wiki.koha-community.org/wiki/SQL_Reports_Library#Percentage_of_circs_by_collection_codebut I'm not sure how to alter it to show the percentage of the DVDs in the collection instead of the percentage of all check outs. #2: DVDs circulating 50% of total DVDs where DVD is a collection code This might be the same as the above ... unless you all read it differently than me. Thanks in advance, Nicole ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
[Koha] Looking for a bit of report help
I'm looking for some help with reports that require more math than my brain can handle. #1: circulation by % coll code within a date range [50% of DVDs circulated] I have this one: http://wiki.koha-community.org/wiki/SQL_Reports_Library#Percentage_of_circs_by_collection_codebut I'm not sure how to alter it to show the percentage of the DVDs in the collection instead of the percentage of all check outs. #2: DVDs circulating 50% of total DVDs where DVD is a collection code This might be the same as the above ... unless you all read it differently than me. Thanks in advance, Nicole ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
