[Koha] Need a little help with an SQL query

[Joe Sikowitz]

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