[Koha] Looking for a bit of report help

[Katelyn Browne]

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 (yyyy-mm-dd)|date>> AND
     <<and (yyyy-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
kbrowne at ccpcs.org
http://www.ccpcs.org/library/


On Thu, May 15, 2014 at 10:51 PM, Nicole Engard <nengard at 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 at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>