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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
