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