[Koha] Report Help

[Jared Camins-Esakov]

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.

Regards,
Jared

On Thu, Dec 2, 2010 at 10:16 AM, Nicole Engard <nengard at gmail.com> wrote:

> I am looking at this report in the report library:
>
> SELECT DATE(datetime) AS date, substring(itemcallnumber,1,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(itemcallnumber,1,1)
>
> And I'm wondering if anyone knows how I could change that substring
> bit to show me everything before the first space.  For example I want
> F but I also want JFIC - basically I want to lump it together using
> the first bit of the call number - the bit before the space.
>
> 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
>



-- 
Jared Camins-Esakov
Freelance bibliographer, C & P Bibliography Services, LLC
(phone) +1 (917) 727-3445
(e-mail) jcamins at cpbibliography.com
(web) http://www.cpbibliography.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20101202/a024a465/attachment-0001.htm