[Koha] Report Help

[Chris Nighswonger]

2010/12/2 Jared Camins-Esakov <jcamins at cpbibliography.com>

> Nicole,
>
> I'm not sure I'm completely understanding what you want to do, but, if I
> am, does this query work:
>
> SELECT DATE(datetime) AS date, SUBSTRING_INDEX(itemcallnumber, ' ', 1) AS
> 'Call# range', count(*) AS count FROM statistics LEFT JOIN items USING
> (itemnumber) WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime)
> = 2010 GROUP BY DATE(datetime), SUBSTRING_INDEX(itemcallnumber, ' ', 1);
>
> What this should do is select all the text before the first space in a call
> number. I don't have access to any catalog with circulation data, though, so
> I can't really test it.
>
>
I probably should sit out this one, because I don't have a suggestion, but
Jared's suggestion works nicely unless you have call numbers in the form of
"R 1234.1233" which results in just the "R" being listed in the "Call#
range" column.

You can change the 'count' value in the SUBSTRING_INDEX to '2', but that
gets more of the call number than Nicole was looking for I think.

Kind Regards,
Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20101202/86de85ca/attachment.htm