[Koha] Report Help

[Manos PETRIDIS]

As the original request was "I'm trying to get a report to show patrons with
overdues and if there is a guarantor I want that to show too", might I
propose the following modification to Bernardo Gonzalez Kriegel's reply, so
that all patrons are shown in a single report:

select * from
(
SELECT
    patron.borrowernumber 	as borrower, 
    patron.surname 		as Surname, 
    patron.firstname 		as Firstname, 
    patron.relationship 	as relationship, 
    guarantor.borrowernumber	as guarantor, 
    guarantor.surname		as guarantor_Surname, 
    guarantor.firstname		as guarantor_Firstname
FROM    
    borrowers as patron
LEFT JOIN
    borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber ) WHERE
patron.guarantorid IS NOT NULL

UNION

SELECT
    patron.borrowernumber 	as borrower, 
    patron.surname 		as Surname, 
    patron.firstname 		as Firstname, 
    patron.relationship 	as relationship, 
    '-'	as guarantor, 
    '-'	as guarantor_Surname, 
    '-'	as guarantor_Firstname
FROM
    borrowers as patron
WHERE patron.guarantorid IS NULL
)   tmp
order by tmp.Surname, tmp.Firstname;


Kind regards,
Manos Petridis


-----Original Message-----
From: koha-bounces at lists.katipo.co.nz
[mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Bernardo Gonzalez
Kriegel
Sent: Wednesday, April 10, 2013 9:45 PM
To: Nicole Engard
Cc: Koha
Subject: Re: [Koha] Report Help

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
>
_______________________________________________
Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha