Also, if you have working reports remember to share them with your colleagues via the wiki: http://wiki.koha.org/doku.php?id=sql_library Thanks a bunch, Nicole C. Engard Documentation Manager (and designated nagger) On Mon, Nov 30, 2009 at 4:38 PM, Chris Cormack <chris@bigballofwax.co.nz> wrote:

2009/12/1 Adrea Lund <adrea@moablibrary.org>:

...

Dear KOHA Users,

With the help of Beverly Church at Liblime (Thank You Beverly!!), our library is trying to get an SQL report that we can use for weeding our collections.  We are getting closer to what we want, but we have gotten stuck – are there any SQL gurus out there who can help us?

Right now we have the following 2 reports… we want to merge them into one report (I’ll describe how below).

Report #1 gives us all items within a specified call number range with less than 5 total circulations during a specified time period (circulations include issues, renewals and local use).

select count(*), itemcallnumber as ' call number', dateaccessioned, author, title, barcode, datelastseen as 'last seen', itemlost as 'lost status', damaged from statistics, items, biblio where statistics.itemnumber = items.itemnumber and items.biblionumber = biblio.biblionumber and statistics.type in ('issue','renew', 'localuse') and date(datetime) between '2004-01-01' and '2009-12-31' and itemcallnumber between 'MUSIC 1999 CD' and 'MUSIC ZZ TOP CD' group by statistics.itemnumber having count(*) < 5 order by cn_sort

Report #2 gives us a list breakdown by year of all items within a specified call number range that have less than 5 total circulations during any year within a specified time frame (circulations include issues, renewals and local use).

select year(datetime) as 'year', count(*), itemcallnumber as ' call number', dateaccessioned, author, title, barcode, datelastseen as 'last seen', itemlost as 'lost status', damaged from statistics, items, biblio where statistics.itemnumber = items.itemnumber and items.biblionumber = biblio.biblionumber and statistics.type in ('issue','renew', 'localuse') and date(datetime) between '2004-01-01' and '2009-12-31' and itemcallnumber between 'MUSIC 1999 CD' and 'MUSIC ZZ TOP CD' group by year, statistics.itemnumber having count(*) < 5 order by cn_sort

We would like to merge them into a report which gives us the “individual” circulation counts by year, but also limits the list of materials to ones with a total circulation of less than 5 during the total specified time period.  Is there a way to include totals and sub-totals on the same report?

Hi Andrea

I'm not sure I'm understanding what you are asking for correctly. Could you put a sample of what you think the output should look like?

Chris _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel

Thank you for all the responses. I should have explained more clearly that I know extremely little about mysql - so some of the comments I've gotten from the lists I sent this to are going right over my head. But I do promise that once we have a working report(s), I will make sure it gets posted to the KOHA community report repository. We realized that we have a major problem with the sql reports that I posted previously. These reports do not include zero circulations when listing items with less than three circulations. We are using this for weeding so we definitely want to know about those zero circulation items! Beverly has been a wonderfully helpful, but does not know how to solve this problem. Perhaps we need to approach this report in a different way? I will explain what we are trying to get and attach an example to this email. I'll try to answer Chris Cormack's question and describe our ideal report - It would allow you to specify the following variables... 1) a specific call number range 2) a specific time frame - _____ date to _____ date 3) choose "greater than," "less than" or "equal to" a specific a number of circulations. For example the report could list all "copies with less than 3 circulations" in the specified call number range (MUSIC 1999 CD to MUSIC ZZ TOP CD) during the specified time period (2004-01-01 to 2009-12-31). Circulations = issues+renewals+local use I am attaching a copy of a page from the circulation report we used to use with our old ILS - Follett. The attached .pdf is listing oversize non-fiction books with less than 3 circulations between 1/1/2004 and 4/28/2009. So it gives you items that meet this criteria and also lists the following data - total circulations for today, total circulations this month, total circulations year to date, total circulations last year, and total circulations during the specified time period. So, that is what we need. I hope my efforts to explain, are not making this more confusing :) The final report does not need to look like the attached report, it just needs to have the data. We would greatly appreciate any help, and think that this type of circulation report (or something similar) is an essential tool for librarians who regularly weed their collections. Regarding Chris Nighswonger's comment - We are hoping to eventually move to LEK, and I can test the reports in our LEK sandbox. If we could get a report working with our current database that would be a great first step. We are currently using KOHA version 3.01.00.032. Thank you, Adrea Adrea Lund Head of Adult Services Grand County Public Library 257 E. Center St. Moab, UT 84532 435-259-1111 ext11

Chris Cormack a écrit :

2009/12/11 Adrea Lund <adrea@moablibrary.org>:

...

I'll try to answer Chris Cormack's question and describe our ideal report -

It would allow you to specify the following variables... 1) a specific call number range 2) a specific time frame - _____ date to _____ date 3) choose "greater than," "less than" or "equal to" a specific a number of circulations. For example the report could list all "copies with less than 3 circulations" in the specified call number range (MUSIC 1999 CD to MUSIC ZZ TOP CD) during the specified time period (2004-01-01 to 2009-12-31). Circulations = issues+renewals+local use

(answer for everyone) well... this in in BibLibre pipeline (entering parameters at runtime), and already works pretty well for SQL reports, what is missing is just being able to create parameters in the wizard. Otherwise, the following syntax works well :

SELECT barcode FROM items WHERE callnumber in (<<Call Number from>>,<<Call Number to>>) AND branchcode=<<Choose branch|branches>> Will ask : Call Number from: ____________ Call Number to: ____________ Choose branch: __________v (_____v is the list of branches) [OK] at runtime. It won't probably be in 3.2 (feature freeze declared), but in git & in 3.4, it will definitely be ! (don't help you Adrea, sorry) -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08

2009/12/19 Adrea Lund <adrea@moablibrary.org>:

Dear Chris, Paul and KOHA devel list,

Thank you very much for your responses.  Our library is currently using KOHA version 3.01.00.032., so if you could write a report like I described that works with version 3.01.00.032, then we don't care right now if it works with LEK.  It is also not important that I be able to specify parameters in a wizard, I am happy to go in and edit the SQL with new call numbers, dates etc. each time I run it. If a report is created that does what we want in version 3.01.00.032, I will figure out how to post it to the community repository of KOHA reports - I haven't ever done that :) - but I'll figure out how and share it, I promise.

I don't see any reports like this in the repository right now.  There is one called a "super weeder" but I don't think it is quite what we are looking for, so I wonder what kind of "weeding" reports other KOHA libraries are currently using?

(Sorry for the delayed reponse, your responses got marked spam and I didn't find them until now.)

Ahhh thats a pretty old version of the code. Koha is currently at 3.01.00.100 in master, and 3.00.05.001 in the 3.0.x branch. Ill have a go at writing one that works with the latest database structure. This will probably work fine with your older version. It will probably have to wait until after Christmas though :) Chris