<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16939" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV>
<DIV>Dear KOHA Users,</DIV>
<DIV> </DIV>
<DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">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.<SPAN
style="mso-spacerun: yes"> </SPAN>We are getting closer to what we want,
but we have gotten stuck – are there any SQL gurus out there who can help
us?</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" /><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Right now we have the following 2
reports… we want to merge them into one report (I’ll describe how below).</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">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).</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">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</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">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).</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">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</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">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.<SPAN style="mso-spacerun: yes">
</SPAN>Is there a way to include totals and sub-totals on the same report?<SPAN
style="mso-spacerun: yes"> </SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">We would greatly appreciate any
help!</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p> </o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Thank you,</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Adrea</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"> </P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Adrea Lund<BR>Head of Adult
Services<BR>Grand County Public Library<BR>257 E. Center St.<BR>Moab, UT
84532<BR>435-259-1111 ext11</P></DIV></DIV></BODY></HTML>