[Koha] All I want from Santa (or possibly the great Paul Poulain ;)

[Stephen Hedges]

>> What I want to be able to
>> generate is a raw number of
>> records for a certain type of material.
>
> Right now?  Query MySQL directly!  In the meantime, submit an enhancement
> bug
> for the kind of report you'd like to see.  Perhaps in the meantime some
> other
> folks can chime in on how they get the reports they need.

Here are some of the SQL statements I use to prepare the Nelsonville
Library reports:

SELECT biblioitems.itemtype, COUNT(biblioitems.itemtype) AS count FROM
items,biblioitems WHERE YEAR(items.dateaccessioned)=2004 AND
MONTH(items.dateaccessioned)=4 AND
biblioitems.biblioitemnumber=items.biblioitemnumber GROUP BY
biblioitems.itemtype;

This one produces a count of items, subdivided by item type, that were
added to the catalog in any given month.  (In the example above, the month
would be April 2004.)  If you want a count of all items by item type, just
leave out the YEAR and MONTH conditionals.

SELECT itemtype, COUNT(itemtype) AS count FROM statistics WHERE
YEAR(datetime)=2004 AND MONTH(datetime)=4 AND branch='APL' AND
type='issue' GROUP BY itemtype;

This one produces a count of books issued by a particular branch in a
particular month.  Note that this _does not_ count renewals.  I use
another statement for that:

SELECT biblioitems.itemtype, COUNT(biblioitems.itemtype) AS count FROM
statistics,biblioitems,items WHERE YEAR(statistics.datetime)=2004 AND
MONTH(statistics.datetime)=4 AND type='renew' AND
statistics.itemnumber=items.itemnumber AND items.homebranch='APL' AND
items.biblioitemnumber=biblioitems.biblioitemnumber GROUP BY
biblioitems.itemtype;

Note that modifying the SQL statement for issues by substituting "renew"
for "issue" gives an inaccurate count.  Also note that the issue SQL
statement is more accurate yet than the renew SQL statement, since it
counts by the branch where the item was issued, and the renew SQL
statement counts by the home branch, which is often not the same as the
issuing branch.  But I haven't been able to come up with a 100% accurate
SQL statement for counting renewals by issuing branch.  (I'd be happy to
entertain suggestions...)

Note also that if you don't care about issuing branch vs. home branch, you
can get a combined total of issues and renewals with this statement:

SELECT biblioitems.itemtype, COUNT(biblioitems.itemtype) AS count FROM
statistics,biblioitems,items WHERE YEAR(statistics.datetime)=2004 AND
MONTH(statistics.datetime)=3 AND (type='renew' OR type ='issue') AND
statistics.itemnumber=items.itemnumber AND items.homebranch='APL' AND
items.biblioitemnumber=biblioitems.biblioitemnumber GROUP BY
biblioitems.itemtype;

Finally, here's one that counts newly registered borrowers at each branch
in any particular month:

SELECT branchcode, COUNT(branchcode) AS count FROM borrowers WHERE
YEAR(dateenrolled)=2004 AND MONTH(dateenrolled)=4 AND categorycode='A'
GROUP BY branchcode;

-- 
Stephen Hedges
Skemotah Solutions, USA
www.skemotah.com  --  shedges at skemotah.com