[Koha] SQL Report help

[Jeramey Valley]

Thanks so much, was exactly what I needed.

Final solution:

SELECT p.cardnumber, p.surname, p.firstname,
DATE_FORMAT(c.issuedate, "%m/%d/%Y") AS Issue_Date, DATE_FORMAT(c.date_due, "%m/%d/%Y") AS Due_Date, h.attribute AS teacher,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
b.title, b.author, i.itemcallnumber, i.replacementprice, ExtractValue(bi.marcxml,'//datafield[@tag="020"]/subfield[@code="c"]') AS price, b.biblionumber, i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
LEFT JOIN borrower_attributes g ON (p.borrowernumber=g.borrowernumber)
LEFT JOIN borrower_attributes h ON (p.borrowernumber=h.borrowernumber)
LEFT JOIN biblioitems bi ON (b.biblionumber = bi.biblionumber)
WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>> AND h.code="HOMEROOM"
ORDER BY g.attribute, h.attribute, p.surname ASC

--

Regards, 

Jeramey Valley
Network Manager, Bullock Creek Schools
valleyj at bcreek.org




> On Apr 24, 2015, at 2:04 PM, Barton Chittenden <barton at bywatersolutions.com> wrote:
> 
> Jeremy,
> 
> You can join biblioitems to biblio using biblionumber. Add this right
> before your WHERE clause:
> 
> LEFT JOIN biblioitems bi ON (b.biblionumber = bi.biblionumber)
> 
> Once you've done that, you can add
> 
> b.biblionumber,
> ExtractValue(bi.marcxml,'//datafield[@tag="020"]/subfield[@code="c"]')
> AS heading
> 
> As fields to SELECT,
> 
> Then add
> 
> AND length(ExtractValue(bi.marcxml,
> '//datafield[@tag="020"]/subfield[@code="c"]')) != 0
> 
> as part of your WHERE clause.
> 
> I'll let you work out the 'order by' part - you'll know better than I
> will how you want your results ordered.
> 
> --Barton
> 
> On Fri, Apr 24, 2015 at 1:44 PM, Jeramey Valley <valleyj at bcreek.org> wrote:
>> Sure there’s a simple solution, not very good with SQL…
>> 
>> This query works great:
>> 
>> SELECT p.cardnumber, p.surname, p.firstname,
>> DATE_FORMAT(c.issuedate, "%m/%d/%Y") AS Issue_Date, DATE_FORMAT(c.date_due, "%m/%d/%Y") AS Due_Date, h.attribute AS teacher,
>> (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
>> b.title, b.author, i.itemcallnumber, i.replacementprice,
>> i.barcode
>> FROM borrowers p
>> LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
>> LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
>> LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
>> LEFT JOIN borrower_attributes g ON (p.borrowernumber=g.borrowernumber)
>> LEFT JOIN borrower_attributes h ON (p.borrowernumber=h.borrowernumber)
>> WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
>> date_due)) >= <<Days overdue>> AND h.code="HOMEROOM"
>> ORDER BY g.attribute, h.attribute, p.surname ASC
>> 
>> 
>> I need to also add to the above:
>> 
>> SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="020"]/subfield[@code="c"]') AS heading
>> FROM biblioitems
>> WHERE length(ExtractValue(marcxml, '//datafield[@tag="020"]/subfield[@code="c"]')) != 0
>> ORDER BY heading
>> 
>> Basically need to combine the two, how to do so?
>> 
>> --
>> 
>> Regards,
>> 
>> Jeramey Valley
>> Network Manager, Bullock Creek Schools
>> valleyj at bcreek.org
>> 
>> 
>> 
>> 
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha