That is interesting. I'm using Koha 18.05 and it doesn't require me to enter the dates twice. You could just set the query up so that it looks at the past six months from the current day instead: SELECT b.cardnumber, b.surname, b.firstname, i.issuedate AS 'Current Issue', o.issuedate AS 'Old Issue' FROM borrowers b LEFT JOIN issues i ON (b.borrowernumber = i.borrowernumber) LEFT JOIN old_issues o ON (b.borrowernumber = o.borrowernumber) WHERE i.issuedate >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) OR o.issuedate
= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY b.cardnumber
Joe On Thu, Feb 21, 2019 at 4:51 AM Chris Brown <chris@stayawake.co.uk> wrote:
Hi Joe,
Many thanks for your help. That seems to be working. However your revised version does require the "from" and "to" dates to be entered twice when you run it. Is there a way to avoid that? (It's not a big deal as we won't be running the report very often)
Thanks again,
Chris Brown
On Wed, Feb 20, 2019 at 5:20 PM Joe Sikowitz <joe@flo.org> wrote:
Ignore the last one. This one will work better. Hope this is helpful.
SELECT b.cardnumber, b.surname, b.firstname, i.issuedate AS 'Current Issue', o.issuedate AS 'Old Issue' FROM borrowers b LEFT JOIN issues i ON (b.borrowernumber = i.borrowernumber) LEFT JOIN old_issues o ON (b.borrowernumber = o.borrowernumber) WHERE i.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> OR o.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY b.cardnumber
On Wed, Feb 20, 2019 at 12:11 PM Joe Sikowitz <joe@flo.org> wrote:
You could also add in old issues which you probably would want so that you get returned items:
SELECT b.cardnumber, b.surname, b.firstname, i.issuedate AS 'Current Issue', o.issuedate AS 'Old Issue' FROM borrowers b INNER JOIN issues i ON (b.borrowernumber = i.borrowernumber) INNER JOIN old_issues o ON (b.borrowernumber = o.borrowernumber) WHERE i.issuedate or o.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY b.cardnumber
Joe
On Wed, Feb 20, 2019 at 11:35 AM Joe Sikowitz <joe@flo.org> wrote:
Something like this may work:
SELECT b.cardnumber, b.surname, b.firstname, i.issuedate FROM borrowers b INNER JOIN issues i ON (b.borrowernumber = i.borrowernumber) WHERE i.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY b.cardnumber
Joe
On Wed, Feb 20, 2019 at 11:24 AM Chris Brown <chris@stayawake.co.uk> wrote:
Gentle Reader,
We want to get a list of all patrons for who we do *not *have email addresses but who are "active" (for example, have checked a book out in the last 6 months)
I know how to get a list of patrons with no email address but my knowledge of SQL and the database schema aren't good enough to write the "AND have checked a book out in the last 6 months" part of the query.
Is there a kind soul out there who could help? We are using Koha 17.11
Thanks and Best Regards,
Chris Brown _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Joe Sikowitz Collaborative Services Librarian Fenway Library Organization (617) 989-5031 joe@flo.org
-- Joe Sikowitz Collaborative Services Librarian Fenway Library Organization (617) 989-5031 joe@flo.org
-- Joe Sikowitz Collaborative Services Librarian Fenway Library Organization (617) 989-5031 joe@flo.org
-- Joe Sikowitz Collaborative Services Librarian Fenway Library Organization (617) 989-5031 joe@flo.org