[Koha] Need a little help with an SQL query

[Chris Brown]

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 at 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 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
>>
>