Josh- I tried this on Great Bend Public Library's collection and have a few observations. If the to/from date is the same (12/08 - 12/08), the result is zero. So I tried 12/08 - 12/09 and then got a figure. Kathy At 02:47 AM 12/6/2008, Joshua Ferraro wrote:
Hi Katharine,
On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon <katharin@ci.salinas.ca.us> wrote:
Hello, I am new to the list so sorry if this has been asked before but does anybody have the SQL for a report that would just give me a total of all items circulated on a certain day. Just the number, no titles or types. You can find this out from the Circulation Statistics wizard, located in the Reports area. The path is /cgi-bin/koha/reports/issues_stats.pl
Select Period as a row, and specify the dates in question as From/To.
Select Library as a Column
Run the report and you will get a chart of circulations per branch for that date range.
Cheers,
-- Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE CEO migration, training, maintenance, support LibLime Featuring Koha Open-Source ILS jmf@liblime.com |Full Demos at http://liblime.com/koha |1(888)KohaILS _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Kathy Rippel Dept. Head/Consultant--Resource Sharing and Access ; Pathfinder Central (Manager) Central Kansas Library System 1409 Williams Great Bend, KS 67530 (620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax kdr@ckls.org ******************************************************************************************** I'm currently reading: Redesigning the American Lawn, by F. Herbert Bormann [... et al.] I'm currently listening to: Xenocide, by Orson Scott Card.
Last week I updated this document: http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Rep... to include a tip about dates. "TIP: The statistic reports are exclusive, so, if you want a report to show data for the month of November you should pick October 31 to December 1." So if you want the month of December you should do December 1 to January 1. --- 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 Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <kdr@ckls.org> wrote:
Josh-
I tried this on Great Bend Public Library's collection and have a few observations.
If the to/from date is the same (12/08 - 12/08), the result is zero.
So I tried 12/08 - 12/09 and then got a figure.
Kathy
At 02:47 AM 12/6/2008, Joshua Ferraro wrote:
Hi Katharine,
On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon <katharin@ci.salinas.ca.us> wrote:
Hello, I am new to the list so sorry if this has been asked before but does anybody have the SQL for a report that would just give me a total of all items circulated on a certain day. Just the number, no titles or types. You can find this out from the Circulation Statistics wizard, located in the Reports area. The path is /cgi-bin/koha/reports/issues_stats.pl
Select Period as a row, and specify the dates in question as From/To.
Select Library as a Column
Run the report and you will get a chart of circulations per branch for that date range.
Cheers,
-- Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE CEO migration, training, maintenance, support LibLime Featuring Koha Open-Source ILS jmf@liblime.com |Full Demos at http://liblime.com/koha |1(888)KohaILS _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Kathy Rippel Dept. Head/Consultant--Resource Sharing and Access ; Pathfinder Central (Manager) Central Kansas Library System 1409 Williams Great Bend, KS 67530
(620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax
kdr@ckls.org ******************************************************************************************** I'm currently reading: Redesigning the American Lawn, by F. Herbert Bormann [... et al.] I'm currently listening to: Xenocide, by Orson Scott Card.
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Hello, --- On Fri, 12/12/08, Nicole Engard <nicole.engard@liblime.com> wrote:
From: Nicole Engard <nicole.engard@liblime.com> Subject: Re: [Koha] SQL report To: "Kathy Rippel" <kdr@ckls.org> Cc: koha@lists.katipo.co.nz, "Katharine Dixon" <katharin@ci.salinas.ca.us> Date: Friday, December 12, 2008, 3:57 AM Last week I updated this document: http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Rep... to include a tip about dates.
"TIP: The statistic reports are exclusive, so, if you want a report to show data for the month of November you should pick October 31 to December 1."
So if you want the month of December you should do December 1 to January 1.
Following your earlier example should'nt this be November 30 to January 1 ?
Inclusive data for the month of November would be Nov 1 to Dec 1 or Oct 31 to Dec 1? The examples below are conflicting. From my testing, I think it's Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats for Dec 5. Sharon NExpress Shared Catalog On Dec 12, 2008, at 5:57 AM, Nicole Engard wrote:
Last week I updated this document: http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Rep... to include a tip about dates.
"TIP: The statistic reports are exclusive, so, if you want a report to show data for the month of November you should pick October 31 to December 1."
So if you want the month of December you should do December 1 to January 1.
---
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 Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <kdr@ckls.org> wrote:
Josh-
I tried this on Great Bend Public Library's collection and have a few observations.
If the to/from date is the same (12/08 - 12/08), the result is zero.
So I tried 12/08 - 12/09 and then got a figure.
Kathy
At 02:47 AM 12/6/2008, Joshua Ferraro wrote:
Hi Katharine,
On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon <katharin@ci.salinas.ca.us> wrote:
Hello, I am new to the list so sorry if this has been asked before but does anybody have the SQL for a report that would just give me a total of all items circulated on a certain day. Just the number, no titles or types. You can find this out from the Circulation Statistics wizard, located in the Reports area. The path is /cgi-bin/koha/reports/issues_stats.pl
Select Period as a row, and specify the dates in question as From/ To.
Select Library as a Column
Run the report and you will get a chart of circulations per branch for that date range.
Cheers,
-- Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE CEO migration, training, maintenance, support LibLime Featuring Koha Open-Source ILS jmf@liblime.com |Full Demos at http://liblime.com/koha | 1(888)KohaILS _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Kathy Rippel Dept. Head/Consultant--Resource Sharing and Access ; Pathfinder Central (Manager) Central Kansas Library System 1409 Williams Great Bend, KS 67530
(620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax
kdr@ckls.org ******************************************************************************************** I'm currently reading: Redesigning the American Lawn, by F. Herbert Bormann [... et al.] I'm currently listening to: Xenocide, by Orson Scott Card.
_______________________________________________ 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
From my testing, I think it's Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats for Dec 5.
This is right. 11/1 - 12/1 will give you the month of November. Did I use the wrong word (inclusive v. exclusive)? --- 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 Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <smoreland@nekls.org> wrote:
Inclusive data for the month of November would be Nov 1 to Dec 1 or Oct 31 to Dec 1? The examples below are conflicting. From my testing, I think it's Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats for Dec 5.
Sharon NExpress Shared Catalog
On Dec 12, 2008, at 5:57 AM, Nicole Engard wrote:
Last week I updated this document:
http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Rep... to include a tip about dates.
"TIP: The statistic reports are exclusive, so, if you want a report to show data for the month of November you should pick October 31 to December 1."
So if you want the month of December you should do December 1 to January 1.
---
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 Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <kdr@ckls.org> wrote:
Josh-
I tried this on Great Bend Public Library's collection and have a few observations.
If the to/from date is the same (12/08 - 12/08), the result is zero.
So I tried 12/08 - 12/09 and then got a figure.
Kathy
At 02:47 AM 12/6/2008, Joshua Ferraro wrote:
Hi Katharine,
On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon <katharin@ci.salinas.ca.us> wrote:
Hello, I am new to the list so sorry if this has been asked before but does anybody have the SQL for a report that would just give me a total of all items circulated on a certain day. Just the number, no titles or types.
You can find this out from the Circulation Statistics wizard, located in the Reports area. The path is /cgi-bin/koha/reports/issues_stats.pl
Select Period as a row, and specify the dates in question as From/To.
Select Library as a Column
Run the report and you will get a chart of circulations per branch for that date range.
Cheers,
-- Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE CEO migration, training, maintenance, support LibLime Featuring Koha Open-Source ILS jmf@liblime.com |Full Demos at http://liblime.com/koha |1(888)KohaILS _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Kathy Rippel Dept. Head/Consultant--Resource Sharing and Access ; Pathfinder Central (Manager) Central Kansas Library System 1409 Williams Great Bend, KS 67530
(620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax
kdr@ckls.org
******************************************************************************************** I'm currently reading: Redesigning the American Lawn, by F. Herbert Bormann [... et al.] I'm currently listening to: Xenocide, by Orson Scott Card.
_______________________________________________ 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
I think the confusion is because it isn't clear where the inclusive or exclusive property applies. In this case, the query is both inclusive and exclusive, so I'll try to state the description somewhat technically. The question regards date ranges in reports, i.e. a range specified by a lower and upper bound. For statistical reports, the DATE is supplied by the user (like YYYY-MM-DD) but compared against a DATETIME field (like YYYY-MM-DD HH:mm:ss). Thus, the behavior is that the lower bound is inclusive, because even the first second of a day sorts to *after* the DATE string by itself. The same principle applies to exclude the upper bound. Here's how mysql treats this problem, in concrete terms. I'm using the DATE "2007-12-06" against the DATETIME "2007-12-06 17:25:39". Examples: mysql> select "2007-12-06" < "2007-12-06 17:25:39" as lower_bound; +-------------+ | lower_bound | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select "2007-12-06 17:25:39" < "2007-12-06" as upper_bound; +-------------+ | upper_bound | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) NOTE: this applies only to the *statistics* table and other places where we compare DATE vs DATETIME (or TIMESTAMP). When querying *other* tables and the comparison is DATE vs. DATE, you would expect different behavior. Of course "2007-12-06" > "2007-12-06" would fail. In date vs. date, the comparisons *should* be <= and >=, but I cannot say that they are. A lot of the underlying report code has been copied around and I would expect the authors did not adjust accordingly for different behavior that corresponds to different data types. I think Koha *should* evolve to the point where the date specification is the same regardless of the type of report. Probably both ends should be inclusive, such that the date range "2007-12-06" to "2007-12-06" (the same day) would result in a one day range. Currently (for statistics), it results in an empty set. This sounds easy but it gets complicated when you get into the upcoming hourly circulation features that will change a lot of the fields in the DB to be fuller types, and the fact that the guided reports writer targets different tables. But hopefully that settles the question for date ranges against the statistics table. --Joe Atzberger On Mon, Dec 15, 2008 at 7:33 AM, Nicole Engard <nicole.engard@liblime.com>wrote:
From my testing, I think it's Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats for Dec 5.
This is right. 11/1 - 12/1 will give you the month of November. Did I use the wrong word (inclusive v. exclusive)?
---
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 Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <smoreland@nekls.org> wrote:
Inclusive data for the month of November would be Nov 1 to Dec 1 or Oct 31 to Dec 1? The examples below are conflicting. From my testing, I think it's Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats for Dec 5.
Sharon NExpress Shared Catalog
On Dec 12, 2008, at 5:57 AM, Nicole Engard wrote:
Last week I updated this document:
http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Rep...
to include a tip about dates.
"TIP: The statistic reports are exclusive, so, if you want a report to show data for the month of November you should pick October 31 to December 1."
So if you want the month of December you should do December 1 to January 1.
---
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 Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <kdr@ckls.org> wrote:
Josh-
I tried this on Great Bend Public Library's collection and have a few observations.
If the to/from date is the same (12/08 - 12/08), the result is zero.
So I tried 12/08 - 12/09 and then got a figure.
Kathy
At 02:47 AM 12/6/2008, Joshua Ferraro wrote:
Hi Katharine,
On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon <katharin@ci.salinas.ca.us> wrote:
Hello, I am new to the list so sorry if this has been asked before
but
does anybody have the SQL for a report that would just give me a total of all items circulated on a certain day. Just the number, no titles or types.
You can find this out from the Circulation Statistics wizard, located in the Reports area. The path is /cgi-bin/koha/reports/issues_stats.pl
Select Period as a row, and specify the dates in question as From/To.
Select Library as a Column
Run the report and you will get a chart of circulations per branch for that date range.
Cheers,
-- Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE CEO migration, training, maintenance, support LibLime Featuring Koha Open-Source ILS jmf@liblime.com |Full Demos at http://liblime.com/koha |1(888)KohaILS _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Kathy Rippel Dept. Head/Consultant--Resource Sharing and Access ; Pathfinder Central (Manager) Central Kansas Library System 1409 Williams Great Bend, KS 67530
(620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax
kdr@ckls.org
I'm currently reading: Redesigning the American Lawn, by F. Herbert Bormann [... et al.] I'm currently listening to: Xenocide, by Orson Scott Card.
_______________________________________________ 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
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
We have some reports that use DATE and others that use DATETIME, so this is useful. Sharon On Dec 15, 2008, at 3:41 PM, Joe Atzberger wrote:
I think the confusion is because it isn't clear where the inclusive or exclusive property applies. In this case, the query is both inclusive and exclusive, so I'll try to state the description somewhat technically.
The question regards date ranges in reports, i.e. a range specified by a lower and upper bound. For statistical reports, the DATE is supplied by the user (like YYYY-MM-DD) but compared against a DATETIME field (like YYYY-MM-DD HH:mm:ss). Thus, the behavior is that the lower bound is inclusive, because even the first second of a day sorts to *after* the DATE string by itself. The same principle applies to exclude the upper bound.
Here's how mysql treats this problem, in concrete terms. I'm using the DATE "2007-12-06" against the DATETIME "2007-12-06 17:25:39". Examples:
mysql> select "2007-12-06" < "2007-12-06 17:25:39" as lower_bound; +-------------+ | lower_bound | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec)
mysql> select "2007-12-06 17:25:39" < "2007-12-06" as upper_bound; +-------------+ | upper_bound | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)
NOTE: this applies only to the statistics table and other places where we compare DATE vs DATETIME (or TIMESTAMP). When querying *other* tables and the comparison is DATE vs. DATE, you would expect different behavior. Of course "2007-12-06" > "2007-12-06" would fail. In date vs. date, the comparisons *should* be <= and >=, but I cannot say that they are. A lot of the underlying report code has been copied around and I would expect the authors did not adjust accordingly for different behavior that corresponds to different data types.
I think Koha *should* evolve to the point where the date specification is the same regardless of the type of report. Probably both ends should be inclusive, such that the date range "2007-12-06" to "2007-12-06" (the same day) would result in a one day range. Currently (for statistics), it results in an empty set. This sounds easy but it gets complicated when you get into the upcoming hourly circulation features that will change a lot of the fields in the DB to be fuller types, and the fact that the guided reports writer targets different tables.
But hopefully that settles the question for date ranges against the statistics table.
--Joe Atzberger
On Mon, Dec 15, 2008 at 7:33 AM, Nicole Engard <nicole.engard@liblime.com
wrote: From my testing, I think it's Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats for Dec 5.
This is right. 11/1 - 12/1 will give you the month of November. Did I use the wrong word (inclusive v. exclusive)?
---
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/
Inclusive data for the month of November would be Nov 1 to Dec 1 or Oct 31 to Dec 1? The examples below are conflicting. From my testing, I
On Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <smoreland@nekls.org> wrote: think it's
Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats for Dec 5.
Sharon NExpress Shared Catalog
On Dec 12, 2008, at 5:57 AM, Nicole Engard wrote:
Last week I updated this document:
http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Rep... to include a tip about dates.
"TIP: The statistic reports are exclusive, so, if you want a report to show data for the month of November you should pick October 31 to December 1."
So if you want the month of December you should do December 1 to January 1.
---
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 Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <kdr@ckls.org> wrote:
Josh-
I tried this on Great Bend Public Library's collection and have
a few
observations.
If the to/from date is the same (12/08 - 12/08), the result is zero.
So I tried 12/08 - 12/09 and then got a figure.
Kathy
At 02:47 AM 12/6/2008, Joshua Ferraro wrote:
Hi Katharine,
On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon <katharin@ci.salinas.ca.us> wrote:
Hello, I am new to the list so sorry if this has been asked
before but
does anybody have the SQL for a report that would just give me a total of all items circulated on a certain day. Just the number, no titles or types.
You can find this out from the Circulation Statistics wizard, located in the Reports area. The path is /cgi-bin/koha/reports/issues_stats.pl
Select Period as a row, and specify the dates in question as From/To.
Select Library as a Column
Run the report and you will get a chart of circulations per branch for that date range.
Cheers,
-- Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE CEO migration, training, maintenance, support LibLime Featuring Koha Open- Source ILS jmf@liblime.com |Full Demos at http://liblime.com/koha | 1(888)KohaILS _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Kathy Rippel Dept. Head/Consultant--Resource Sharing and Access ; Pathfinder Central (Manager) Central Kansas Library System 1409 Williams Great Bend, KS 67530
(620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax
kdr@ckls.org
I'm currently reading: Redesigning the American Lawn, by F. Herbert Bormann [... et al.] I'm currently listening to: Xenocide, by Orson Scott Card.
_______________________________________________ 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
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
participants (5)
-
Joe Atzberger -
Kathy Rippel -
Nicole Engard -
Olugbenga Adara -
Sharon Moreland