[Koha] Need a little help with an SQL query

[Chris Brown]

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
>