[Koha] Report Help

[Nicole Engard]

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