Catalog/Item Reports

From Koha Wiki
Jump to navigation Jump to search

Reports about anything to do with item records in the catalog

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Return to the SQL Reports Library

Item Counts

Count of all items

  • Developer: Michael Hafen
  • Module: Catalog
  • Purpose: Count of all items with barcodes
  • Status: Complete
  SELECT COUNT(barcode) AS Count FROM items WHERE barcode <> '' AND barcode IS NOT NULL

Count of items in collection, select date range

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Show collection by item type. Includes deleted bibs and items.
  • Status: Complete
SELECT
    COALESCE( i.itype, di.itype ) as itype,
    COUNT(*)
FROM
    (select biblionumber from biblio union select biblionumber from deletedbiblio ) b
    left join items i using (biblionumber)
    left join deleteditems di using (biblionumber)
WHERE
    ( i.timestamp is not NULL OR  date(di.timestamp) >= <<item present between | date>> )
    AND date( COALESCE( i.dateaccessioned, di.dateaccessioned ) ) <= <<and | date>>
GROUP BY
    itype
ORDER BY
    itype

Count by Call Number

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Count by Call Number
  • Status: Complete
SELECT count(items.itemcallnumber) as 'Number of Items', items.itemcallnumber 
FROM items 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber asc

Count of all items: categorized by DDC

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Count of all items categorized by 1st grade of Dewey Decimal Classes
  • Status: Complete
SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') as "Call Number Code",COUNT(itemnumber) as "Books Count"
from biblio,items
WHERE biblio.biblionumber=items.biblionumber
AND SUBSTRING(itemcallnumber,1,1) regexp '^[0-9].*'
AND items.itemlost = '0' 
AND items.damaged ='0'
GROUP BY SUBSTRING(itemcallnumber,1,1)
ORDER BY SUBSTRING(itemcallnumber,1,1) ASC

Count of all items and broken down by branch

  • Developer: Zachary Spalding, SENYLRC
  • Module: Catalog
  • Purpose: Count of all items by Item and broken down by branch
  • Status: Complete
SELECT items.homebranch,branches.branchname, count(items.itemnumber) AS items 
FROM items,branches where items.homebranch=branches.branchcode 
GROUP BY homebranch 
ORDER BY homebranch ASC

Count of items in a location

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: This will count the items in a location showing locations that have 0 items in them
  • Status: Complete
select v.lib as loc, count(i.itemnumber) as items 
from authorised_values v 
left join items i  ON (i.location=v.authorised_value) 
where v.category='LOC' 
group by v.id

Count of all items by Item Type

  • Developer: Michael Hafen
  • Module: Catalog
  • Purpose: Count of all items by Item Type
  • Status: Complete
SELECT
  itype AS 'Item Type',
  COUNT(barcode) AS Count
FROM items
WHERE barcode <> '' AND barcode IS NOT NULL 
GROUP BY itype

Count of items by branch, then by item type

SELECT homebranch, itype AS  'Item Type', COUNT( barcode ) AS Count
FROM items
WHERE barcode <>  '' AND barcode IS NOT NULL 
GROUP BY homebranch, itype
ORDER BY homebranch, itype ASC

Number of items on each collection (952$8)

SELECT
    i.ccode AS  "Collection code",
    av.lib AS  "Collection name",
    COUNT( i.barcode ) AS  "Count"
FROM items i
LEFT JOIN authorised_values av ON ( av.authorised_value = i.ccode ) 
    AND av.category =  'CCODE'
WHERE i.ccode IS NOT NULL
GROUP BY i.ccode

Item Lists

List of All Unique Items at a Branch

  • Developer: Christofer Zorn
  • Module: Catalog
  • Purpose: A listing of all titles where only one item exists in the system and at the specified branch, nowhere else in the system. Items unique to the chosen branch (excludes withdrawn and lost items).
  • Status: Complete
SELECT
  biblio.title AS Title,
  biblio.author AS Author,
  items.barcode AS Barcode,
  items.dateaccessioned AS DateAccessioned,
  items.datelastborrowed AS DateLastBorrowed,
  items.homebranch AS HomeBranch,
  items.itype AS IType,
  items.ccode AS CollectionCode,
  items.location AS Location
FROM biblio
INNER JOIN items USING (biblionumber)
WHERE
  items.withdrawn = 0
  AND items.itemlost = 0
GROUP BY biblio.biblionumber
HAVING 
  COUNT(1) = 1 
  AND items.homebranch = <<Branch|branches>>

All Barcodes

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: All Barcodes
  • Status: Complete
  SELECT items.barcode,items.location,biblio.title,items.itemcallnumber 
  FROM items 
  LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  
  WHERE items.homebranch=<<Home branch|branches>>

Call Number Shelflist

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: list in call number order
  • Status: Completed
SELECT items.itemcallnumber,items.datelastborrowed,biblio.title,biblioitems.publicationyear 
FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
ORDER BY items.cn_sort asc

Complete Shelflist

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Complete Shelf list
  • Status: Complete
  SELECT  items.price,items.replacementprice,biblio.title,biblio.author,items.itemcallnumber 
  FROM items 
  LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE items.homebranch=<<Home branch|branches>> 
  ORDER BY items.itemcallnumber ASC

New Items

Count by Call Number for items added last month

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Count by Call Number for items added last month
  • Status: Complete
SELECT count(items.itemcallnumber), items.itemcallnumber 
FROM items 
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber asc

Items added by Collection

  • Developer: Katrin Fischer and Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Count of items added by collection in a specific date range
  • Status: Complete
SELECT count(ccode), ccode as collection
FROM (
SELECT ccode, dateaccessioned FROM items
UNION ALL
SELECT ccode, dateaccessioned FROM deleteditems
)
AS itemsadded
WHERE date(dateaccessioned) between
<<Added BETWEEN (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
group by ccode

Items Added in Date Range

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Items added in a time period (will ask for date range twice)
  • Status: Complete
SELECT sum(COUNT) AS added
FROM
  (SELECT count(*) AS COUNT
   FROM items
   WHERE date(dateaccessioned) BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<AND (yyyy-mm-dd)|date>>
   UNION ALL SELECT count(*) AS COUNT
   FROM deleteditems
   WHERE date(dateaccessioned) BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<AND (yyyy-mm-dd)|date>> ) AS items

List new items

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: List new items
  • Status: Complete
SELECT items.dateaccessioned,
       biblio.title,
       items.itemcallnumber
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE DATE (items.dateaccessioned) BETWEEN <<BETWEEN (yyyy-mm-dd)|date>> AND <<AND (yyyy-mm-dd)|date>>
  AND items.homebranch=<<Home branch|branches>>
ORDER BY items.itemcallnumber ASC

List of Items added to catalog in last 30 days

  • Developer: Nora Blake
  • Module: Catalog
  • Purpose: List of Items added to catalog in last 30 days (includes bibliographic info)
  • Status: Complete
  SELECT items.dateaccessioned,items.itemcallnumber,biblio.title,biblio.author 
  FROM items 
  LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE items.homebranch=<<Home branch|branches>> and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= items.dateaccessioned 
  ORDER BY biblio.title asc

List of new items added in a date & subject range

  • Developer: Furrukh Hussian Zai
  • Module: Catalog
  • Purpose: List of new items added in a date & subject range
  • Status: Complete
select *
from(SELECT
    items.dateaccessioned,
    items.barcode,
    items.itemcallnumber,
    biblio.title,
    biblio.author,
    biblioitems.publishercode,
    (select ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
  AS Subject
FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)  
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) as t
where  Subject like concat('%',<<Subject>>,'%')
ORDER BY dateaccessioned DESC

List of new items added in a date range

  • Developer: Furrukh Hussian Zai
  • Module: Catalog
  • Purpose: List of new items added in a date range
  • Status: Complete
SELECT items.dateaccessioned,items.barcode,items.itemcallnumber,biblio.author,biblio.title,biblioitems.publishercode FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN
<<Between Date (2017-08-01)>> and <<and (2017-08-31)>>
ORDER BY items.barcode DESC

Another new items report

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: List new items between specific dates
  • Status: Complete
select monthname(timestamp) as month, year(timestamp) as year, count(itemnumber) as count 
from items 
where timestamp between <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
group by year(timestamp), month(timestamp)

Previous Month Items Created

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Created
  • Status: Complete
SELECT count(items.itemnumber) as ItemsCreated 
FROM items 
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month)

Previous Month Items Created--by item type

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Created--by item type (The total number of rows shown is misleading -- It matches the first item type total. An empty item type column means unknown item type. Add all the entries for the complete total.)
  • Status: Complete
SELECT items.itype as ItemType, count(items.itemnumber) as ItemsCreated 
FROM items 
WHERE (items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month))  
GROUP BY items.itype

Count of items added by cataloger

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Asks for librarian's borrower number and shows them with a count of items they've added. [Requires CataloguingLog to be on]
  • Status: Completed
select count(timestamp) as 'items added' 
from action_logs 
where module='CATALOGUING' and user=<<Borrower number>> 
      and info='item' and action='ADD'

Count of items added by cataloger

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Counts the number of cataloging actions each cataloger performed in a date range. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT concat(p.firstname, ' ', p.surname) as staff, concat(a.action, ' ', a.info) as action, count(a.timestamp) as count 
FROM action_logs a 
left join borrowers p on (a.user=p.borrowernumber)
WHERE a.module='CATALOGUING' and a.timestamp between <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
      and a.info in ('item','biblio')
group by p.borrowernumber, concat(a.action, ' ', a.info)

Deleted Items

Item records added/deleted in time frame

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the items added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging cataloging actions.
SELECT l.action, count(l.timestamp) AS 'items'
FROM action_logs l
LEFT JOIN borrowers p on (p.borrowernumber=l.user)
WHERE module='CATALOGUING' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) between <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
      AND l.info='item'
GROUP BY l.action

Items Deleted in Date Range at Branch

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Items deleted at a branch in a time period
  • Status: Complete
SELECT count(*) AS "Items Deleted"
FROM deleteditems
WHERE timestamp between <<Deleted between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
      and homebranch=<<Owning branch|branches>>

Previous Month Items Deleted

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Deleted
  • Status: Complete
SELECT count(deleteditems.itemnumber) as ItemsDeleted 
FROM deleteditems 
WHERE deleteditems.timestamp like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))

Previous Month Items Deleted--by item type

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Deleted--by item type (The total number of rows shown is misleading -- It matches the first item type total. An empty item type column means unknown item type. Add all the entries for the complete total.)
  • Status: Complete
SELECT deleteditems.itype as ItemType, count(deleteditems.itemnumber) as ItemsDeleted 
FROM deleteditems 
WHERE (deleteditems.timestamp like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))) 
GROUP BY deleteditems.itype

Lost/Missing Items

List of Items Marked Lost/Missing

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Finds all items that are marked as lost in some way.
  • Status: Completed
select i.itemnumber, b.title, b.author, i.itemcallnumber, 
       i.barcode, v.lib 
from items i
left join biblio b on (i.biblionumber=b.biblionumber) 
left join authorised_values v on (i.itemlost=v.authorised_value) 
where i.itemlost != 0 and v.category='LOST'

List of Items Marked Lost/Missing with Hold Info

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Finds all items that are marked as lost in some way and shows if they're on hold.
  • Status: Completed
SELECT i.itemnumber, i.reserves, i.issues, i.datelastseen,
i.dateaccessioned, i.ccode, b.title, b.author,
i.itemcallnumber, i.barcode, v.lib, 
IF(h.reservedate IS NULL, '', 'on hold') as holds
FROM items i
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN reserves h on (b.biblionumber=h.biblionumber)
WHERE i.itemlost != 0 AND v.category='LOST'

List of Items Marked Lost/Missing with Holds past 6 months

  • Developer: Agnes Rivers-Moore, Hanover Public Library
  • Module: Catalog
  • Purpose: Finds all items that are marked as lost/missing, since 6 months ago, with title link and shows if they're on hold. Uses the new lost_on date.
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',title,'</a>') AS Title, i.location, i.itemcallnumber,
i.barcode, i.itemlost_on, v.lib, i.issues, i.datelastseen, i.dateaccessioned,
IF(h.reservedate IS NULL, '', 'on hold') AS holds
FROM items i
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
WHERE i.itemlost != 0 AND v.category='LOST' AND i.itemlost_on > DATE_SUB(now(),INTERVAL 6 MONTH)
ORDER BY i.itemlost_on DESC

List of Items Marked Lost/Missing, Choose Lost Status

  • Developer: Barton Chittenden
  • Module: Circulation
  • Purpose: Finds all items that are marked as lost/missing, choose lost status.
  • Status: Completed
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', title, '</a>' ) AS title,
    itemnumber,
    barcode
FROM
    items
    inner join biblio using (biblionumber)
WHERE
    items.itemlost = <<Lost status|lost>>

Lost Items & Who Lost Them

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report should show all items that are marked lost and who lost them. It's not fool proof, but it's the closest I can get.
  • Status: Complete
SELECT i.itemnumber, i.ccode, b.title, b.author, i.itemcallnumber, 
       i.enumchron, i.itemnotes, i.barcode, v.lib as 'lost', c.borrowernumber
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN ( SELECT itemnumber, borrowernumber, issuedate, timestamp, returndate
            FROM issues UNION SELECT itemnumber, borrowernumber, issuedate, timestamp,
            returndate FROM old_issues ) c 
     ON (c.itemnumber=i.itemnumber) 
left join statistics s on (s.itemnumber=i.itemnumber)
WHERE i.itemlost != 0 AND v.category='LOST' and
      date(s.datetime)=date(c.issuedate) and s.type='issue'

Withdrawn Items

Withdrawn Items (with details)

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Withdrawn Items
  • Status: Complete
SELECT biblio.title,biblio.author,items.itemcallnumber,items.barcode,items.datelastborrowed, items.withdrawn 
FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.withdrawn != 0 
ORDER BY biblio.title asc

Withdrawn Items 3.12- (barcodes only)

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Barcodes of items marked as withdrawn (best used for batch deleting)
  • Status: Complete
  • Version: 3.12-
SELECT barcode
FROM items
WHERE withdrawn != 0 
ORDER BY barcode ASC

Withdrawn Items 3.14+ (barcodes only)

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Barcodes of items marked as withdrawn (best used for batch deleting)
  • Status: Complete
  • Version: 3.14+
SELECT barcode
FROM items
WHERE withdrawn != 0 
ORDER BY barcode ASC

Items with Specific Attributes

Items without proper itype, ccode or location

  • Developer: Barton Chittenden
  • Module: Catalog
  • Purpose: find count of items without corresponding entry in the itemtypes table or authorized values for location or ccode.
  • Status: Complete
SELECT 'itype' AS type, count(*), itype AS code 
FROM items LEFT JOIN itemtypes on (items.itype = itemtypes.itemtype) 
WHERE itemtypes.itemtype IS NULL 
GROUP BY code

    UNION

SELECT 'ccode' AS type, count(*), ccode AS code 
FROM items LEFT JOIN authorised_values av on ( av.authorised_value = items.ccode AND av.category = 'CCODE') 
WHERE av.authorised_value IS NULL 
GROUP BY code

    UNION

SELECT 'location' AS type , count(*), location AS code 
FROM items LEFT JOIN authorised_values av on ( av.authorised_value = items.location AND av.category = 'LOC') 
WHERE av.authorised_value IS NULL 
GROUP BY code

Titles on a particular branch and shelving location

  • Developer: Nicole C. Baratta, ByWater Solutions (Posted by Rachel)
  • Module: Catalog
  • Purpose: Creates a list of titles (245a and 245b), authors, and call numbers along with home-branch and library location.
  • Status: Completed
SELECT concat(b.title, ' ', 
       ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, i.itemcallnumber 
FROM biblio b 
LEFT JOIN items i using (biblionumber)
left join biblio_metadata m using (biblionumber)
WHERE i.homebranch=<<homebranch|branches>> AND i.location=<<Shelving Location|LOC>>

Action log entries of items damaged within the last day

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Catalog
  • Purpose: Items damaged within the last day
  • Status: Complete
SELECT
      b.title
    , b.author
    , i.itemnumber
    , i.barcode
    , i.timestamp
    , l.*
FROM items i
LEFT JOIN biblio b USING ( biblionumber )
LEFT JOIN action_logs l on (l.timestamp >= timestamp( SUBDATE(CURDATE(), INTERVAL 1 DAY) ) and l.object = i.itemnumber )
WHERE
    i.damaged = 1
    AND DATE(i.timestamp) >= SUBDATE(CURDATE(), INTERVAL 1 DAY)
    AND l.info like '%damaged%'
ORDER BY i.timestamp ASC

Author List by Branch

  • Developer: Nick Clemens, VOKAL
  • Module: Catalog
  • Purpose: A list of authors that match search criteria
  • Status: Complete
SELECT  ' ' as Checkbox, b.title, b.author, i.itemcallnumber, i.barcode
FROM items i
JOIN biblio b using (biblionumber)
WHERE i.homebranch=<<Branch|branches>> AND 
      b.author LIKE CONCAT(<<Author: Last Name, First Name>>,'%')
ORDER BY b.title

Barcode Search Report

  • Developer: Ata ur Rehman (ata.rehman@gmail.com)
  • Module: Catalog
  • Purpose: Barcode search report. To verify if a record available against provided barcode. Barcode can be searched with wild cards '%' or '_'
  • Status: Complete
SELECT
  Concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">', biblio.biblionumber, '</a>') AS biblionumbers,
  items.barcode,
  items.dateaccessioned,
  items.itemcallnumber,
  biblioitems.isbn,
  biblio.author,
  biblio.title,
  biblioitems.pages,
  biblioitems.publishercode,
  biblioitems.place,
  biblio.copyrightdate
FROM
  items
  LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
  LEFT JOIN biblio ON biblioitems.biblionumber = biblio.biblionumber
WHERE
  items.homebranch = <<Branch|branches>> AND
  items.barcode LIKE <<Enter Barcode>>
ORDER BY
  LPad(items.barcode, 30, ' ')

Basic Item Information By Call Number Range

  • Developer: Jared Camins and Chris Nighswonger
  • Module: Catalog
  • Purpose: This report returns a set of items limited by a range of call numbers. The data included in the result set are: Call Number, Title, Author. A link is provided for easy viewing of the item details.
  • Status: Complete
SELECT CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">View Details</a>' ) AS 'View Details',
  items.itemcallnumber,
  biblio.title,
  biblio.author 
FROM items 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE items.itemcallnumber BETWEEN <<starting call number>> AND <<ending call number>>

Call Numbers

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Call Numbers
  • Status: Complete
  SELECT items.itype,items.itemcallnumber,items.barcode,biblio.title,biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE items.homebranch=<<Home branch|branches>> AND items.itemcallnumber LIKE concat(<<Call number like>>, '%')
  ORDER BY items.itemcallnumber ASC

Damaged Items with Title

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Damaged Items with Title
  • Status: Complete
SELECT items.damaged, items.itemcallnumber, items.barcode, biblio.title, biblio.author 
FROM items 
INNER JOIN biblio ON items.biblionumber = biblio.biblionumber 
WHERE items.damaged = True ORDER BY biblio.title asc

Items by Like Call Number, Branch and Item Type

  • Developer: Rebecca Crago, Systems and Teaching Librarian, Mercyhurst University
  • Module: Catalog
  • Purpose: Search items by like call number, by item type and branch location.
  • Status: Complete
SELECT itemcallnumber, biblio.title
FROM items
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
LEFT JOIN biblioitems ON items.biblionumber=biblioitems.biblioitemnumber
WHERE items.homebranch=<<Home branch|branches>>
AND biblioitems.itemtype = <<Item Type|itemtypes>>
AND items.itemcallnumber LIKE concat(<<Call number like>>, '%')

Items in a location with lists

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: This will list all items in a specific location and the lists they are in (if any).
  • Status: Complete
select b.title, i.barcode, i.location, group_concat(l.shelfname, ' || ') 
from items i 
left join biblio b using (biblionumber) 
left join virtualshelfcontents c on (b.biblionumber=c.biblionumber) 
left join virtualshelves l using (shelfnumber) 
where i.location=<<Location|LOC>> 
group by i.itemnumber

Items not for loan

SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber, '\">', b.title, '</a>' ) AS Title,
    ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",
    ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="n"]') AS "Part name",
    ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="p"]') AS "Part",
    b.author AS 'Author',
    b.copyrightdate AS 'Year',
    i.barcode AS Barcode, 
    i.itemcallnumber AS 'Callnumber', 
    i.itype AS 'Item Type'
FROM biblio b
LEFT JOIN items i USING ( biblionumber ) 
LEFT JOIN biblioitems bi USING ( biblionumber ) 
WHERE i.notforloan <> '0'
ORDER BY b.title

List of Not for Loan Magazine items

  • Developer: Alex Buckley, Catalyst IT for Waitaki District Library NZ
  • Module: Catalog
  • Purpose: List magazine items (items with collection code of "MAG") which are set Not for Loan
  • Status: Complete
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber, '\">', b.title, '</a>' ) AS Title,
    ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",
    ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="n"]') AS "Part name",
    ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="p"]') AS "Part",
    b.author AS 'Author',
    b.copyrightdate AS 'Copyright year',
    i.barcode AS Barcode, 
    i.itemcallnumber AS 'Callnumber', 
    i.itype AS 'Item Type'
FROM biblio b
LEFT JOIN items i USING ( biblionumber ) 
LEFT JOIN biblio_metadata bi USING ( biblionumber ) 
WHERE i.notforloan = '1' AND i.ccode = "MAG" 
ORDER BY b.title

Items with "X" & "Y" ITypes

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Items with "X" & "Y" ITypes
  • Status: Complete
  SELECT  items.dateaccessioned,items.itype,items.itemcallnumber,items.barcode,biblio.author,biblio.title, biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE (items.homebranch=<<Home branch|branches>> AND items.itype=<<Item type|itemtypes>>) 
        OR (items.homebranch=<<Second home branch|branches>> AND items.itype=<<Second item type|itemtypes>>) 
  ORDER BY items.dateaccessioned DESC

Items with "X" CCode

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Items with "X" CCode
  • Status: Complete
  SELECT  items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  
  WHERE items.homebranch=<<Home branch|branches>> AND items.ccode=<<Collection|CCODE>> 
  ORDER BY items.dateaccessioned DESC

Items with notes

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records with either a public or nonpublic note
  • Status: Complete
select b.title, b.author, i.itemcallnumber, i.barcode, 
       i.itemnotes as 'public note',
       ExtractValue(i.more_subfields_xml,'//datafield[@tag="999"]/subfield[@code>="x"]')
       as 'nonpublic note' 
from items i 
left join biblio b using (biblionumber) 
where i.itemnotes is not null 
      or i.more_subfields_xml is not null

List items for Reading groups - Provide the number of copies needed

  • Developer: Brenda Turnbull, LiveWire CIC
  • Module: Catalog
  • Purpose: List of items that can be used for reading groups - enter the number of copies needed for an item Looks for Item types Adult Fiction and Junior Fiction in various locations A
  • Status: Complete
SELECT   
      b.title AS   'Item                   Title          ',
      b.author as '       Author       ',
      i.itemcallnumber AS CallNo,  
      i.itype AS 'Item/ Type',  
      i.location ,   
      CONCAT( Extractvalue(bi.marcxml,  '//datafield[@tag="264"]/subfield[@code>="c"]'),  Extractvalue(bi.marcxml,  '//datafield[@tag="260"]/subfield[@code>="c"]') )AS PUBYR , 
             CONCAT( COUNT(i.barcode),   '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',i.biblionumber,'\">'," see more ",'</a>')   AS' Possible No. / of Copies',
 i.itemnotes AS 'Public Notes', abstract as Abstract
FROM items i
LEFT JOIN biblio b USING (biblionumber)   
LEFT JOIN biblioitems bi USING (biblionumber)               
WHERE    i.itype IN ('AF','JF')
 AND i.location IN ('A', 'G','SFG','H','X','LP','R','SF','Y','T','TC')
GROUP by i. biblionumber  HAVING COUNT(i.barcode) > <<How many copies needed? >>
ORDER BY b.title

List items which have data in the copy number field (952$t)

  • Developer: Heather Hernandez
  • Module: Catalog
  • Purpose: Finds all items with data in the copy number field, 952$t, retrieving that field plus location, call number, barcode, and item number, sorted by location and call number
  • Status: Complete
SELECT i.location, i.itemcallnumber, i.copynumber, i.barcode, i.itemnumber
FROM items i
WHERE i.copynumber IS NOT NULL
ORDER BY i.location, i.itemcallnumber

List of items by bibnumber with particular string in item type, with item location, call number, title and author

  • Developer: Heather Hernandez
  • Module: Catalog
  • Purpose: Finds all items with particular string in item type, with item location, call number, title and author, retrieving a hyperlinked bib number (sorted by this field), location, item call number, title and author. Replace 'music' with whatever string you want as the search string.
  • Status: Complete
SELECT concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">', biblio.biblionumber, '</a>') as 'bib',items.location,items.itemcallnumber,biblio.title,biblio.author
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype LIKE CONCAT( '%', 'music', '%' )
ORDER BY biblio.biblionumber ASC

Serial Enumeration Chronology containing c.2

  • Developer: Heather Hernandez
  • Module: Catalog
  • Purpose: Finds all items with c.2 in the serial enumeration chronology field 952$h (it should be in 952$t), retrieving that field plus location, call number, barcode, and item number, sorted by location and call number
  • Status: Complete
SELECT i.location, i.itemcallnumber, i.copynumber, i.barcode, i.itemnumber, i.enumchron
FROM items i
WHERE i.enumchron = 'C.2'
ORDER BY i.location, i.itemcallnumber

Items with Replacement Price Over X

  • Developer: Myka Kennedy Stephens, Fosgail LLC
  • Module: Catalog
  • Purpose: Finds all items that have a replacement price over the amount specified by the user
  • Status: Complete
  • Version: 24.05
SELECT
   i.itemnumber, 
   i.itemcallnumber, 
   b.title, 
   b.author, 
   i.barcode, 
   i.price, 
   i.replacementprice
FROM   
   items i 
LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber)
WHERE i.homebranch = <<Library|branches>> 
   AND i.replacementprice > <<Replacement price over (amount with two decimal places, no dollar sign)>>

Quality Control

Biblio Items without a Koha Item Type

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: Displays the biblionumber, title, and author of biblioitems that do not have an associated Koha Item Type.
  • Status: Complete
SELECT biblio.biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' ) = ""

Versions 17.05 and later:

SELECT biblio.biblionumber, biblio.title, biblio.author
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]' ) = ""

Items missing an item type or record missing an item type

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: Displays the title, biblionumber, barcode, itemcallnumber, and itemnumber of items that do not have an associated item type or where it's biblio record doesn't have an associated item type in the 942c.
  • Status: Complete
  • Version: Koha 23.05
SELECT title, biblionumber, barcode, itemcallnumber, itemnumber
FROM items
JOIN biblio USING (biblionumber)
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE itype IS NULL
OR ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') = ''

Items without Callnumber

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Cataloging quality control
  • Status: Complete
SELECT concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') as Title,Barcode, itemcallnumber
FROM items,biblioitems,biblio
WHERE items.biblionumber=biblioitems.biblionumber
AND items.biblionumber=biblio.biblionumber
AND (items.itemcallnumber IS NULL OR items.itemcallnumber = '')

Null Barcodes

  • Developer: Rachel Hollis
  • Module: Catalog
  • Purpose: Null Barcodes
  • Status: Complete
  SELECT items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate FROM biblio
  JOIN items using(biblionumber)
  WHERE (items.barcode IS NULL OR items.barcode = '')

Null Item Type

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Null Item Type
  • Status: Complete
  SELECT  items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  
  WHERE items.itype IS NULL AND items.homebranch=<<Home branch|branches>>

Null Location

  • Developer: Georgia Katsarou
  • Module: Catalog
  • Purpose: Null Location in Item
  • Status: Complete
SELECT items.biblionumber,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate, items.barcode, items.itemnumber FROM biblio 
JOIN items USING (biblionumber) 
WHERE (items.location IS NULL OR items.location = '')

Items with orphaned Locations

  • Developer: Barton Chittenden
  • Module: Catalog
  • Purpose: Find items whose location does not match any locations in authorized_values with category 'LOC'.
  • Status: Complete
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">', title, '</a>' ) AS title,
    barcode,
    location
FROM
    items
    INNER JOIN biblio using (biblionumber)
WHERE
    NOT EXISTS( 
        SELECT * from authorised_values  
        WHERE items.location = authorised_values.authorised_value 
          AND authorised_values.category = 'LOC' 
    )

Items Available Soon or On Order before a Date

  • Developer: Myka Kennedy Stephens, Fosgail LLC
  • Module: Catalog
  • Purpose: Select a branch and a date. Returns a list of items with "Available Soon" (notforloan value = -2) or "On Order (notforloan value = -1) status that haven't been updated since the date selected. List ordered by date last updated.
  • Status: Complete
  • Version: 24.05
SELECT i.biblionumber,
  i.itemnumber, 
  i.dateaccessioned, 
  b.title, 
  i.notforloan, 
  i.itype, 
  i.ccode, 
  i.location, 
  i.itemcallnumber,
  i.timestamp AS 'Last updated'
FROM items i
LEFT JOIN biblio b USING (biblionumber)
WHERE i.timestamp <= <<Items last updated on or before|date>>
    AND i.homebranch = <<Select a library|branches:all>>
    AND i.notforloan IN (-2, -1)
ORDER BY i.timestamp
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Catalog/Item_Reports&oldid=38741"