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