SQL report

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL report

Katharine Dixon

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.

 

 Thanks in advance,

Katharine Dixon

Senior Technician

Salinas Public Library

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Uma Mukkamala
I am trying to install KOHA 3.0 on linux redhat, please let me know the required applications and the exact sequence to install.

Thanks & Regards,
Uma Sankar
KSS Development,
M248, MotechHouse,
Mogra Village, Off Old Nagardas Road,
Andheri East
Desk    : +91 66811533
Mobile : +91 9967007817

----- Original Message -----
From: "Katharine Dixon" <[hidden email]>
To: [hidden email]
Sent: Saturday, December 6, 2008 4:52:29 AM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: [Koha] SQL report





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.

 

 Thanks in advance,

Katharine Dixon

Senior Technician

Salinas Public Library

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
"Confidentiality Warning: This message and any attachments are intended only for the use of the intended recipient(s).
are confidential. and may be privileged. If you are not the intended recipient. you are hereby notified that any
review. re-transmission. conversion to hard copy. copying. circulation or other use of this message and any attachments is
strictly prohibited. If you are not the intended recipient. please notify the sender immediately by return email.
and delete this message and any attachments from your system.

Virus Warning: Although the company has taken reasonable precautions to ensure no viruses are present in this email.
The company cannot accept responsibility for any loss or damage arising from the use of this email or attachment."
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Krishnan Mani
Uma,

When you download and extract Koha, you will find multiple text files named INSTALL.* (such as INSTALL.fedora7) that tell you how to install for a particular Linux flavor. Try them first. You can also try the guides at kohadocs.org

Also, instead of responding to an unrelated thread, you probably want to start a new one to save yourself and others some confusion.

Thanks and regards,

krishnan mani
Pune, India

--- On Sat, 6/12/08, Uma Mukkamala <[hidden email]> wrote:
From: Uma Mukkamala <[hidden email]>
Subject: Re: [Koha] SQL report
To: "Katharine Dixon" <[hidden email]>
Cc: [hidden email]..co.nz
Date: Saturday, 6 December, 2008, 11:01 AM

I am trying to install KOHA 3.0 on linux redhat, please let me know the required
applications and the exact sequence to install.

Thanks & Regards,
Uma Sankar
KSS Development,
M248, MotechHouse,
Mogra Village, Off Old Nagardas Road,
Andheri East
Desk : +91 66811533
Mobile : +91 9967007817

----- Original Message -----
From: "Katharine Dixon" <[hidden email]>
To: [hidden email]
Sent: Saturday, December 6, 2008 4:52:29 AM GMT +05:30 Chennai, Kolkata,
Mumbai, New Delhi
Subject: [Koha] SQL report





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.

 

 Thanks in advance,

Katharine Dixon

Senior Technician

Salinas Public Library

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
"Confidentiality Warning: This message and any attachments are intended
only for the use of the intended recipient(s).
are confidential. and may be privileged. If you are not the intended recipient.
you are hereby notified that any
review. re-transmission. conversion to hard copy. copying. circulation or other
use of this message and any attachments is
strictly prohibited. If you are not the intended recipient. please notify the
sender immediately by return email.
and delete this message and any attachments from your system.

Virus Warning: Although the company has taken reasonable precautions to ensure
no viruses are present in this email.
The company cannot accept responsibility for any loss or damage arising from
the use of this email or attachment."
_______________________________________________
Koha mailing list
[hidden email]..nz
http://lists.katipo.co.nz/mailman/listinfo/koha


Add more friends to your messenger and enjoy! Invite them now.
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report [of number of issues]

Rick Welykochy
In reply to this post by Katharine Dixon
Katharine Dixon 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.

If by circulated you mean issued/withdrawn:

select count(*) from issues where issuedate = '2008-04-30';



cheers
rickw


--
_________________________________
Rick Welykochy || Praxis Services

Finster's Law: A closed mouth gathers no feet.
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report [of number of issues]

Chris Cormack-6
On Sat, Dec 6, 2008 at 9:11 PM, Rick Welykochy <[hidden email]> wrote:

> Katharine Dixon 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.
>
> If by circulated you mean issued/withdrawn:
>
> select count(*) from issues where issuedate = '2008-04-30';
>
This won't work for Koha 3.0 and even older versions of Koha where
past issues are expunged.
For Koha 3.0 only current issues are held in the issues table. So this
will only count items that are still on issue.

For 3.0 (and this will work in all versions of 2.2 also)

SELECT count(*) FROM statistics WHERE type = 'issue' AND datetime >
'2008-04-30' AND datetime < '2008-05-01'

(For the 30th of april 2008)

Chris
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Joshua Ferraro-3
In reply to this post by Katharine Dixon
Hi Katharine,

On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon
<[hidden email]> 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
[hidden email] |Full Demos at http://liblime.com/koha |1(888)KohaILS
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report [of number of issues]

Owen Leonard-4
In reply to this post by Chris Cormack-6
> 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
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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


_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Kathy Rippel
In reply to this post by Joshua Ferraro-3
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
><[hidden email]> 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
>[hidden email] |Full Demos at http://liblime.com/koha |1(888)KohaILS
>_______________________________________________
>Koha mailing list
>[hidden email]
>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

[hidden email]
********************************************************************************************
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
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Nicole Engard
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
[hidden email]
AIM/Y!/Skype: nengard

http://liblime.com
http://blogs.liblime.com/open-sesame/



On Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <[hidden email]> 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
>><[hidden email]> 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
>>[hidden email] |Full Demos at http://liblime.com/koha |1(888)KohaILS
>>_______________________________________________
>>Koha mailing list
>>[hidden email]
>>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
>
> [hidden email]
> ********************************************************************************************
> 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
> [hidden email]
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Olugbenga Adara
Hello,


--- On Fri, 12/12/08, Nicole Engard <[hidden email]> wrote:

> From: Nicole Engard <[hidden email]>
> Subject: Re: [Koha] SQL report
> To: "Kathy Rippel" <[hidden email]>
> Cc: [hidden email], "Katharine Dixon" <[hidden email]>
> 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/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.
>


Following your earlier example should'nt this be November 30 to January 1 ?


     
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Sharon Moreland
In reply to this post by Nicole Engard
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
> [hidden email]
> AIM/Y!/Skype: nengard
>
> http://liblime.com
> http://blogs.liblime.com/open-sesame/
>
>
>
> On Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <[hidden email]> 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
>>> <[hidden email]> 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
>>> [hidden email] |Full Demos at http://liblime.com/koha |
>>> 1(888)KohaILS
>>> _______________________________________________
>>> Koha mailing list
>>> [hidden email]
>>> 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
>>
>> [hidden email]
>> ********************************************************************************************
>> 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
>> [hidden email]
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
> _______________________________________________
> Koha mailing list
> [hidden email]
> http://lists.katipo.co.nz/mailman/listinfo/koha

_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Nicole Engard
>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
[hidden email]
AIM/Y!/Skype: nengard

http://liblime.com
http://blogs.liblime.com/open-sesame/



On Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <[hidden email]> 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
>> [hidden email]
>> AIM/Y!/Skype: nengard
>>
>> http://liblime.com
>> http://blogs.liblime.com/open-sesame/
>>
>>
>>
>> On Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <[hidden email]> 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
>>>> <[hidden email]> 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
>>>> [hidden email] |Full Demos at http://liblime.com/koha |1(888)KohaILS
>>>> _______________________________________________
>>>> Koha mailing list
>>>> [hidden email]
>>>> 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
>>>
>>> [hidden email]
>>>
>>> ********************************************************************************************
>>> 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
>>> [hidden email]
>>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>> _______________________________________________
>> Koha mailing list
>> [hidden email]
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Joe Atzberger
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 <[hidden email]> 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
[hidden email]
AIM/Y!/Skype: nengard

http://liblime.com
http://blogs.liblime.com/open-sesame/



On Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <[hidden email]> 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
>> [hidden email]
>> AIM/Y!/Skype: nengard
>>
>> http://liblime.com
>> http://blogs.liblime.com/open-sesame/
>>
>>
>>
>> On Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <[hidden email]> 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
>>>> <[hidden email]> 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
>>>> [hidden email] |Full Demos at http://liblime.com/koha |1(888)KohaILS
>>>> _______________________________________________
>>>> Koha mailing list
>>>> [hidden email]
>>>> 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
>>>
>>> [hidden email]
>>>
>>> ********************************************************************************************
>>> 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
>>> [hidden email]
>>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>> _______________________________________________
>> Koha mailing list
>> [hidden email]
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha


_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL report

Sharon Moreland
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 <[hidden email]> 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
[hidden email]
AIM/Y!/Skype: nengard

http://liblime.com
http://blogs.liblime.com/open-sesame/



On Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <[hidden email]> 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
>> [hidden email]
>> AIM/Y!/Skype: nengard
>>
>> http://liblime.com
>> http://blogs.liblime.com/open-sesame/
>>
>>
>>
>> On Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <[hidden email]> 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
>>>> <[hidden email]> 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
>>>> [hidden email] |Full Demos at http://liblime.com/koha |1(888)KohaILS
>>>> _______________________________________________
>>>> Koha mailing list
>>>> [hidden email]
>>>> 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
>>>
>>> [hidden email]
>>>
>>> ********************************************************************************************
>>> 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
>>> [hidden email]
>>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>> _______________________________________________
>> Koha mailing list
>> [hidden email]
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha



_______________________________________________
Koha mailing list
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha