Tell me more ×
Libraries & Information Science Stack Exchange is a question and answer site for librarians and library professionals. It's 100% free, no registration required.
up vote 4 down vote favorite

I have inherited a Koha 3.05 installation, and now need to figure out how to run a report within Koha to find out which call numbers grow at higher rates... I know it's going to be like a SQL query, but I don't understand the actual process at this point, much less how to craft this particular query.

Any help is appreciated, I'm a Koha admin newbie.

share|improve this question
To clarify, are you looking for circ increase or item count increase? Depending on your level of comfort with SQL, this site might provide some hints: wiki.koha-community.org/wiki/SQL_Reports_Library There are a couple of queries that look like they could be modified to be close to what you want but I don't have a Koha database to test them on to be sure. – Gem Nov 15 '12 at 18:04

1 Answer

up vote 1 down vote

If you're talking about collection growth, the best way I can think of to find that is to pull out a monthly count by call number, and then graph it in Excel. The query to pull this data out gets a bit messy, though, if you're using LC call numbers, as MySQL doesn't care to parse substrings based on patterns:

SELECT date_format(items.dateaccessioned, '%M %Y') AS "Month Added",
    SUBSTR(TRIM(items.itemcallnumber), 1, LEAST(
        IF(LOCATE(' ', TRIM(items.itemcallnumber)) > 0, LOCATE(' ', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('0', TRIM(items.itemcallnumber)) > 0, LOCATE('0', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('1', TRIM(items.itemcallnumber)) > 0, LOCATE('1', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('2', TRIM(items.itemcallnumber)) > 0, LOCATE('2', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('3', TRIM(items.itemcallnumber)) > 0, LOCATE('3', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('4', TRIM(items.itemcallnumber)) > 0, LOCATE('4', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('5', TRIM(items.itemcallnumber)) > 0, LOCATE('5', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('6', TRIM(items.itemcallnumber)) > 0, LOCATE('6', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('7', TRIM(items.itemcallnumber)) > 0, LOCATE('7', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('8', TRIM(items.itemcallnumber)) > 0, LOCATE('8', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('9', TRIM(items.itemcallnumber)) > 0, LOCATE('9', TRIM(items.itemcallnumber)), 999)
    ) - 1) AS 'LC Range', COUNT(*) AS "Count"
FROM items
GROUP BY date_format(items.dateaccessioned, '%M %Y'),
    SUBSTR(TRIM(items.itemcallnumber), 1, LEAST(
        IF( LOCATE(' ', TRIM(items.itemcallnumber)) > 0, LOCATE(' ', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('0', TRIM(items.itemcallnumber)) > 0, LOCATE('0', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('1', TRIM(items.itemcallnumber)) > 0, LOCATE('1', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('2', TRIM(items.itemcallnumber)) > 0, LOCATE('2', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('3', TRIM(items.itemcallnumber)) > 0, LOCATE('3', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('4', TRIM(items.itemcallnumber)) > 0, LOCATE('4', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('5', TRIM(items.itemcallnumber)) > 0, LOCATE('5', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('6', TRIM(items.itemcallnumber)) > 0, LOCATE('6', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('7', TRIM(items.itemcallnumber)) > 0, LOCATE('7', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('8', TRIM(items.itemcallnumber)) > 0, LOCATE('8', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('9', TRIM(items.itemcallnumber)) > 0, LOCATE('9', TRIM(items.itemcallnumber)), 999)
    ) - 1)

This will give you 'Month Added', 'LC Range', and 'Count'. Once you have this, export it as a CSV file, which you can then open in Excel. Once in Excel, select the data range, and insert a PivotTable. You'll want your "Column Labels" to be the LC Range, the "Row Labels" to be the Month Added, and the "Values" to be the Count. Once you add the Count to "Values", click on it, and select "Value Field Settings..." to open a dialog box. In that dialog box, select the "Show Values As" tab, and then in the drop-down, select "Running Total In" and set the "Base Field" to Month Added. Click "OK". If you want a graphical representation of this data, now you just need to select "PivotChart" and select a Line style (or whatever style you prefer).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.