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
I don't have an answer yet (I'd had to poke at this) but remember you can ask here: http://wiki.koha.org/doku.php?id=sql_library for people to write reports - also make sure to share your report once you get it working. --- Nicole C. Engard Open Source Evangelist, LibLime (888) Koha ILS (564-2457) ext. 714 nce@liblime.com AIM/Y!/Skype: nengard http://liblime.com http://blogs.liblime.com/open-sesame/ 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
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
WHERE type= 'issue' AND datetime > '2009-06-01' AND datetime < '2009-06-30' Is that right? Keeping in mind that I'm no Koha expert, much less expert in MySQL, I could easily be wrong. But in more general db work, I was always taught that a bare date stamp equated to a time stamp of midnight (the start of that date). Thus a WHERE clause of "less than date" would technically exclude anything that was actually on that date. I was told to use < '2009-07-01' if I wanted to include everything up to '2009-06-30 23:59:59'. Right? Wrong? Conditional? -- Greg Barniskis, Computer Systems Integrator South Central Library System (SCLS) Library Interchange Network (LINK) <gregb at scls.lib.wi.us>, (608) 266-6348
You are right, you want to make it '2009-07-01' if you want the entire month of June. Also a tip - you can do this : WHERE type= 'issue' AND datetime BETWEEN '20091-06-01' AND '2009-07-01' For examples of reports you might want to check out this page: http://wiki.koha.org/doku.php?id=sql_library --- Nicole C. Engard Open Source Evangelist, LibLime (888) Koha ILS (564-2457) ext. 714 nce@liblime.com AIM/Y!/Skype: nengard http://liblime.com http://blogs.liblime.com/open-sesame/ On Thu, Jul 9, 2009 at 1:30 PM, Greg Barniskis<gregb@scls.lib.wi.us> wrote:
WHERE type= 'issue' AND datetime > '2009-06-01' AND datetime < '2009-06-30'
Is that right? Keeping in mind that I'm no Koha expert, much less expert in MySQL, I could easily be wrong. But in more general db work, I was always taught that a bare date stamp equated to a time stamp of midnight (the start of that date).
Thus a WHERE clause of "less than date" would technically exclude anything that was actually on that date. I was told to use
< '2009-07-01'
if I wanted to include everything up to '2009-06-30 23:59:59'.
Right? Wrong? Conditional?
-- Greg Barniskis, Computer Systems Integrator South Central Library System (SCLS) Library Interchange Network (LINK) <gregb at scls.lib.wi.us>, (608) 266-6348 _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Another option is to use the "date" time function: WHERE type= 'issue' AND date(datetime) BETWEEN '20091-06-01' AND '2009-06-30' This seems to work. On Thu, Jul 9, 2009 at 1:33 PM, Nicole Engard <nicole.engard@liblime.com>wrote:
You are right, you want to make it '2009-07-01' if you want the entire month of June. Also a tip - you can do this :
WHERE type= 'issue' AND datetime BETWEEN '20091-06-01' AND '2009-07-01'
For examples of reports you might want to check out this page: http://wiki.koha.org/doku.php?id=sql_library
---
Nicole C. Engard Open Source Evangelist, LibLime (888) Koha ILS (564-2457) ext. 714 nce@liblime.com AIM/Y!/Skype: nengard
http://liblime.com http://blogs.liblime.com/open-sesame/
On Thu, Jul 9, 2009 at 1:30 PM, Greg Barniskis<gregb@scls.lib.wi.us> wrote:
WHERE type= 'issue' AND datetime > '2009-06-01' AND datetime <
'2009-06-30'
Is that right? Keeping in mind that I'm no Koha expert, much less expert in MySQL, I could easily be wrong. But in more general db work, I was always taught that a bare date stamp equated to a time stamp of midnight (the start of that date).
Thus a WHERE clause of "less than date" would technically exclude anything that was actually on that date. I was told to use
< '2009-07-01'
if I wanted to include everything up to '2009-06-30 23:59:59'.
Right? Wrong? Conditional?
-- Greg Barniskis, Computer Systems Integrator South Central Library System (SCLS) Library Interchange Network (LINK) <gregb at scls.lib.wi.us>, (608) 266-6348 _______________________________________________ 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
-- ----------------- Beverly Church Project Manager - LibLime phone: 1.888.564.2457 x717 email: beverly.church@liblime.com skype: beverlychurch
You can also do: WHERE YEAR(datetime) = 2009 AND MONTH(datetime) = 6 I tend to prefer that one, since it is easy to update/increment and you don't have to remember how many days are in a given month for that specific year. -- Joe Atzberger LibLime - Open Source Library Solutions On Thu, Jul 9, 2009 at 1:30 PM, Greg Barniskis <gregb@scls.lib.wi.us> wrote:
WHERE type= 'issue' AND datetime > '2009-06-01' AND datetime < '2009-06-30'
Is that right? Keeping in mind that I'm no Koha expert, much less expert in MySQL, I could easily be wrong. But in more general db work, I was always taught that a bare date stamp equated to a time stamp of midnight (the start of that date).
Thus a WHERE clause of "less than date" would technically exclude anything that was actually on that date. I was told to use
< '2009-07-01'
if I wanted to include everything up to '2009-06-30 23:59:59'.
Right? Wrong? Conditional?
-- Greg Barniskis, Computer Systems Integrator South Central Library System (SCLS) Library Interchange Network (LINK) <gregb at scls.lib.wi.us>, (608) 266-6348 _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
That's my favorite one - but for sometimes I never think of it :) thanks Joe! --- Nicole C. Engard Open Source Evangelist, LibLime (888) Koha ILS (564-2457) ext. 714 nce@liblime.com AIM/Y!/Skype: nengard http://liblime.com http://blogs.liblime.com/open-sesame/ 2009/7/9 Joe Atzberger <ohiocore@gmail.com>:
You can also do:
WHERE YEAR(datetime) = 2009 AND MONTH(datetime) = 6
I tend to prefer that one, since it is easy to update/increment and you don't have to remember how many days are in a given month for that specific year. -- Joe Atzberger LibLime - Open Source Library Solutions
On Thu, Jul 9, 2009 at 1:30 PM, Greg Barniskis <gregb@scls.lib.wi.us> wrote:
WHERE type= 'issue' AND datetime > '2009-06-01' AND datetime < '2009-06-30'
Is that right? Keeping in mind that I'm no Koha expert, much less expert in MySQL, I could easily be wrong. But in more general db work, I was always taught that a bare date stamp equated to a time stamp of midnight (the start of that date).
Thus a WHERE clause of "less than date" would technically exclude anything that was actually on that date. I was told to use
< '2009-07-01'
if I wanted to include everything up to '2009-06-30 23:59:59'.
Right? Wrong? Conditional?
-- Greg Barniskis, Computer Systems Integrator South Central Library System (SCLS) Library Interchange Network (LINK) <gregb at scls.lib.wi.us>, (608) 266-6348 _______________________________________________ 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
participants (6)
-
Beverly Church -
Greg Barniskis -
Joe Atzberger -
Nicole Engard -
Ryan Higgins -
Scott Zollars