[Koha] Help with report on percentage of Circ

[Katelyn Browne]

Hi Nicole,

I think you just need to add

AND s.type = 'issue'

to the WHERE section of your query for x (e.g. right after ba.code = 'JUR').

That got the numbers looking right for my collection.

--Katelyn.

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 Tue, Mar 11, 2014 at 9:28 AM, Nicole Engard <nengard at gmail.com> wrote:

> I got this request today and I have to admit it's a bit over my head
> in terms of the math involved. Can someone help me with this report?
>
> -------------
>
> May we have a report that will give us the total circulation for a
> specific time frame and what percentage was attributed to a specific
> JUR Code?
>
> I ran this report and for example it gave me 128% as the percentage of
> total circ for JUR Code M814.
>
> SELECT
>    x.attribute as 'Attribute',
>    x.issues as 'Circ for this attribute',
>    (x.issues * 100)/(
>      SELECT COUNT(s.datetime) as 'total'
>      FROM statistics s
>      WHERE type = 'issue'
>      AND s.datetime BETWEEN <<Date BETWEEN (yyyy-mm-dd)>> AND <<and
> (yyyy-mm-dd)>>
>    ) as 'Percentage of total circ'
> FROM
>    (SELECT
>      ba.attribute as 'attribute',
>      COUNT(s.datetime) as 'issues'
>    FROM statistics s
>    JOIN borrower_attributes ba
>      ON s.borrowernumber = ba.borrowernumber
>    WHERE ba.code = 'JUR'
>      AND s.datetime BETWEEN <<Enter the same dates again BETWEEN
> (yyyy-mm-dd)>> AND
>      <<and (yyyy-mm-dd)>>
>    GROUP BY attribute) x
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>