[Koha] Looking for a bit of report help

[Nicole Engard]

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 <kbrowne at 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 (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
>>
>
>