Active borrowers report
A couple years ago Jesse Weaver came up with this report for active borrowers (i.e., patrons checking out materials): SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM old_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode; I'm wondering if the caveat he notes is still true: Because it uses old_issues, it won't include anything that's still checked out; getting around that requires either ugly subqueries or running the report with old_issues and issues and manually combining the results. Is there a relatively easy way to modify the report so it includes patrons who may still have items checked out? Thanks for any assistance! Cab Vinton, Director Sanbornton Public Library Sanbornton, NH "Politeness and consideration for others is like investing pennies and getting dollars back." Thomas Sowell
I've seen UNION used to join those two tables in queries. I'm imaginning something like: SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate,categorycode,borrowernumber FROM old_issues UNION ALL SELECT issuedate,categorycode,borrowernumber FROM issues ) LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode; But I'm no database expert, so use this with care. On Mon, Jan 9, 2012 at 1:12 PM, Cab Vinton <bibliwho@gmail.com> wrote:
A couple years ago Jesse Weaver came up with this report for active borrowers (i.e., patrons checking out materials):
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM old_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode;
I'm wondering if the caveat he notes is still true:
Because it uses old_issues, it won't include anything that's still checked out; getting around that requires either ugly subqueries or running the report with old_issues and issues and manually combining the results.
Is there a relatively easy way to modify the report so it includes patrons who may still have items checked out?
Thanks for any assistance!
Cab Vinton, Director Sanbornton Public Library Sanbornton, NH
"Politeness and consideration for others is like investing pennies and getting dollars back." Thomas Sowell _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Hi, On 01/09/2012 03:30 PM, Mike Hafen wrote:
I've seen UNION used to join those two tables in queries. I'm imaginning something like:
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate,categorycode,borrowernumber FROM old_issues UNION ALL SELECT issuedate,categorycode,borrowernumber FROM issues ) LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode;
A UNION ALL is indeed a valid way to do it. Regards, Galen -- Galen Charlton Director of Support and Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
And now that I've actually tried that query, there are problems with it. Here is a fixed version: SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate, borrowernumber FROM old_issues UNION ALL SELECT issuedate, borrowernumber FROM issues ) AS all_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode On Mon, Jan 9, 2012 at 1:31 PM, Galen Charlton <gmc@esilibrary.com> wrote:
Hi,
On 01/09/2012 03:30 PM, Mike Hafen wrote:
I've seen UNION used to join those two tables in queries. I'm imaginning something like:
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate,categorycode,**borrowernumber FROM old_issues UNION ALL SELECT issuedate,categorycode,**borrowernumber FROM issues ) LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode;
A UNION ALL is indeed a valid way to do it.
Regards,
Galen -- Galen Charlton Director of Support and Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
______________________________**_________________ Koha-devel mailing list Koha-devel@lists.koha-**community.org<Koha-devel@lists.koha-community.org> http://lists.koha-community.**org/cgi-bin/mailman/listinfo/**koha-devel<http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel> website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.**org/ <http://bugs.koha-community.org/>
Awesome! Thank you, Mike -- seems to work perfectly! Cheers, Cab Vinton, Director Sanbornton Public Library Sanbornton, NH "Politeness and consideration for others is like investing pennies and getting dollars back." Thomas Sowell 2012/1/9 Mike Hafen <mdhafen@tech.washk12.org>:
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate, borrowernumber FROM old_issues UNION ALL SELECT issuedate, borrowernumber FROM issues ) AS all_issues
LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode
And you know what, that one isn't in the report library so I added it for you. :) You can see it here: http://wiki.koha-community.org/wiki/SQL_Reports_Library#Active_Patrons Thanks! Liz Rea lrea@nekls.org On Jan 9, 2012, at 2:36 PM, Mike Hafen wrote:
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate, borrowernumber FROM old_issues UNION ALL SELECT issuedate, borrowernumber FROM issues ) AS all_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode
Cool. Though I would prefer Jesse Weaver get all/joint credit for it. I really just changed his sql a bit. On Mon, Jan 9, 2012 at 2:31 PM, Liz Rea <lrea@nekls.org> wrote:
And you know what, that one isn't in the report library so I added it for you. :)
You can see it here: http://wiki.koha-community.org/wiki/SQL_Reports_Library#Active_Patrons
Thanks!
Liz Rea lrea@nekls.org
On Jan 9, 2012, at 2:36 PM, Mike Hafen wrote:
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate, borrowernumber FROM old_issues UNION ALL SELECT issuedate, borrowernumber FROM issues ) AS all_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode
Wow, that's an old report. Glad you got some use out of it, and found a way to make it better! On the other hand, should we consider promoting the use of the `statistics` table? Though I've been out of the loop, I think it was easier to design reports like this around. Spent many frustrating hours and LEFT JOINs trying to twist the `issues` and `old_issues` tables into the output the librarians asked for. 2012/1/9 Mike Hafen <mdhafen@tech.washk12.org>
Cool. Though I would prefer Jesse Weaver get all/joint credit for it. I really just changed his sql a bit.
On Mon, Jan 9, 2012 at 2:31 PM, Liz Rea <lrea@nekls.org> wrote:
And you know what, that one isn't in the report library so I added it for you. :)
You can see it here: http://wiki.koha-community.org/wiki/SQL_Reports_Library#Active_Patrons
Thanks!
Liz Rea lrea@nekls.org
On Jan 9, 2012, at 2:36 PM, Mike Hafen wrote:
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM ( SELECT issuedate, borrowernumber FROM old_issues UNION ALL SELECT issuedate, borrowernumber FROM issues ) AS all_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Jesse Weaver
participants (5)
-
Cab Vinton -
Galen Charlton -
Jesse -
Liz Rea -
Mike Hafen