PAGE IN PROGRESS – CHECK BACK LATER FOR MORE
On this page I’ll add Koha SQL reports over time. Keep in mind that I only have basic SQL knowledge that I’ve learned on the job as needed, so I may not be able to answer many questions or assist in creating different reports that what is on this page. However, rest assured every report posted here works for what it was designed for. Whether or not it works for you depends largely on what version SQL your Koha installation uses.
My Koha SQL version: mysql Ver 14.14
All items with datelastseen
This report is used for inventory purposes mostly. The datelastseen attribute is used to determine what is likely missing or what was not picked up in an inventory scan. It includes lost and withdrawn statuses to see what items may legitimately have an older last seen date than your most recent inventory.
SELECT biblio.title,biblio.author,biblio.copyrightdate,items.barcode, items.homebranch,items.datelastseen, items.itemcallnumber,items.location,items.itemtype,items.itemlost, items.wthdrawn,items.onloan FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) ORDER BY items.datelastseen
This report is used for reporting how many items we added during a particular timeframe. The SQL itself doesn’t allow for specifying a date range (although that’s possible), we just export the whole report to Excel then sort by accession date and pick out what we’re interested in. Note: you will need to specify which holding branch you are querying or delete the “where” phrase altogether to include all branches (or if you only have one branch)
SELECT biblio.title,items.dateaccessioned FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.homebranch=’MCAMAIN’ ORDER BY items.dateaccessioned desc
All items of a certain type
Similar to the accession list report, we use this to see how many items of a specific type we have added over a period of time by exporting and sorting this report. It can be used for a variety of purposes though because it’s just a list of all items of whatever item type you set based on your collection. Here, it’s set to ‘GRAP NOVEL’, our graphic novel item type.
SELECT items.itemtype,items.itemcallnumber,items.dateaccessioned FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itemtype=’GRAP NOVEL’ ORDER BY items.itemcallnumber asc
Items from a certain location, minus checkouts
This specific report was used to track down books with the location type of “New”, but that weren’t actually on our new shelf. For any number of reasons we had books that naturally rotated off of our “new” shelf, but didn’t have their location status changed, and this report generated a list of items that we could use to then shelf check our non-new collection. Of note is that this list excludes any items that are checked out with the phrase “items.onloan IS NULL” which can be useful in other reports too.
SELECT items.itemcallnumber,biblio.title,items.barcode,items.location,items.itemtype,items.itemlost,items.datelastseen FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.location=’NEW’ AND items.onloan IS NULL ORDER BY items.itemcallnumber asc