Need a little help with an SQL query
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
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
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
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
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
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
This was added in 18.05. There is a way to fudge it using SQL variables, but it's such a pain to write that you're better double-entering the values until you can upgrade to 18.05. if you *are* using 18.05, then you'll just need to make sure that whatever you want repeated, e.g. BETWEEN <<start date>> AND <<end date>> is repeated *exactly*. There's a piece of terminology that we use at ByWater ... we call '<<' and '>>' 'hungry alligators' (because '<<' and '>>' look like the open mouths of alligators)... it's easier to talk about than 'greater-than greater-than' ... 'less-than less-than'. So... in 18.05 forward if you repeat a pair of strings in hungry alligators, then Koha will only prompt once for the value, and populate it the same way both times. On Thu, Feb 21, 2019 at 9:11 AM Joe Sikowitz <joe@flo.org> wrote:
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 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Barton and Joe, All interesting discussion, and many thanks for the help and interest .... but putting the dates in twice is not a big deal for me. Acting on the results of our query (i.e. chasing up those active patrons for who we don't have email addresses) is many magnitudes more effort than running the query in the first place. Entirely out of curiosity, does this variant actually work? WHERE i.issuedate or o.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> I'm no SQL expert but the computer programmer in me is dubious of the way the OR and BETWEEN operators would interact Best Regards, Chris Brown On Thu, Feb 21, 2019 at 2:21 PM Barton Chittenden < barton@bywatersolutions.com> wrote:
This was added in 18.05.
There is a way to fudge it using SQL variables, but it's such a pain to write that you're better double-entering the values until you can upgrade to 18.05.
if you *are* using 18.05, then you'll just need to make sure that whatever you want repeated, e.g. BETWEEN <<start date>> AND <<end date>> is repeated *exactly*.
There's a piece of terminology that we use at ByWater ... we call
'<<' and '>>'
'hungry alligators' (because '<<' and '>>' look like the open mouths of alligators)... it's easier to talk about than 'greater-than greater-than' ... 'less-than less-than'.
So... in 18.05 forward if you repeat a pair of strings in hungry alligators, then Koha will only prompt once for the value, and populate it the same way both times.
On Thu, Feb 21, 2019 at 9:11 AM Joe Sikowitz <joe@flo.org> wrote:
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 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
That doesn't work. It seems to be implicitly saying find rows where i.issuedate IS NOT NULL OR o.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>. Joe On Thu, Feb 21, 2019 at 1:05 PM Chris Brown <chris@stayawake.co.uk> wrote:
Hi Barton and Joe,
All interesting discussion, and many thanks for the help and interest .... but putting the dates in twice is not a big deal for me. Acting on the results of our query (i.e. chasing up those active patrons for who we don't have email addresses) is many magnitudes more effort than running the query in the first place.
Entirely out of curiosity, does this variant actually work?
WHERE i.issuedate or o.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
I'm no SQL expert but the computer programmer in me is dubious of the way the OR and BETWEEN operators would interact
Best Regards,
Chris Brown
On Thu, Feb 21, 2019 at 2:21 PM Barton Chittenden < barton@bywatersolutions.com> wrote:
This was added in 18.05.
There is a way to fudge it using SQL variables, but it's such a pain to write that you're better double-entering the values until you can upgrade to 18.05.
if you *are* using 18.05, then you'll just need to make sure that whatever you want repeated, e.g. BETWEEN <<start date>> AND <<end date>> is repeated *exactly*.
There's a piece of terminology that we use at ByWater ... we call
'<<' and '>>'
'hungry alligators' (because '<<' and '>>' look like the open mouths of alligators)... it's easier to talk about than 'greater-than greater-than' ... 'less-than less-than'.
So... in 18.05 forward if you repeat a pair of strings in hungry alligators, then Koha will only prompt once for the value, and populate it the same way both times.
On Thu, Feb 21, 2019 at 9:11 AM Joe Sikowitz <joe@flo.org> wrote:
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 _______________________________________________ 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
participants (3)
-
Barton Chittenden -
Chris Brown -
Joe Sikowitz