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