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.<br>
<br>Thanks, Jared!<br><br><br>-Ian<br><br><div class="gmail_quote">2010/12/2 Jared Camins-Esakov <span dir="ltr"><<a href="mailto:jcamins@cpbibliography.com">jcamins@cpbibliography.com</a>></span><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div>Nicole,</div><div><br></div>I'm not sure I'm completely understanding what you want to do, but, if I am, does this query work:<div><br></div><div><div>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);</div>
</div><div><br></div><div>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.</div><div>
<br></div><div>Regards,</div><div>Jared</div><div><div><div></div><div class="h5"><br><div class="gmail_quote">On Thu, Dec 2, 2010 at 10:16 AM, Nicole Engard <span dir="ltr"><<a href="mailto:nengard@gmail.com" target="_blank">nengard@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">I am looking at this report in the report library:<br>
<br>
SELECT DATE(datetime) AS date, substring(itemcallnumber,1,1) AS 'Call#<br>
range', count(*) AS count FROM statistics LEFT JOIN items USING<br>
(itemnumber) WHERE statistics.type IN ('issue', 'renew') AND<br>
YEAR(datetime) = 2010 GROUP BY DATE(datetime),<br>
substring(itemcallnumber,1,1)<br>
<br>
And I'm wondering if anyone knows how I could change that substring<br>
bit to show me everything before the first space. For example I want<br>
F but I also want JFIC - basically I want to lump it together using<br>
the first bit of the call number - the bit before the space.<br>
<br>
Thanks in advance,<br>
Nicole<br>
_______________________________________________<br>
Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
<a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
</blockquote></div><br><br clear="all"><br></div></div>-- <br>Jared Camins-Esakov<div>Freelance bibliographer, C & P Bibliography Services, LLC</div><div>(phone) +1 (917) 727-3445</div><div>(e-mail) <a href="mailto:jcamins@cpbibliography.com" target="_blank">jcamins@cpbibliography.com</a></div>
<div>(web) <a href="http://www.cpbibliography.com/" target="_blank">http://www.cpbibliography.com/</a></div><br>
</div>
<br>_______________________________________________<br>
Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
<a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br>Ian Walls<br>Lead Development Specialist<br>ByWater Solutions<br>Phone # (888) 900-8944<br><a href="http://bywatersolutions.com">http://bywatersolutions.com</a><br><a href="mailto:ian.walls@bywatersolutions.com">ian.walls@bywatersolutions.com</a><br>
Twitter: @sekjal<br>