[Koha] Need a little help with an SQL query

[Joe Sikowitz]

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

-- 
Joe Sikowitz
Collaborative Services Librarian
Fenway Library Organization
(617) 989-5031
joe at flo.org