[Koha] SQL report [of number of issues]

[Joe Atzberger]

Actually it is slightly trickier than that with the statistics table.  You
are passing it a DATE value (YYYY-MM-DD) and the "datetime" field is
(reasonably enough) a DATETIME field (YYYY-MM-DD hh:mm::ss).  So this has an
interesting implication: it matches on comparison for lower bound only.  The
reason is that on the same date, ANY datetime sorts to after the date value
with NO time.

mysql> select "2007-12-06 17:25:39" < "2007-12-06";
+--------------------------------------+
| "2007-12-06 17:25:39" < "2007-12-06" |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select "2007-12-06 17:25:39" > "2007-12-06";
+--------------------------------------+
| "2007-12-06 17:25:39" > "2007-12-06" |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

In my opinion, this is actually one version of what users would want, so
they can put 9/1-10/1 and don't have to remember how many days September
had.  Note that this is data dependent, since the other report interfaces
that query different tables (with regular date fields) will not behave this
way on their own.  This other behavior is probably what you were thinking
of, Owen.

The alternative, probably more correct approach would be to make both dates
inclusive, such that you could limit to one day by specifying it as both
lower and upper bound.  This would require you to know how many days
September had, but since we have a Calendar picker... it can remember for
you.  In the end, I don't think users care too much what logic is applied as
long as it is documented and they can make efficient use of it.

--Joe

On Sat, Dec 6, 2008 at 10:35 AM, Owen Leonard <oleonard at myacpl.org> wrote:

> > SELECT count(*) FROM statistics WHERE type = 'issue' AND datetime >
> > '2008-04-30' AND datetime < '2008-05-01'
>
> Comparing this method with Josh's instructions for running the report in
> Koha:
>
> Koha reports 40705 circulations for all branches during the month of July
> 2008.
>
> I ran this query in mysql:
>
> select count(*) from statistics WHERE type = 'issue' AND datetime >
> '2008-06-30' AND datetime < '2008-08-01';
>
> ...and got 44227. It looks like the reports interface in Koha is using
> the same logic as the SQL statement, so if you choose "Jul 1" through
> "Jul 31" on the date-pickers the report will not include circulations
> from those dates. Selecting "June 30" and "Aug. 1" as the start and
> end dates produces results that match the SQL.
>
>  -- Owen
> --
> Web Developer
> Athens County Public Libraries
> http://www.myacpl.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20081208/af06b02a/attachment.htm