[Koha] Report Help

[Ian Walls]

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 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.
>
> 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/
>
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at 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 at bywatersolutions.com
Twitter: @sekjal
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20101202/abee98fc/attachment.htm