[Koha] Need a little help with an SQL query

[Barton Chittenden]

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