[Koha] Report Help

[Nicole Engard]

Thank you so much - so simple!! :)


On Wed, Apr 10, 2013 at 2:45 PM, Bernardo Gonzalez Kriegel <
bgkriegel at gmail.com> wrote:

> Nicole,
> what you need to match is p(atron).guarantorid with
> g(uarantor).borrowernumber,
> not g(uarantor).guarantorid with p(atron).borrowernumber
> that is what causing you to get an inverted list.
>
> For example this query will list all guarantees with corresponding
> guarantor (if not null),
>
> SELECT
>     patron.surname, patron.firstname, patron.relationship,
> patron.guarantorid,
>     guarantor.borrowernumber, guarantor.surname, guarantor.firstname
> FROM
>     borrowers as patron
> LEFT JOIN
>     borrowers as guarantor
> ON ( patron.guarantorid = guarantor.borrowernumber )
> WHERE patron.guarantorid IS NOT NULL;
>
>
> Regards,
> Bernardo
>
> --
> Bernardo Gonzalez Kriegel
> bgkriegel at gmail.com
>
>
> On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard <nengard at gmail.com> wrote:
>
>> Sorry - I was wrong - not patrons with overdues, but expired patrons -
>> that's what I'm looking for.
>>
>>
>> On Wed, Apr 10, 2013 at 1:25 PM, Nicole Engard <nengard at gmail.com> wrote:
>>
>> > I'm trying to get a report to show patrons with overdues and if there
>> is a
>> > guarantor I want that to show too. But what's happening is the
>> Guarantees
>> > are showing as the Guarantors and the Guarantor is showing 2 times
>> cause he
>> > has 2 Guarantees - can someone help me with my SQL:
>> >
>> >
>> > SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
>> > p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname,
>> '
>> > (', g.cardnumber, ')'),'') as guarantor, p.relationship,
>> > FORMAT(SUM(a.amountoutstanding),2) as due
>> > FROM borrowers p
>> > LEFT JOIN accountlines a USING (borrowernumber)
>> > left join borrowers g on (g.guarantorid=p.borrowernumber)
>> > WHERE p.dateexpiry < NOW() and p.surname = 'Meave'
>> > group by p.borrowernumber, g.borrowernumber
>> > ORDER BY p.dateexpiry asc ;
>> >
>> >
>> >
>> >
>> >
>> >
>> +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+
>> > | categorycode | dateofbirth | cardnumber | surname | firstname     |
>> > dateexpiry | guarantor                       | relationship | due    |
>> >
>> >
>> +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+
>> > | TRES         | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
>> > 2008-12-04 |                                 | parent       | 5.90   |
>> > | TRES         | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
>> > 2008-12-04 |                                 | parent       | 2.30   |
>> > | STRICT       | 1956-09-23  | P0012127   | Meave   | Jose          |
>> > 2008-12-04 | Meave, Melina-Maria (P0012129)  |              | 351.53 |
>> > | STRICT       | 1956-09-23  | P0012127   | Meave   | Jose          |
>> > 2008-12-04 | Meave, Phoenix-Jonas (P0012128) |              | 351.53 |
>> >
>> >
>> +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+
>> >
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>