Hi Scott. Let me preface this by saying that I know nothing about SQL. A little while ago I got some help with a similar question. I needed a report that would generate a count of items added to the catalog by Dewey range. My report gathers data for a consortium implementation of Koha. I think you need to work the following elements into your statement: substring(itemcallnumber,1,1) as 'Call# range' - should be part of your SELECT statement for the item call number (I think). Group by substring(itemcallnumber,1,1) - should be part of your ORDER BY statement (I think). I tried to work these into your statement but I couldn't get it to work. It could be because I've got a different setup for my Koha system, or it could be because I'm all wet and this advice is useless. Maybe others can expand on this. Good luck, Nora ________________________ Nora Blake MassCat Manager P.O. Box 241 South Deerfield, MA 01373-0241 Toll Free in MA: 866-MASSCAT (627-7228) Local Phone: 413-665-5511 Email: nblake@masscat.org Website: http://www.masscat.org AIM: vegancatwoman Skype: veganlibrarian Skype: nora.blake -----Original Message----- From: koha-bounces@lists.katipo.co.nz [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Ryan Higgins Sent: Thursday, July 09, 2009 12:13 PM To: Scott Zollars Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Problem constructing a report Scott, you have an extra close-paren in that first to_days() bit. (TO_DAYS(06/01/2009)-TO_DAYS(06/30/2009)) is the corrected syntax, but adding that to the SELECT clause doesn't give you any useful information. I think you want something more like SELECT itemcallnumber, barcode, datetime, author, title from statistics LEFT JOIN items USING( itemnumber ) LEFT JOIN biblio USING( biblionumber ) WHERE type= 'issue' AND datetime > '2009-06-01' AND datetime < '2009-06-30' ORDER BY itemcallnumber Hope that helps, -- Ryan Higgins LibLime * Open-Source Solutions for Libraries Featuring KohaZOOM ILS 888-564-2457 x704 2009/7/8 Scott Zollars <ScottZ@labette.edu>:
I am trying to come up with a report that will list how many items were checked out in each Dewey area, their barcode and date. If I could get the author and title, that would be icing on the cake. I haven't figured out the last part. However, I figured out this for the first part:
SELECT items.datelastborrowed,items.barcode,items.itemcallnumber, (TO_DAYS(06/01/2009))-TO_DAYS(06/30/2009)) FROM borrowers LEFT JOIN statistics on (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE DATE (items.datelastborrowed) BETWEEN '2009-06-01' AND '2009-06-30'
It shows that there are 3 pages of a report but when I run the report, I receive the following message:
Content-type: text/html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en" xml:lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head><title>Koha Error</title></head>
<body>
<h1>Koha error</h1>
<p>The following fatal error has occurred:</p>
<pre><code>Can't use an undefined value as an ARRAY reference at /home/slb/_kohaclone/reports/guided_reports.pl line 461.
</code></pre>
<table>
<tr><th>Apache</th><td> Server version: Apache/2.2.3
Server built: Sep 8 2008 08:59:32
</td></tr>
<tr><th>Koha</th><td> 3.01.00.032</td></tr>
<tr><th>Koha DB</th><td> 3.0100032</td></tr>
<tr><th>MySQL</th><td> mysql Ver 14.12 Distrib 5.0.32
for pc-linux-gnu (x86_64) using readline 5.2
</td></tr>
<tr><th>OS</th><td> Linux shin.ec2.liblime.com 2.6.21.7-2.fc8xen #1 SMP Fri Feb 15 12:34:28 EST 2008 x86_64</td></tr>
<tr><th>Perl</th><td> 5.008008</td></tr>
</table></body></html>
I have constructed another report that gives me basically what I want but shows everything that is checked out since we went live with the system. That SQL is:
SELECT items.datelastborrowed,items.itemcallnumber,items.barcode, FROM borrowers LEFT JOIN statistics on (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber) ORDER BY items.datelastborrowed desc,items.itemcallnumber desc,items.barcode desc
So how do I get the date in example one to work with example 2?
Scotty
"You must try to generate happiness. If you aren't happy in one place, chances are you won't be happy anyplace" Ernie Banks, former Chicago Cub
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha