[Koha-devel] Help with SQL report

[Nicole Engard]

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 at bigballofwax.co.nz> wrote:
> 2009/12/1 Adrea Lund <adrea at 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 at lists.koha.org
> http://lists.koha.org/mailman/listinfo/koha-devel
>