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