[Koha] Report Help

[Nicole Engard]

Thank you! That's a step in the right direction :)  I have shared it (in
your name) on the Koha wiki for others to benefit from.

Nicole


On Mon, Apr 22, 2013 at 9:19 PM, Bernardo Gonzalez Kriegel <
bgkriegel at gmail.com> wrote:

> Nicole,
> you want for each guarantor it's guarantees, Q: it's ok to repeat
> guarantor information on each guarantee tuple?
>
> you could use something like this (put any data you want in first select,
> and change order by if needed)
>
> SELECT
>   IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'')
> AS guarantor,
>   IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'')
> AS guarantee
> FROM
> (
>   SELECT *
>   FROM borrowers
>   WHERE guarantorid  IS NOT NULL
> ) AS p
> LEFT JOIN borrowers AS g
>   ON p.guarantorid  = g.borrowernumber
> ORDER BY g.borrowernumber
> ;
>
> Bernardo
>
> --
> Bernardo Gonzalez Kriegel
> bgkriegel at gmail.com
>
>
> On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard <nengard at gmail.com> wrote:
>
>> I'm looking to change this report around.  It shows patrons with their
>> guarantor information.  I want a report that shows me the guarantees for
>> each guarantor instead.
>>
>>
>> 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,
>> ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
>> FROM borrowers p
>> LEFT JOIN accountlines a USING (borrowernumber)
>> LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
>> WHERE p.dateexpiry < NOW()
>> GROUP BY p.borrowernumber
>> ORDER BY p.dateexpiry ASC
>>
>>
>>
>> Nicole
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>