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?
We would greatly appreciate any help!
Thank you,
Adrea
Adrea Lund
Head of Adult Services
Grand County Public Library
257 E. Center St.
Moab, UT 84532
435-259-1111 ext11
_______________________________________________
Koha-devel mailing list
Koha-devel@lists.koha.org
http://lists.koha.org/mailman/listinfo/koha-devel
