That report certainly works, at least in the sense that it returns a valid set of columns. As to whether it's returning the info that it's intended to, I'm still not sure (would require more testing). But at first blush, it looks pretty good. If nothing else, the use of SUBSTRING_INDEX() opens up lots of possibilities. Thanks, Jared! -Ian 2010/12/2 Jared Camins-Esakov <jcamins@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.

Regards, Jared

On Thu, Dec 2, 2010 at 10:16 AM, Nicole Engard <nengard@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@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@cpbibliography.com (web) http://www.cpbibliography.com/

_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha

-- Ian Walls Lead Development Specialist ByWater Solutions Phone # (888) 900-8944 http://bywatersolutions.com ian.walls@bywatersolutions.com Twitter: @sekjal

That is what I want - just the R - I'm going to send this to the library and see if it was what they were looking for. Thanks all!! Nicole On Thu, Dec 2, 2010 at 10:37 AM, Chris Nighswonger <cnighswonger@foundations.edu> wrote:

2010/12/2 Jared Camins-Esakov <jcamins@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