[Koha] SQL report

Tue Dec 16 11:18:51 NZDT 2008

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 at 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 at 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 at 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/Reports/reports-wizards
> >> 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 at 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 at 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 at 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 at liblime.com |Full Demos at http://liblime.com/koha | 
> 1(888)KohaILS
> >>>> _______________________________________________
> >>>> Koha mailing list
> >>>> Koha at 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 at 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 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
> >
> >
> _______________________________________________
> Koha mailing list
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20081215/6971c92b/attachment-0001.htm 