Collection Development

Reports about anything to do with collection development, weeding, inventory, etc.

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

Collection Development

Turnover Rate by Collection in Date Range

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Lists turnover ratios by collection in a given date range
Status: Complete

SELECT av.lib as Collection, COUNT(stats.datetime) AS Issues, COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
(COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))) AS Turnover
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') AS stats
RIGHT JOIN items i ON (i.itemnumber = stats.itemnumber)
LEFT JOIN (select * from authorised_values where category='ccode') av on (i.ccode=av.authorised_value)
WHERE i.dateaccessioned < <<End Date|date>> AND i.itype != 'ILL' #remove interlibrary loans
GROUP BY i.ccode, av.lib
ORDER BY av.lib

Turnover Rate for Call Number Range in Collection by Date Range

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Lists turnover ratios for a call number range within a collection and in a given date range
Status: Complete

SELECT COUNT(stats.datetime) AS Issues, COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
(COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))) AS Turnover
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> 
    AND ccode = <<Collection|ccode>> AND type='issue') AS stats
RIGHT JOIN items i ON (i.itemnumber = stats.itemnumber)
WHERE i.itemcallnumber BETWEEN <<Starting Call>> AND <<Ending Call (Exclusive)>> 
AND i.dateaccessioned < <<End Date|date>> AND i.ccode=<<Collection|ccode>>

Relative Use by Collection with Turnover

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Shows Relative Use (% of Issues/% of Total Collection) by Collection. Ideally relative use should be a 1:1 ratio.
Status: Complete

SELECT av.lib AS Collection, 
COUNT(stats.datetime) AS Issues, 
COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
totalissues,
totalcount,
(COUNT(stats.datetime)*100/totalissues) as 'Percentage of Issues',
((COUNT(DISTINCT(i.itemnumber))*100)/totalcount) as 'Percentage of Total Collection', 
FORMAT((COUNT(stats.datetime)/totalissues)/(COUNT(DISTINCT(i.itemnumber))/totalcount),4) as 'Relative Use',
FORMAT((COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))),4) AS Turnover
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') AS stats
RIGHT JOIN items i ON (i.itemnumber = stats.itemnumber)
LEFT JOIN (SELECT count(*) as totalcount from items) tct on (totalcount IS NOT NULL) 
LEFT JOIN (SELECT count(datetime) as totalissues from statistics WHERE date(datetime) 
BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') ti on (totalissues IS NOT NULL)
LEFT JOIN (SELECT * FROM authorised_values WHERE category='ccode') av ON (i.ccode=av.authorised_value)
WHERE i.dateaccessioned < <<End Date|date>> AND i.itype != 'ILL' #remove interlibrary loans
GROUP BY i.ccode
ORDER by av.lib

Relative Use For Dewey Based Collections in Call Num Range with Turnover

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Shows Relative Use (% of Issues/% of Total Collection) within a Dewey based collection. Allows for fine grain examination of collection area. Ideally relative use should be a 1:1 ratio.
Status: Complete

SELECT CASE
	/*When the total number of digits is < 3 add zeros to pad */
	WHEN <<Max # Dewey Digits>>=1 THEN CONCAT(LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>),'00')
    WHEN <<Max # Dewey Digits>>=2 THEN CONCAT(LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>),'0')
    WHEN <<Max # Dewey Digits>>=3 THEN LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>)
    /*If the total number of digits is > 3 then the decimal might be the 4th character if so add one more character*/
    WHEN <<Max # Dewey Digits>>=4 THEN LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>+1)
    /*Else if total number of digits is > 4 then check for ending character as decimal and adjust for skipping decimal, if the ending character isn't a decimal
    Then just adjust for skipping the decimal*/
    ELSE IF(RIGHT(LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>),1)='.',
	LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>+2),
	LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>+1))
    END AS DeweyNum,
COUNT(stats.datetime) AS Issues, 
COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
totalissues as 'Collection Issues',
totalcount 'Collection NumItems',
ROUND(avg(copyrightdate),0) as 'AVG Pubdate',
(COUNT(stats.datetime)*100/totalissues) as 'Percentage of Issues',
((COUNT(DISTINCT(i.itemnumber))*100)/totalcount) as 'Percentage of Total Collection', 
FORMAT((COUNT(stats.datetime)/totalissues)/(COUNT(DISTINCT(i.itemnumber))/totalcount),4) as 'Relative Use',
FORMAT((COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))),4) AS Turnover,
FORMAT((totalissues/totalcount),4) AS 'Collection Turnover'
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') AS stats
RIGHT JOIN (SELECT * FROM items ) i ON (i.itemnumber = stats.itemnumber)
LEFT JOIN biblio b on (b.biblionumber=i.biblionumber)
LEFT JOIN (SELECT count(*) as totalcount from items WHERE ccode=<<Dewey Based Collection|ccode>>) tct ON (totalcount IS NOT NULL) 
LEFT JOIN (SELECT count(datetime) as totalissues from statistics WHERE ccode=<<Dewey Based Collection|ccode>> AND
date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') ti on (totalissues IS NOT NULL)
WHERE i.dateaccessioned < <<End Date|date>> AND i.ccode=<<Dewey Based Collection|ccode>>
 AND i.itemcallnumber BETWEEN <<Starting Call>> AND <<Ending Call (Exclusive)>>
GROUP BY DeweyNum
ORDER by DeweyNum

Percentage of collection by collection code

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Number of items per collection code with the percentage of the total collection
Status: Complete

SELECT
   x.ccode as collection,
   x.allitems as 'items',
   (x.allitems * 100)/(
     SELECT COUNT(itemnumber) as 'total'
     FROM items
   ) as 'Percentage of total collection'
FROM
   (SELECT
     i.ccode,
     COUNT(i.itemnumber) as 'allitems'
   FROM items i
   GROUP BY i.ccode) x

Collection Evaluation Report

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Collection Evaluation report asks for branch, shelving location, data acquired range and date last borrowed range and returns titles
Status: Completed

select b.title, b.author, b.copyrightdate, i.itemcallnumber 
from biblio b 
left join items i 
using (biblionumber) 
where i.homebranch=<<Branch|branches>> and i.location=<<Shelving location|LOC>> 
          and i.dateaccessioned between <<Date acquired between (yyyy-mm-dd)|date>> and 
         <<and (yyyy-mm-dd)|date>> and i.datelastborrowed between 
         <<Date last checked out between (yyyy-mm-dd)|date>> and 
         <<and (yyyy-mm-dd)|date>>
order by i.itemcallnumber ASC

Collection Evaluation Report 2

Developer: Nicole C. Baratta and Ian Walls, ByWater Solutions
Module: Catalog
Purpose: Shows entire collection with publication info pulled from the 008 (Tip: would be wise to add a filter of some sort to this)
Status: Completed

select b.title, b.author, i.dateaccessioned, i.location, i.itemcallnumber,
i.itype, i.datelastborrowed, i.issues, substring(ExtractValue((
    SELECT marcxml
    FROM biblioitems b2
    WHERE b.biblionumber = b2.biblionumber), 
'//controlfield[@tag="008"]'),8,4) as 'pub date'
from biblio b left join items i using (biblionumber)

A particular Title total number of times issued count in date range

Developer: Vinod Kumar Mishra
Module: Collection Development
Purpose: Report to generate total number of times a particular book/title is issued. It will be helpful in collection development/procurement.
Status: Complete

SELECT b.biblionumber 'Record No.', b.title 'Title',b.author 'Author',ExtractValue(bm.metadata, '//datafield[@tag="260"]/subfield[@code="b"]') 'Publisher', b.copyrightdate 'Year',SUM(i.issues)'Total Issued',count(i.biblioitemnumber)'Total Copy'

FROM biblio b

LEFT JOIN items i on (b.biblionumber = i.biblionumber)
LEFT JOIN biblio_metadata bm on (b.biblionumber = bm.biblionumber)

GROUP BY b.biblionumber
HAVING SUM(i.issues) > <<Issued More Than>>
ORDER BY SUM(i.issues) DESC

Weeding

SuperWeeder 008

Developer: Barton Chittenden
Module: Catalog
Purpose: A variation on SuperWeeder which uses the publication date in 008.
Status: Complete

SELECT
    CONCAT( 
        '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',
        biblio.biblionumber, '\">', 
        items.barcode, '</a>' 
    ) AS 'Barcode',
    items.itemcallnumber,
    biblio.title,
    items.barcode,
    datelastseen, 
    SUBSTR(
        ExtractValue( 
            biblio_metadata.metadata,
            '//controlfield[@tag="008"]'
        ),
        8,4
    )  AS 008pubdate,
    items.dateaccessioned AS 'Accessioned',
    items.itype, 
    items.issues,
    (IFNULL(items.issues, 0) + IFNULL(items.renewals, 0)) AS Total_Circ, 
    items.datelastborrowed,
    items.itemlost,
    items.onloan,
    items.damaged,
    items.itemnotes
FROM
    items
    LEFT JOIN biblioitems USING (biblionumber) 
    LEFT JOIN biblio  USING (biblionumber) 
    LEFT JOIN biblio_metadata  USING (biblionumber) 
WHERE
    items.itype= <<Item type code|itemtypes>>
    AND items.holdingbranch=<<Branch code|branches>> 
    AND items.itemcallnumber BETWEEN <<Call number between>> AND <<and>>
ORDER BY
    items.itemcallnumber

Weeding tool

Developer: Kathy Rippel
Module: Catalog
Purpose: Weeding tool, we call this the SuperWeeder because it includes all sorts of data to help in decision making
Status: Complete

SELECT CONCAT( '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=', biblio.biblionumber,'\">', 
       items.barcode, '</a>' ) AS 'Barcode', items.itemcallnumber, biblio.title, 
       biblio.copyrightdate AS 'Copyright', items.dateaccessioned AS 'Accessioned', items.itype, 
       items.issues, items.renewals, (IFNULL(items.issues, 0)+IFNULL(items.renewals, 0)) AS Total_Circ, 
       items.datelastborrowed, items.itemlost, items.onloan, items.damaged, items.itemnotes
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype= <<Item type code|itemtypes>> AND items.holdingbranch=<<Branch code|branches>> 
      AND items.itemcallnumber between <<Call number between>> and <<and>>
ORDER BY items.itemcallnumber

CREW Friendly Weeding

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Generates list candidates for weeding based upon number of years since last cko and publication date within a call number range in a collection
Status: Complete

SELECT  
CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', b.biblionumber, '\">View Details</a>' ) AS 'View Details',
i.itemcallnumber,b.copyrightdate,b.author,b.title,i.ccode,i.itemnumber,i.barcode,b.biblionumber,i.dateaccessioned,
i.datelastborrowed,i.issues,i.itemnotes_nonpublic,i.itemnotes
FROM items i
LEFT JOIN biblioitems bi on (i.biblioitemnumber=bi.biblioitemnumber)
LEFT JOIN biblio b on (bi.biblionumber=b.biblionumber)  
WHERE i.itemcallnumber BETWEEN <<'Starting Call'>> AND <<'Ending Call (Exclusive)'>> AND i.ccode=<<Collection|ccode>> 
AND i.onloan IS NULL AND i.damaged=0 AND i.itemlost=0 AND i.withdrawn=0 AND
CASE
  /*if the item has not circulated then see if it was added before num years since last cko
  if it was then use copyright date as criteria*/
  WHEN (datelastborrowed IS NULL AND dateaccessioned < <<Last CKO Date|date>>)
    THEN b.copyrightdate < <<Published Before (YYYY)>>
    /*as long as the item has circulated at least once then use combination of datelastborrowed and copyright date
    as criteria*/
  ELSE (i.datelastborrowed < <<Last CKO Date|date>> OR b.copyrightdate < <<Published Before (YYYY)>> )
END

UNION ALL

SELECT '','zzz# Proposed to Weed: ',FOUND_ROWS(),'# in Range: ',count(itemnumber), 'Percent Proposed to Weed:',
CONCAT(FORMAT(IF(count(itemnumber)=0,0,(FOUND_ROWS()*100.0/count(itemnumber))),2),'%'),'Generated on: ',CURDATE(),'','','','',''
FROM items i
WHERE i.withdrawn=0 and i.damaged=0 AND i.itemcallnumber BETWEEN <<'Starting Call'>> 
AND <<'Ending Call (Exclusive)'>> AND i.ccode=<<Collection|ccode>>
ORDER BY itemcallnumber,author,title

Inventory & Accession Registers

Accession Register Sorted by Barcode Number Report

Developer: Ata ur Rehman (ata.rehman@gmail.com)
Module: Catalog
Purpose: To create an Accession Register Sorted by Barcode Number Report
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>> 
ORDER BY LPAD(items.barcode,40,' ') asc

Updated Accession Register Sorted by Barcode Number Report

Developer: Ata ur Rehman (ata.rehman@gmail.com)
Module: Catalog
Purpose: To create an Accession Register Sorted by Barcode Number Report
Status: Complete
Updated: Feb 13, 2021

SELECT 
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio_metadata.biblionumber,'\">',biblio_metadata.biblionumber,'</a>') 
AS biblionumber,
items.barcode as Barcode,
items.dateaccessioned as AccDate, 
items.itemcallnumber as CallNo, 

ExtractValue(metadata,'//datafield[@tag="020"]/subfield[@code="a"]') as ISBN,
ExtractValue(metadata,'//datafield[@tag="100"]/subfield[@code="a"]') as Author,
ExtractValue(metadata,'//datafield[@tag="700"]/subfield[@code="a"]') as OtherAuthors,
ExtractValue(metadata,'//datafield[@tag="710"]/subfield[@code="a"]') as CorporateAuthor,
ExtractValue(metadata,'//datafield[@tag="245"]/*') as Title,
ExtractValue(metadata,'//datafield[@tag="250"]/subfield[@code="a"]') as Ed,
ExtractValue(metadata,'//datafield[@tag="260"]/subfield[@code="c"]') as Year,
ExtractValue(metadata,'//datafield[@tag="260"]/subfield[@code="a"]') as Place,
ExtractValue(metadata,'//datafield[@tag="260"]/subfield[@code="b"]') as Pub,
ExtractValue(metadata,'//datafield[@tag="300"]/*') as PhysicalDesc,
ExtractValue(metadata,'//datafield[@tag="650"]/*') as Subject


FROM biblio_metadata
LEFT JOIN items ON (biblio_metadata.biblionumber=items.biblionumber)
WHERE items.homebranch =<<Branch|branches>> 
ORDER BY LPAD(items.barcode,40,' '), biblio_metadata.biblionumber ASC

Inventory Report

Developer: Sher Afzal Khan (Kohapakistan@gmail.com)
Module: Catalog
Purpose: Subject wise list of books
Status: Complete

SELECT  items.itemnumber,items.biblionumber,items.barcode,items.price,items.holdingbranch,items.ccode,items.itype,
biblioitems.biblionumber,biblioitems.isbn,biblio.author,items.stack,items.location,items.
permanent_location,items.ccode,biblio.title,biblio.author
FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)

Inventory Report

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Find all items that haven't been seen since a specific date
Status: Complete

SELECT b.title, i.barcode, i.itemcallnumber, i.itemlost, i.damaged,
      IF(i.onloan IS NULL, '', 'checked out') as onloan
 FROM biblio b
 LEFT JOIN items i USING (biblionumber)
 WHERE datelastseen < <<Last seen before (yyyy-mm-dd)|date>> 
                 and i.homebranch=<<Home branch|branches>>
 ORDER BY datelastseen desc, i.itemcallnumber ASC

Inventory Report by Location

Developer: Jason O'Neil. Original by Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Find all items that haven't been seen since a specific date, filtered by shelving location, and with borrower details for items that are currently checked out.
Status: Complete

SELECT b.title, i.barcode, i.itemcallnumber, datelastseen, i.location,
      IF(i.onloan IS NULL, '', 'checked out') AS onloan,
      IF(p.cardnumber IS NULL, '', p.cardnumber) AS cardnumber,
      IF(p.firstname IS NULL, '', p.firstname) AS firstname,
      IF(p.surname IS NULL, '', p.surname) AS surname
 FROM biblio b
 LEFT JOIN items i USING (biblionumber)
 LEFT JOIN issues c ON (i.itemnumber=c.itemnumber) 
 LEFT JOIN borrowers p ON (p.borrowernumber=c.borrowernumber) 
 WHERE datelastseen < <<Last seen before (yyyy-mm-dd)|date>> 
                 AND i.homebranch=<<Home branch|branches>>
                 AND i.location=<<Location|LOC>>
 ORDER BY onloan DESC, datelastseen DESC, i.itemcallnumber ASC

Accession Register Report by Branch

Developer: Mahesh Palamuttath
Module: Catalog
Purpose: Getting details of all books under a specific branch library.
Status: Completed

SELECT  items.barcode,items.itemcallnumber,items.itype,items.ccode,items.location,biblioitems.isbn,biblio.author,biblio.title,biblio.subtitle,biblioitems.editionstatement,biblioitems.place,biblioitems.publishercode,biblio.copyrightdate,biblioitems.pages,items.price,items.enumchron,items.dateaccessioned FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) where items.homebranch =<<Choose library|branches>>

Duplicates

Duplicate barcodes after removing leading zeros

Developer: Pablo Bianchi
Module: Catalog
Purpose: After a big import of records trying to remove leading zeros from barcods result in duplicates.
Status: Complete
Notes: TODO: make biblionumber links to records.

SELECT COUNT(*) AS "Reps",
       CAST(barcode AS UNSIGNED) AS "Barcodes duplicated without zeros",
       GROUP_CONCAT(biblionumber SEPARATOR ' ') AS "Biblionumbers"
FROM items
GROUP BY CAST(barcode AS UNSIGNED)
HAVING COUNT(*) > 1 AND COUNT(CASE WHEN homebranch = <<Pick your branch|branches>> THEN 1 END) >= 1
ORDER BY COUNT(*) DESC, CAST(barcode AS UNSIGNED) DESC

Duplicate bibs using the 001

Developer: Katrin Fischer and Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Show records with duplicate 001 fields
Status: Completed
Version: Will not work after upgrade to 17.05 or later

SELECT
  GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers,
  ExtractValue(marcxml,'//controlfield[@tag="001"]') AS id
FROM biblioitems
GROUP BY id
HAVING count(id) > 1

Duplicate ISBNs

Developer: Jared Camins-Esakov, ByWater Solutions
Module: Catalog
Purpose: Show records with duplicate ISBNs; download for full list (doesn't paginate)
Status: Completed

SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, isbn 
FROM biblioitems 
GROUP BY isbn 
HAVING COUNT(isbn)>1

Duplicate ISBNs Alternative

Developer: Nick Clemens, VOKAL
Module: Catalog
Purpose: Building from the duplicate isbn report, but normalizing to 13-digits and adding a fast link to merge the highest and lowest bibnumbers. Long and maybe a bit clunky, but very effective.
Status: Completed

SELECT
  GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers,
  b.title,
  b.author,
  IF(LEFT(REPLACE(TRIM(i.isbn), '-', ''), 3) <> '978', CONCAT('978', LEFT(REPLACE(TRIM(i.isbn), '-', ''), 9), 
  (MOD(10 - MOD(
    (CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 1, 1), UNSIGNED INTEGER) +
      CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 3, 1), UNSIGNED INTEGER) +
      CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 5, 1), UNSIGNED INTEGER) +
      CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 7, 1), UNSIGNED INTEGER) +
      CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 9, 1), UNSIGNED INTEGER))*3 +
    CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 2, 1), UNSIGNED INTEGER) +
    CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 4, 1), UNSIGNED INTEGER) +
    CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 6, 1), UNSIGNED INTEGER) +
    CONVERT(SUBSTR(REPLACE(TRIM(i.isbn), '-', ''), 8, 1), UNSIGNED INTEGER) + 38, 10), 10))),
  LEFT(REPLACE(TRIM(i.isbn), '-', ''), 13)) AS NormISBN,
  CONCAT('<a href=\"http://staff.kohavt.org/cgi-bin/koha/cataloguing/merge.pl?biblionumber=',
    MIN(b.biblionumber),
    '&biblionumber=',
    MAX(b.biblionumber),
    '\">Merge</a>') AS FastMerge,
  GROUP_CONCAT(DISTINCT b.typelist SEPARATOR '::') AS TypeDiscrepCheck 
FROM
  (SELECT
      b2.biblionumber,
      b2.title,
      b2.author,
      COUNT(i2.barcode) AS itemcount,
      GROUP_CONCAT(DISTINCT i2.itype) AS typelist 
    FROM
      biblio b2 
      JOIN
        items i2 
        ON i2.biblionumber = b2.biblionumber 
    GROUP BY
      b2.biblionumber 
    HAVING
      itemcount > 0) b 
  LEFT JOIN
    biblioitems i 
    ON (i.biblionumber = b.biblionumber) 
WHERE
  i.isbn IS NOT NULL 
  AND i.isbn <> '' 
GROUP BY
  CONCAT(substr(b.title, 1, 9), " / ", NormISBN) 
HAVING
  COUNT(CONCAT(substr(b.title, 1, 9), " / ", NormISBN)) > 1 
ORDER BY
  COUNT(b.biblionumber) ASC

Duplicate ISBNs in Time Frame

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Show records with duplicate ISBNs added within a specific time frame.
Status: Completed

SELECT GROUP_CONCAT(CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') 
       SEPARATOR ', ') AS biblionumbers, b.title, b.author 
FROM biblio b
LEFT JOIN biblioitems i USING (biblionumber)
WHERE b.datecreated between <<Added between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
GROUP BY substring_index(i.isbn, ' ', 1) 
HAVING COUNT(substring_index(i.isbn, ' ', 1))>1

Duplicate ISBNs with Links to Bib Records

Developer: Zachary Spalding, SENYLRC
Module: Catalog
Purpose: Show records with duplicate ISBNs; download for full list (doesn't paginate) and has links to bib records. Based on ISBN report written by Jared Camins-Esakov
Status: Completed

SELECT  GROUP_CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>') AS biblionumbers, 
        isbn 
FROM biblioitems 
group by isbn, itemtype 
HAVING COUNT(isbn)>1

Duplicate ISBNs++ with Links to Bib Records and link to batch edit and combine

Developer: Pablo López Liotti, UNMDP
Module: Catalog
Purpose: Show records with duplicate ISBNs; has links to individual bib records and link to duplicate records group to batch edit and combine.

Based on ISBN report written by Zachary Spalding, SENYLRC.

Status: Completed
Version: All
Notes: Click biblionumber -> show individual bib record. Click ISBN ->search and list all records with same ISBN for edit/combine them.

SELECT  
        GROUP_CONCAT('<a target="_blank" title="Show record with THIS biblionumber" 
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
        biblionumber,'\">',biblionumber,'</a>') AS biblionumbers, 
        CONCAT('<a target="_blank" title="List ALL records with same ISBN to edit/combine" 
href=\"/cgi-bin/koha/catalogue/search.pl?q=', isbn,'\">',isbn,'</a>') AS ISBN 
FROM biblioitems 
GROUP BY isbn, itemtype 
HAVING COUNT(isbn)>1

Duplicate EAN/UPC (024$a)

Developer: Owen Leonard
Module: Catalog
Purpose: Show records with duplicate EAN
Status: Completed
Version: All
Notes:

SELECT 
    GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, 
    ExtractValue(metadata,'//datafield[@tag="024"]/subfield[@code="a"]') AS `EAN` 
FROM 
    biblio_metadata 
WHERE 
    (ExtractValue(metadata,'//datafield[@tag="024"]/subfield[@code="a"]') != '' 
    AND ExtractValue(metadata,'//datafield[@tag="024"]/subfield[@code="a"]') IS NOT NULL ) 
GROUP BY `EAN` 
HAVING COUNT(`EAN`)>1;

Duplicate titles (using author and title)

Developer: D Ruth Bavousett, ByWater Solutions
Module: Catalog
Purpose: Checks for exact duplicates on author/title combo; download for full list (doesn't paginate)
Status: Completed

SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, title, author 
FROM biblio 
GROUP BY CONCAT(title,"/",author) 
HAVING COUNT(CONCAT(title,"/",author))>1

Duplicate titles (using title and ISBN)

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Show records with duplicate titles (using the first 9 characters) and duplicate ISBNs
Status: Completed

SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title, 
       b.author, GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns 
FROM biblio b 
left join biblioitems i 
on (i.biblionumber=b.biblionumber)
GROUP BY CONCAT(substr(b.title,0,9),"/",i.isbn) 
HAVING COUNT(CONCAT(substr(b.title,0,9),"/",i.isbn))>1

Duplicate titles (using title and ISBN), multi-branch version

Developer: Barton Chittenden, ByWater Solutions / Cab Vinton
Module: Catalog
Purpose: Show records with duplicate titles (using the first 9 characters) and duplicate ISBNs, where at least one item is owned by a particular branch
Status: Completed
Works With: 17.11

SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title, b.author, GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns 
FROM biblio b 
LEFT JOIN biblioitems i ON (i.biblionumber=b.biblionumber)
WHERE i.isbn IS NOT NULL AND i.isbn <> ' ' AND
     EXISTS( SELECT * from items where b.biblionumber = items.biblionumber and items.homebranch = <<Library|branches>> )
GROUP BY CONCAT(substr(b.title,0,9),"/",i.isbn) 
HAVING COUNT(*) > 1

Duplicate titles (with same date)

Developer: Jared Camins-Esakov
Module: Catalog
Purpose: Based on druthb's report for duplicate titles, but considers date as well; download for full list (doesn't paginate)
Status: Completed

SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, title, author,copyrightdate 
FROM biblio 
GROUP BY CONCAT(title,"/",author,"/",copyrightdate) HAVING COUNT(CONCAT(title,"/",author,"/",copyrightdate))>1

Duplicate titles having both DVD items and others

Developer: Barton Chittenden
Module: Catalog
Purpose: Find pairs of titles, regardless of item type, having at least item of itype DVD and one item that does *not* have itype DVD. Used to exclude Kanopy downloads where the library already has a DVD of the title.
Status: Completed

SELECT
    title,
    GROUP_CONCAT(
        distinct CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' )
        SEPARATOR ', ' 
    ) as biblionumbers,
    GROUP_CONCAT(distinct description SEPARATOR ', ' ) AS itemtypes    
FROM
    biblio
    INNER JOIN items USING (biblionumber)
    LEFT JOIN itemtypes on (itype = itemtype)
GROUP BY
    title
HAVING
    SUM(IF(itype = 'DVD', 1, 0 )) > 0
    AND SUM(IF(itype != 'DVD' OR itype IS NULL, 1, 0 )) > 0

Duplicate 001 fields with OCLC prefixes removed

Developer: Sarah Cornell
Module: Catalog
Purpose: Removes OCLC prefixes from 001 field and then returns duplicates. Useful if OCLC records have been imported using a variety of rules. Includes tool for merging and normalized OCLC record number lookup. Added TRIM to remove leading zeroes.
Status: Completed
Works with: 20.05

SELECT 
GROUP_CONCAT(DISTINCT biblio.biblionumber SEPARATOR ', ') AS biblionumbers,
normOCLC,
CONCAT('<a href=\"/cgi-bin/koha/catalogue/search.pl?idx=kw&q=',normOCLC,'\"target="_blank">oclc lookup</a>') AS 'lookup by oclc',
COUNT(DISTINCT biblio.biblionumber) AS count,
GROUP_CONCAT(DISTINCT biblio.title SEPARATOR ' NEXT TITLE:') AS titles,
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/merge.pl?biblionumber=',MIN(biblio.biblionumber),'&biblionumber=',MAX(biblio.biblionumber),'\"target="_blank">Merge</a>') AS FastMerge,
GROUP_CONCAT(DISTINCT biblioitems.itemtype SEPARATOR '::') AS TypeDiscrepCheck

FROM (

	SELECT 
	biblionumber,
	TRIM(LEADING '0' FROM REGEXP_REPLACE(ExtractValue( metadata, '//controlfield[@tag=\"001\"]' ), '[ocmn ]', '')) AS normOCLC
		FROM biblio_metadata 
		WHERE ExtractValue( metadata, '//controlfield[@tag=\"001\"]' )  !=''

) AS OCLC

	LEFT JOIN biblio ON (OCLC.biblionumber=biblio.biblionumber) 
	LEFT JOIN biblioitems ON (OCLC.biblionumber=biblioitems.biblionumber)
	LEFT JOIN biblio_metadata ON (OCLC.biblionumber=biblio_metadata.biblionumber)
GROUP BY normOCLC
HAVING COUNT(DISTINCT biblio.biblionumber) >1
LIMIT 100

Duplicate title and author combinations within an item type

Developer: Andrew Fuerste-Henry
Module: Catalog
Purpose: Finds bib records that share a title, author, and item type. Includes a link to a cataloging search to facilitate merging.
Status: Completed
Works with: 19.11

SELECT title,
    author, 
    itemtype,
    count(DISTINCT biblionumber) as count_of_bibs, 
    group_concat(ifnull(bib_info,concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>')) SEPARATOR '<br>') as bib_numbers, 
    concat('<a href=\"
/cgi-bin/koha/cataloguing/addbooks.pl?q=sn%3A', group_concat(biblionumber SEPARATOR '+OR+sn%3A'), '\">','Link to merge', '</a>') as merge_link
FROM biblio
	left join biblioitems USING (biblionumber)
    LEFT JOIN (SELECT biblionumber, concat(concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>'),' - items at:',
    				group_concat(homebranch SEPARATOR ', ')) as bib_info
                FROM items
                GROUP BY biblionumber) i USING (biblionumber)
WHERE title is not null and author is not null
GROUP BY title, author, itemtype
HAVING count_of_bibs > 1

Collection Development

Contents

Collection Development

Turnover Rate by Collection in Date Range

Turnover Rate for Call Number Range in Collection by Date Range

Relative Use by Collection with Turnover

Relative Use For Dewey Based Collections in Call Num Range with Turnover

Percentage of collection by collection code

Collection Evaluation Report

Collection Evaluation Report 2

A particular Title total number of times issued count in date range

Weeding

SuperWeeder 008

Weeding tool

CREW Friendly Weeding

Inventory & Accession Registers

Accession Register Sorted by Barcode Number Report

Updated Accession Register Sorted by Barcode Number Report

Inventory Report

Inventory Report

Inventory Report by Location

Accession Register Report by Branch

Duplicates

Duplicate barcodes after removing leading zeros

Duplicate bibs using the 001

Duplicate ISBNs

Duplicate ISBNs Alternative

Duplicate ISBNs in Time Frame

Duplicate ISBNs with Links to Bib Records

Duplicate ISBNs++ with Links to Bib Records and link to batch edit and combine

Duplicate EAN/UPC (024$a)

Duplicate titles (using author and title)

Duplicate titles (using title and ISBN)

Duplicate titles (using title and ISBN), multi-branch version

Duplicate titles (with same date)

Duplicate titles having both DVD items and others

Duplicate 001 fields with OCLC prefixes removed

Duplicate title and author combinations within an item type

Navigation menu

Search