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 <ch...@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 < > bar...@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 <j...@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 <ch...@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 <j...@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 <j...@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 <j...@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 <ch...@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 >>> >>>> j...@flo.org >>> >>>> >>> >>> >>> >>> >>> >>> -- >>> >>> Joe Sikowitz >>> >>> Collaborative Services Librarian >>> >>> Fenway Library Organization >>> >>> (617) 989-5031 >>> >>> j...@flo.org >>> >>> >>> >> >>> >> >>> >> -- >>> >> Joe Sikowitz >>> >> Collaborative Services Librarian >>> >> Fenway Library Organization >>> >> (617) 989-5031 >>> >> j...@flo.org >>> >> >>> > >>> >>> -- >>> Joe Sikowitz >>> Collaborative Services Librarian >>> Fenway Library Organization >>> (617) 989-5031 >>> j...@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 j...@flo.org _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
