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 <neng...@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha