[Koha] Re: Report Help

[Msgr. Marion J. Makarewicz]

Perhaps this can help set up where 5 is the number of years you choose. 

 

The YEAR() function trims the dates returned in the datelastborrowed field
and NOW() gives the present date. CURDATE() also is a possible function to
use. DATEDIFF() can give you the difference in days.

 

SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber, 

i.barcode, i.datelastborrowed, i.issues as totalcheckouts, 

i.dateaccessioned 

FROM items i 

LEFT JOIN issues 

USING (itemnumber) 

LEFT JOIN biblio b 

USING (biblionumber) 

WHERE i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) AND
YEAR(NOW())-YEAR(i.datelastborrowed) > 5

UNION SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber, 

i.barcode, i.datelastborrowed, i.issues as totalcheckouts, 

i.dateaccessioned 

FROM items i 

LEFT JOIN issues 

USING (itemnumber) 

LEFT JOIN biblio b 

USING (biblionumber) 

WHERE i.itemnumber NOT IN (SELECT old_issues.itemnumber FROM old_issues) AND
YEAR(NOW())-YEAR(i.datelastborrowed) > 5;

 

Marion

 

> Can someone help me out here. I need to alter this to show only titles 
>that haven't circulated in X number of years.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.katipo.co.nz/pipermail/koha/attachments/20110817/10e9e9d2/attachment.html>