[Koha] Problem constructing a report

Fri Jul 10 06:47:38 NZST 2009

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 at masscat.org
Website: http://www.masscat.org
AIM: vegancatwoman
Skype: veganlibrarian
Skype: nora.blake

-----Original Message-----
From: koha-bounces at lists.katipo.co.nz [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Ryan Higgins
Sent: Thursday, July 09, 2009 12:13 PM
To: Scott Zollars
Cc: koha at 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 at 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 at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
_______________________________________________
Koha mailing list
Koha at lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha