[Koha] SQL edit for Non-circulating items report

[Tim McMahon]

The ISBN is in the biblioitems table.  Change b.isbn to bi.isbn and add 
LEFT JOIN biblionitems bi USING (biblionumber) before the line that 
starts with WHERE.

On 7/31/20 2:12 PM, David Sale wrote:
> Hello,
>
> Our library is engaged in a weeding project that requires a SQL report for Koha to be customized.
>
> The report we are attempting to customize was written by ByWater Solutions, and it generates a list of items "not seen" in X years, by branch and shelf location.
>
> The existing report is written in SQL as follows:
>
> SELECT i.itemcallnumber, b.biblionumber, b.title, b.author, b.copyrightdate,
>         i.barcode, i.datelastseen, i.issues AS totalcheckouts,
>         i.dateaccessioned
> FROM items i
> LEFT JOIN issues
> USING (itemnumber)
> LEFT JOIN biblio b
> USING (biblionumber)
> WHERE homebranch = <<Home Library|branches>> AND location = <<Shelving location|LOC>> AND i.withdrawn=0 AND i.itemlost=0 AND i.damaged=0 AND i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues)
>        AND  YEAR(NOW())-YEAR(i.datelastseen) > <<Years NOT seen>>
> ORDER BY i.itemcallnumber ASC
>
> We would like to modify this report so that in addition to returning the author, title and copyright date, it also returns the ISBN.
>
> However, adding "b.isbn" to the SELECT list (as with "b.title", "b.author", etc.) breaks the report and renders it non-functional.
>
> Can anyone suggest how the SQL given above should be modified to create a functional report that will generate a list of items "not seen" in X years, by branch and shelf location, and that will return the ISBN *in addition to* the title, author, copyright date and barcode for each item listed in the report?
>
> Thanks,
> David Sale
> Coastline Library Network
> Coos/Curry Counties, Oregon
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

-- 
Tim McMahon
West Liberty Public Library
319-627-2084