Cataloging - Bibliographic Reports

From Koha Wiki
Jump to navigation Jump to search

Reports about anything to do with bibliographic records, catalog maintenance, etc.

NOTE: Due to the size of this page, reports pertaining solely to item records have been moved to a new page, reports pertaining to authority records have been moved to a new page, and reports pertaining to inventory, collection evaluation, and duplicates have been moved to the Collection Development Reports page.

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

Catalog Size

Record Counts

Count of all titles

  • Developer: Michael Hafen
  • Module: Catalog
  • Purpose: Count of all titles
  • Status: Complete
  SELECT COUNT(biblio.title) AS Count FROM biblio

Total collection size

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Total collection size
  • Status: Complete
  SELECT count(i.biblionumber) AS added, i.itype, i.homebranch, i.location 
  FROM items i 
  WHERE i.dateaccessioned < <<Acquired before (yyyy-mm-dd)|date>>  
  GROUP BY i.homebranch,i.itype,i.location 
  ORDER BY i.homebranch,i.itype,i.location ASC

Total Collection Size by Date

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Total collection size by item type and branch by a specific date (for example first of the month)
  • Status: Complete
select COALESCE(homebranch,'*GRAND TOTAL*') as homebranch, 
       IFNULL(itype, "") as itype, count(itype) as count 
from items 
where dateaccessioned < <<Added before (yyyy-mm-dd)|date>> 
group by homebranch, itype 
with rollup

By Age

Age of collection by copyright date

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Count number of Items in collection, grouped by copyright date range.
  • Status: Complete
SELECT
    CONCAT(
        ( copyrightdate DIV 5 ) * 5,
        ' - ',
        ( copyrightdate DIV 5 ) * 5 + 4
    ) AS 'Copyright date range',
    COUNT(*) AS 'Count of items'
FROM
    biblio
    INNER JOIN items USING (biblionumber)
WHERE copyrightdate IS NOT NULL
GROUP BY copyrightdate DIV 5
ORDER BY copyrightdate DIV 5

Average Age by Collection Code

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: This report averages the publication year of titles in your collection to get an age report
  • Status: Complete
select round(avg(b.copyrightdate)) as 'average year' 
from biblio b
left join items i 
using (biblionumber) 
where b.copyrightdate is not null and i.ccode = <<Collection|CCODE>>

Average age of collection by item type

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Average age of collection by item type uses the 008 date field
  • Status: Complete
SELECT itype, avg(mid(ExtractValue(metadata,'//controlfield[@tag="008"]'),8,4))
FROM biblio_metadata
LEFT JOIN items USING (biblionumber)
WHERE SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'),8,4) REGEXP '[0-9]{4}'
GROUP BY itype

By Location

Count of collection by Dewey 10s (tens) optional branch/itemtype limit

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: Counts number of items in a dewey 10s group, will ignore 1 prefix followed by space
  • Status: Complete
SELECT
    CONCAT(IF(
        itemcallnumber REGEXP '^[^0-9]+[[:space:]][0-9]{3}[^0-9]+.*', 
        LEFT(SUBSTR(itemcallnumber,LOCATE(' ',itemcallnumber)+1),2) ,
        LEFT(itemcallnumber,2)
    ),'0') AS Dewey10,
    COUNT(itemnumber)
FROM items
WHERE
    itemcallnumber REGEXP'^[0-9]{3}[^0-9]+.*|^[0-9]{3}$|^[^0-9]+[[:space:]][0-9]{3}[^0-9]+.*|^[0-9]{3}$'
    AND IF(<<Limit by homebranch|YES_NO>>,homebranch=<<Branch|branches>>,1)
    AND IF(<<Limit by itemtype|YES_NO>>,itype=<<Itemtype|itemtypes>>,1)
GROUP BY Dewey10

Count of all Bibs and Items per Branch

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: A count of all unique bibs and total items held at each branch
  • Status: Complete
SELECT homebranch, count(DISTINCT biblionumber) as bibs, 
       count(itemnumber) AS items 
FROM items 
GROUP BY homebranch 
ORDER BY homebranch ASC

Statistical Count of total number of items held by each branch

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Statistical Count of total number of items held by each branch all in one report
  • Status: Complete
  SELECT homebranch,count(itemnumber) as items 
  FROM items 
  GROUP BY homebranch 
  ORDER BY homebranch asc

Other Breakdowns

Count of all Bibs and Items by item type

  • Developer: Agnes Rivers-Moore
  • Module: Catalog
  • Purpose: A count of titles and items by item type, with item type descriptions.
  • Status: Complete
SELECT items.itype, itemtypes.description, count(DISTINCT items.biblionumber) AS bibs, 
count(items.itemnumber) AS items 
FROM items, itemtypes
WHERE items.itype=itemtypes.itemtype AND items.barcode IS NOT NULL 
GROUP BY items.itype
ORDER BY itemtypes.description

Count of all Bibs and Items by item type (avoids counting bibs twice, if they have multiple item types attached.)

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: A count of titles and items by item type, with item type descriptions. Excluding specific item type.
  • Status: Complete
  • Version: 23.05
SELECT 
    biblio_data.`Item Type`,
    COALESCE(itemtypes.description, 'Total') AS `Description`,
    biblio_data.`Title Count`,
    item_data.`Item Count`
FROM 
    (
        -- Count of bibliographic records by item type
        SELECT 
            ExtractValue(metadata, '//datafield[@tag="942"]/subfield[@code="c"]') AS `Item Type`,
            COUNT(biblio.biblionumber) AS `Title Count`
        FROM 
            biblio
        LEFT JOIN 
            biblio_metadata USING (biblionumber)
        WHERE 
            ExtractValue(metadata, '//datafield[@tag="942"]/subfield[@code="c"]') != 'CR'
        GROUP BY 
            ExtractValue(metadata, '//datafield[@tag="942"]/subfield[@code="c"]')
    ) AS biblio_data
LEFT JOIN 
    (
        -- Count of distinct items by item type
        SELECT 
            itype AS `Item Type`,
            COUNT(itemnumber) AS `Item Count`
        FROM 
            items
        GROUP BY 
            itype
    ) AS item_data
ON 
    biblio_data.`Item Type` = item_data.`Item Type`
LEFT JOIN 
    itemtypes ON item_data.`Item Type` = itemtypes.itemtype

UNION ALL

-- Total line
SELECT 
    '' AS `Item Type`,
    'Total' AS `Description`,
    SUM(biblio_data.`Title Count`) AS `Title Count`,
    SUM(item_data.`Item Count`) AS `Distinct Item Count`
FROM 
    (
        -- Count of bibliographic records by item type
        SELECT 
            ExtractValue(metadata, '//datafield[@tag="942"]/subfield[@code="c"]') AS `Item Type`,
            COUNT(biblio.biblionumber) AS `Title Count`
        FROM 
            biblio
        LEFT JOIN 
            biblio_metadata USING (biblionumber)
        WHERE 
            ExtractValue(metadata, '//datafield[@tag="942"]/subfield[@code="c"]') != 'CR'
        GROUP BY 
            ExtractValue(metadata, '//datafield[@tag="942"]/subfield[@code="c"]')
    ) AS biblio_data
LEFT JOIN 
    (
        -- Count of distinct items by item type
        SELECT 
            itype AS `Item Type`,
            COUNT(itemnumber) AS `Item Count`
        FROM 
            items
        GROUP BY 
            itype
    ) AS item_data
ON 
    biblio_data.`Item Type` = item_data.`Item Type`

Count of biblios on each OAI set

SELECT
  oai_sets.name AS "Set",
  COUNT(oai_sets_biblios.biblionumber) AS "Count"
FROM oai_sets_biblios
LEFT JOIN oai_sets ON (oai_sets.id=oai_sets_biblios.set_id) 
WHERE oai_sets_biblios.biblionumber IN (SELECT biblionumber FROM biblio)  /* to remove deleted biblios*/
GROUP BY oai_sets.name
ORDER BY oai_sets.name ASC

Count of URL's from 856

  • Developer: From listserv provided to David Schuster
  • Module: Catalog
  • Purpose: count of URL's from 856
  • Status: Complete
SELECT count(*) FROM biblioitems where biblioitems.url != 'null';

Shelflists

Records with item count

  • Developer: Heather Braum and Paul A at the request of Satish MV
  • Module: Catalog
  • Purpose: Records with item count
  • Status: Complete
  • Note: Revised by Matthew Charlesworth to provide correct link to Staff Interface
SELECT DISTINCT
 CONCAT('<a title="Search for all records sharing the title:',b.title,'" href="/cgi-bin/koha/catalogue/search.pl?q=ti%3A',
 REPLACE(REPLACE (b.title, ' ', '+'),'?',''),'">Search</a>') AS "Search for Title",
 b.biblionumber,
 CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'">',b.title,'</a>') AS "Item Title",
 b.author,
 t.editionstatement,
 t.publishercode,
 t.isbn,
 count(i.biblionumber) AS "Copies"
FROM biblio b LEFT JOIN biblioitems t USING(biblionumber)
LEFT JOIN items i USING(biblionumber)
GROUP BY b.biblionumber
ORDER BY Copies ASC

Title/Subtitle List

  • Developer: Katrin Fischer
  • Module: Catalog
  • Purpose: List of full titles (title and subtitle) with call numbers
  • Status: Completed
select concat(b.title, ' ', ExtractValue((
    SELECT marcxml 
    FROM biblioitems b2
    WHERE b.biblionumber = b2.biblionumber),
      '//datafield[@tag="245"]/subfield[@code="b"]')) as title, 
    b.author, i.itemcallnumber from biblio b left join items i on (i.biblionumber=b.biblionumber)

Titles without leading articles (by 245 second indicators)

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: Useful if you need a report alphabatized by title no including things like A, AN, THE etc.
  • Status: Complete
SELECT SUBSTRING(title,ExtractValue(marcxml,'//datafield[@tag="245"]/@ind2')+1) AS Title
FROM biblio
LEFT JOIN biblioitems USING (biblionumber)
ORDER BY Title

Catalog without withdrawn items

  • Developer: Asif Nawab
  • Module: Catalog
  • Purpose: Accession Register without withdraw items.
  • Status: Completed
SELECT items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,
biblioitems.publishercode,biblioitems.place,biblio.copyrightdate,items.itype,items.ccode,items.price,items.withdrawn
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.withdrawn != 1
ORDER BY items.itype ASC

Changes to Bibliographic Records

New Records

Bibs Marked On Order

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: List all the bib records that have been marked as on order
  • Status: Complete
select b.title, b.author, i.barcode, 
       i.itemcallnumber, b.copyrightdate 
from biblio b
left join items i using (biblionumber)
where i.notforloan = '-1' 
order by b.title

List of bibliographic record numbers of bibs with items On Order

  • Developer: Heather Hernandez
  • Module: Catalog
  • Purpose: List bib numbers of on order bib records to facilitate export of bib records
  • Status: Complete
   SELECT b.biblionumber
   FROM biblio b 
   LEFT JOIN items i using (biblionumber)where i.notforloan = '-1'
   ORDER BY b.biblionumber

New Arrivals by Branch

  • Developer: Mahesh Palamuttath
  • Module: Catalog
  • Purpose: Get the details of newly added books under a specific branch library (can also be used for OPAC Coverflow)
  • Status: Completed
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ''
AND i.homebranch = <<Branch|branches>>
GROUP BY biblionumber
HAVING isbn != ""
ORDER BY rand()
LIMIT 30

List of biblio by item type

  • Developer: Jameela P; NRL Panini
  • Module: Catalog
  • Purpose: This report lists the biblio items by its item type. (includes bibliographic info)
  • Status: Complete
  SELECT
    items.barcode, items.dateaccessioned, items.booksellerid, items.homebranch, items.price, items.replacementprice,  items.itemcallnumber,  items.holdingbranch,  biblio.title, biblio.author,  biblioitems.isbn

FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 

WHERE items.itype=<<Itemtype|itemtypes>>GROUP BY items.barcode ORDER BY items.barcode asc

List of Catalogue records Modified/Added/Deleted by Staff

  • Developer: Vinod Kumar Mishra
  • Module: Catalog
  • Purpose: It gives a list of records modified/added/deleted in a data range by library staff.
  • Status: Complete
  • Note: Catalog Log should be activated in Koha System preferences
SELECT concat(p.firstname, ' ', p.surname) 'Staff Name',p.cardnumber 'Staff Card Number',a.action'Action',a.timestamp'Time',a.object 'Record No',a.info 'Description'

FROM action_logs a
LEFT JOIN borrowers p ON (a.user=p.borrowernumber)
LEFT JOIN items c ON (a.info=c.itemnumber)
LEFT JOIN biblio d ON (c.biblionumber=d.biblionumber)
LEFT JOIN borrowers k ON (a.object=k.borrowernumber)
where a.timestamp BETWEEN <<Date Between (dd/mm/yyyy)|date>> AND <<and (dd/mm/yyyy)|date>> AND a.module='CATALOGUING'

New Bib Records between dates (verbose)

  • Developer: Pablo Bianchi
  • Module: Catalog
  • Purpose: List new bibs in specific time frame but giving a lot of information. Remove AND al.action LIKE 'ADD' if you just want biblio log between dates.
  • Status: Complete
SELECT
    al.user AS Borrowernumber,
    p.cardnumber AS 'CardNumber',
    p.userid AS 'Username',
    concat(p.surname, ', ', p.firstname) AS "Surname, Name",
    al.action_id,
    al.timestamp,
    al.action,
    al.object AS "Biblionumber",
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber, '\">', b.title, '</a>' ) AS "Title",
    ExtractValue(bm.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",
    ExtractValue(bm.metadata, '//datafield[@tag="245"]/subfield[@code="n"]') AS "Part name",
    ExtractValue(bm.metadata, '//datafield[@tag="245"]/subfield[@code="p"]') AS "Part",
    b.author AS 'Author',
    b.copyrightdate AS 'Year',
    al.info
FROM action_logs al
LEFT JOIN borrowers p ON  (al.user = p.borrowernumber)
LEFT JOIN biblio b ON (al.object = b.biblionumber)
LEFT JOIN biblioitems bi ON (al.object = bi.biblionumber)
LEFT JOIN biblio_metadata bm ON (al.object = bm.biblionumber)
WHERE al.module='CATALOGUING'
    AND al.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
    AND al.info LIKE 'biblio%'
    AND al.action LIKE 'ADD'
ORDER BY al.action_id DESC

New Bib Records between dates

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: List new bibs in specific time frame
  • Status: Complete
select monthname(datecreated) as month, year(datecreated) as year, count(biblionumber) as count 
from biblio 
where datecreated between <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
group by year(datecreated), month(datecreated)

Titles added in date range using 005

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: List titles added between a date range listed in the 005
  • Status: Complete
select b.title, m.isbn, b.biblionumber 
from biblio b l
left join bibioitems m using (biblionumber) 
where ExtractValue(m.marcxml,'//controlfield[@tag="005"]') between DATE_FORMAT(<<Added between (yyyy-mm-dd)|date>>, '%Y%m%d%H%i%s') 
      and DATE_FORMAT(<<and (yyyy-mm-dd)|date>>, '%Y%m%d%H%i%s')

7 Random new titles for coverflow plugin

  • Developer: Ed Veal and Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: This will list 7 random new titles for the coverflow plugin
  • Status: Complete
SELECT b.biblionumber, m.isbn, b.title
  FROM items i
  LEFT JOIN biblioitems m using (biblioitemnumber)
  LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
  WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned and m.isbn is not null and m.isbn != ''
  ORDER BY rand()
  LIMIT 7

New items added since selected date with cover images

  • Developer: Jason Robb
  • Module: Catalog
  • Purpose: Gathers items since a specific accession date, normalizes the ISBN and tries to render images from Amazon within the report results
  • Status: Complete
  • Version: 21.05
SELECT
    items.dateaccessioned,
    items.barcode,
    items.itemcallnumber,
    biblio.author,
    concat(biblio.title, ' ', ExtractValue((
        SELECT metadata 
        FROM biblio_metadata
        WHERE biblio.biblionumber = biblio_metadata.biblionumber),
         '//datafield[@tag="245"]/subfield[@code="b"]')) AS FullTitle,
    biblioitems.isbn,
    biblio.copyrightdate,
    ExtractValue( biblio_metadata.metadata, '//datafield[@tag="650"]/subfield[@code="a"]' ) AS 'lcsh',
    COALESCE(CONCAT('<img src="https://images-na.ssl-images-amazon.com/images/P/',
  IF
  (LEFT(TRIM(biblioitems.isbn), 3) = '978',
    CONCAT(SUBSTR(TRIM(biblioitems.isbn), 4, 9),
    REPLACE(MOD(11 - MOD
        (CONVERT(SUBSTR(TRIM(biblioitems.isbn), 4, 1), UNSIGNED INTEGER)*10 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 5, 1), UNSIGNED INTEGER)*9 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 6, 1), UNSIGNED INTEGER)*8 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 7, 1), UNSIGNED INTEGER)*7 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 8, 1), UNSIGNED INTEGER)*6 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 9, 1), UNSIGNED INTEGER)*5 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 10, 1), UNSIGNED INTEGER)*4 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 11, 1), UNSIGNED INTEGER)*3 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 12, 1), UNSIGNED INTEGER)*2, 11
	  ), 11), '10', 'X')
	),
    LEFT(TRIM(biblioitems.isbn), 10)
  ),
     '.01.MZZZZZZZZZ.jpg">')) AS Render  
FROM biblio, items, biblioitems, biblio_metadata
WHERE biblio.biblionumber = items.biblionumber 
     AND biblio.biblionumber = biblio_metadata.biblionumber
     AND biblioitems.biblionumber = biblio.biblionumber
     AND items.dateaccessioned  >= <<Items added on or after (yyyy-mm-dd)|date>>
     AND items.homebranch LIKE <<Choose Library|branches>>
     AND biblio.copyrightdate >= <<Titles published on or after (YYYY), or a % symbol for no limit>>
GROUP BY FullTitle
ORDER BY items.itemcallnumber, biblio.copyrightdate DESC, items.itemcallnumber
LIMIT 10000

Records added by particular employee, in given date range, in pivot table by its type (MARC21 LDR position 6) and source (MARC21 003)

  • Developer: Michał Kula
  • Module: Catalog
  • Purpose: The query is a bit complicated, but the result is intuitive: basically what a given employee catalogued in a given time range.
  • Note: The SELECT * FROM (WITH ... SELECT ...) b is a workaround for Bug 39259
  • Status: Complete
  • Version: 24.05
  • Preview:
Typ NUKAT DNB Other Total
a - Language material
c - Notated music 1 1
d - Manuscript notated music
e - Cartographic material
f - Manuscript cartographic material
g - Projected medium
i - Nonmusical sound recording
j - Musical sound recording
k - Two-dimensional nonprojectable graphic
m - Computer file
o - Kit
p - Mixed materials
r - Three-dimensional artifact or naturally occurring object
t - Manuscript language material
Invalid
Total 1 1 
SELECT * FROM (
    WITH
        bib_ids AS (
            SELECT object AS bib_id FROM action_logs WHERE module = 'CATALOGUING' AND action = 'ADD' AND info = 'biblio'
            AND user = (
                SELECT borrowernumber FROM borrowers WHERE borrowers.userid = <<Username>> OR borrowers.cardnumber = <<Username>>
            )
            AND timestamp BETWEEN <<Date from|date>> AND <<Date to|date>>
        ),
        bibs AS (
            SELECT * FROM biblio_metadata WHERE biblio_metadata.biblionumber IN (SELECT * FROM bib_ids)
            UNION
            SELECT * FROM deletedbiblio_metadata WHERE deletedbiblio_metadata.biblionumber IN (SELECT * FROM bib_ids) AND <<Including deleted|YES_NO>>
        ),
        bibs2 AS (
            SELECT ExtractValue(bibs.metadata, '//controlfield[@tag=003]') AS f003, SubString(ExtractValue(bibs.metadata, '//leader') FROM 7 FOR 1) AS lt, count(*) AS cnt
            FROM bibs
            GROUP BY ExtractValue(bibs.metadata, '//controlfield[@tag=003]'), SubString(ExtractValue(bibs.metadata, '//leader') FROM 7 FOR 1)
        )
    SELECT `Type`, `NUKAT`, `DNB`, `Other`, `Total` FROM (

        SELECT "a - Language material" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "a") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "a") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "a") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "a") AS `Total`

        UNION

        SELECT "c - Notated music" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "c") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "c") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "c") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "c") AS `Total`

        UNION

        SELECT "d - Manuscript notated music" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "d") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "d") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "d") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "d") AS `Total`

        UNION

        SELECT "e - Cartographic material" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "e") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "e") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "e") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "e") AS `Total`

        UNION

        SELECT "f - Manuscript cartographic material" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "f") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "f") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "f") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "f") AS `Total`

        UNION

        SELECT "g - Projected medium" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "g") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "g") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "g") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "g") AS `Total`

        UNION

        SELECT "i - Nonmusical sound recording" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "i") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "i") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "i") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "i") AS `Total`

        UNION

        SELECT "j - Musical sound recording" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "j") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "j") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "j") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "j") AS `Total`

        UNION

        SELECT "k - Two-dimensional nonprojectable graphic" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "k") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "k") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "k") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "k") AS `Total`

        UNION

        SELECT "m - Computer file" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "m") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "m") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "m") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "m") AS `Total`

        UNION

        SELECT "o - Kit" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "o") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "o") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "o") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "o") AS `Total`

        UNION

        SELECT "p - Mixed materials" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "p") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "p") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "p") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "p") AS `Total`

        UNION

        SELECT "r - Three-dimensional artifact or naturally occurring object" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "r") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "r") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "r") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "r") AS `Total`

        UNION

        SELECT "t - Manuscript language material" AS Type, (SELECT cnt FROM bibs2 WHERE f003 IN ("NUKAT") AND lt = "t") AS NUKAT, (SELECT cnt FROM bibs2 WHERE f003 IN ("DNB") AND lt = "t") AS `DNB`, (SELECT cnt FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt = "t") AS `Other`, (SELECT cnt FROM bibs2 WHERE lt = "t") AS `Total`

        UNION

        SELECT '<span style="text-decoration: underline; background-color: pink;">Invalid</span>' AS Typ, (SELECT SUM(cnt) FROM bibs2 WHERE f003 IN ("NUKAT") AND lt NOT IN ("a", "c", "d", "e", "f", "g", "i", "j", "k", "m", "o", "p", "r", "t")) AS NUKAT, (SELECT SUM(cnt) FROM bibs2 WHERE f003 IN ("DNB") AND lt NOT IN ("a", "c", "d", "e", "f", "g", "i", "j", "k", "m", "o", "p", "r", "t")) AS `DNB`, (SELECT SUM(cnt) FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB") AND lt NOT IN ("a", "c", "d", "e", "f", "g", "i", "j", "k", "m", "o", "p", "r", "t")) AS `Other`, (SELECT SUM(cnt) FROM bibs2 WHERE lt NOT IN ("a", "c", "d", "e", "f", "g", "i", "j", "k", "m", "o", "p", "r", "t")) AS `Total`

        UNION

        SELECT "Total" AS Typ, (SELECT SUM(cnt) FROM bibs2 WHERE f003 IN ("NUKAT")) AS NUKAT, (SELECT SUM(cnt) FROM bibs2 WHERE f003 IN ("DNB")) AS `DNB`, (SELECT SUM(cnt) FROM bibs2 WHERE f003 NOT IN ("NUKAT", "DNB")) AS `Other`, (SELECT SUM(cnt) FROM bibs2) AS `Total`
    ) a
) b

Deleted Records

All bibs where last item deleted

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: All bibs without items where the last item was deleted
  • Status: Complete
SELECT b.biblionumber, b.title, b.author
FROM biblio b
LEFT JOIN items i using (biblionumber) 
WHERE i.itemnumber IS NULL 
      and b.biblionumber in (select biblionumber from deleteditems)
group by b.biblionumber

All bibs where last item deleted in time frame

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: All bibs without items where the last item was deleted in a specific timeframe (often used for notifying OCLC of holdings changes)
  • Status: Complete
SELECT b.biblionumber, b.title, b.author
FROM biblio b
LEFT JOIN items i USING (biblionumber) 
WHERE i.biblionumber NOT IN (select biblionumber from items) AND 
      b.biblionumber IN (SELECT biblionumber FROM deleteditems where date(timestamp) = <<Deleted on (yyyy-mm-dd)|date>>)

All bibs where last item withdrawn in time frame

  • Developer: Fridolin Somers, Biblibre
  • Module: Catalog
  • Purpose: All bibs where the last item was withdrawn in a specific timeframe (consider deleting the record)
  • Status: Complete
SELECT
  CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'">Notice</a>') AS 'Notice',
  b.title AS 'Titre',
  b.author AS 'Auteur',
  MIN(i.withdrawn_on) AS 'Pilon depuis',
  COUNT(i.itemnumber) AS 'Nb exemplaires'
FROM items i JOIN biblio b ON(i.biblionumber = b.biblionumber)
WHERE b.biblionumber IN (
  SELECT biblionumber FROM items GROUP BY biblionumber
  HAVING COUNT(itemnumber) > 0 AND COUNT(IF(withdrawn = 1,1,NULL)) = COUNT(itemnumber)
)
AND i.homebranch LIKE <<Site|branches:all>>
AND IF(i.withdrawn_on,DATE(i.withdrawn_on),'2000-01-01') BETWEEN <<Piloné entre|date>> AND <<et|date>>
GROUP BY i.biblionumber
ORDER BY i.withdrawn_on DESC

Bib records added/deleted in time frame

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the bibs 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 'bibs'
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

Bib records marked deleted with leader/05='d'

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: See records marked deleted
  • Status: Complete
SELECT biblionumber, title, author, ExtractValue(metadata,'//leader') AS "Leader Field", SUBSTRING(ExtractValue(metadata,'//leader'),6,1) AS "Position05"
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE SUBSTRING(ExtractValue(metadata,'//leader'),6,1) = 'd'

Bib records that contain only lost items

  • Developer: Barton Chittenden
  • Module: Statistical (Catalog)
  • Purpose: Find
  • Status: Complete
SELECT
        CONCAT(
            '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
            bib.biblionumber, 
            '\">', 
            bib.title, 
            '</a>'
        ) AS Title,
        bib.title,
        bib.biblionumber,
        lostitems.holdingbranch,
        lostitems.barcode,
        lostitems.ccode,
        lostitems.dateaccessioned as 'Accession date',
        lostitems.onloan as 'Checkout date'
from
        biblio bib
        left join items on (bib.biblionumber = items.biblionumber and items.itemlost = 0)
        left join items as lostitems on (bib.biblionumber = lostitems.biblionumber and lostitems.itemlost != 0)
group by bib.biblionumber
having count(items.itemnumber) = 0

Deleted Titles List to Send to OCLC

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides title, author, isbn and OCLC number of deleted titles at a branch in a specific time period for sending to OCLC to update your holdings in a batch.
  • Status: Completed
SELECT b.title, b.author, m.isbn, 
       ExtractValue(bm.metadata, '//controlfield[@tag="001"]') AS 'OCLC Number 001'
FROM biblio b
LEFT JOIN deleteditems i USING (biblionumber)
LEFT JOIN biblioitems m USING (biblionumber)
INNER JOIN biblio_metadata bm on bm.biblionumber = b.biblionumber and bm.format = 'marcxml'
WHERE b.biblionumber not in (select biblionumber from items) 
      and i.homebranch=<<Branch|branches>> and 
      i.timestamp between <<Deleted between (yyyy-mm-dd)|date>> 
      and <<and (yyyy-mm-dd)|date>>

Deleted biblio data

  • Developer: Alex Buckley, Catalyst IT for Toi Ohomai Institute of Technology, New Zealand
  • Module: Catalog
  • Purpose: Given a biblio title this report will list information the biblio if it has been deleted.
  • Status: Completed
SELECT
  deletedbiblio.title,
  deletedbiblio.biblionumber,
  deletedbiblio.author,
  deletedbiblio.frameworkcode,
  deletedbiblio.unititle,
  deletedbiblio.notes,
  deletedbiblio.seriestitle,
  deletedbiblio.copyrightdate,
  deletedbiblio.timestamp,
  deletedbiblio.datecreated,
  deletedbiblio.abstract,
  deletedbiblio_metadata.format,
  deletedbiblio_metadata.metadata
FROM
  deletedbiblio
  LEFT JOIN deletedbiblio_metadata ON deletedbiblio.biblionumber = deletedbiblio_metadata.biblionumber
WHERE
  deletedbiblio.title = <<Enter a title>>

List of items associated with a deleted biblio

  • Developer: Alex Buckley, Catalyst IT for Toi Ohomai Institute of Technology, New Zealand
  • Module: Catalog
  • Purpose: Finds all the item(s) associated with a deleted biblio.
  • Status: Completed
SELECT
  deletedbiblioitems.biblioitemnumber AS itemnumber,
  deleteditems.barcode,
  deletedbiblioitems.volume,
  deletedbiblioitems.number,
  deletedbiblioitems.isbn,
  deletedbiblioitems.issn,
  deletedbiblioitems.ean,
  deletedbiblioitems.publicationyear,
  deletedbiblioitems.publishercode,
  deletedbiblioitems.volumedate,
  deletedbiblioitems.volumedesc,
  deletedbiblioitems.collectiontitle,
  deletedbiblioitems.collectionvolume,
  deletedbiblioitems.editionstatement,
  deletedbiblioitems.editionresponsibility,
  deletedbiblioitems.illus,
  deletedbiblioitems.pages,
  deletedbiblioitems.notes,
  deletedbiblioitems.size,
  deletedbiblioitems.place,
  deletedbiblioitems.lccn,
  deletedbiblioitems.url,
  deletedbiblioitems.cn_source,
  deletedbiblioitems.cn_class,
  deletedbiblioitems.cn_item,
  deletedbiblioitems.cn_suffix,
  deletedbiblioitems.cn_sort,
  deletedbiblioitems.agerestriction,
  deletedbiblioitems.totalissues,
  deleteditems.dateaccessioned,
  deleteditems.booksellerid,
  deleteditems.homebranch,
  deleteditems.price,
  deleteditems.replacementprice,
  deleteditems.replacementpricedate,
  deleteditems.datelastborrowed,
  deleteditems.datelastseen,
  deleteditems.stack,
  deleteditems.notforloan,
  deleteditems.damaged,
  deleteditems.itemlost,
  deleteditems.itemlost_on,
  deleteditems.withdrawn,
  deleteditems.withdrawn_on,
  deleteditems.itemcallnumber,
  deleteditems.coded_location_qualifier,
  deleteditems.issues,
  deleteditems.renewals,
  deleteditems.reserves,
  deleteditems.restricted,
  deleteditems.itemnotes,
  deleteditems.itemnotes_nonpublic,
  deleteditems.holdingbranch,
  deleteditems.paidfor,
  deleteditems.location,
  deleteditems.permanent_location,
  deleteditems.onloan,
  deleteditems.ccode AS Collection,
  deleteditems.materials,
  deleteditems.uri,
  deleteditems.more_subfields_xml,
  deleteditems.enumchron,
  deleteditems.copynumber,
  deleteditems.stocknumber,
  deleteditems.new_status
FROM
  deletedbiblio
  LEFT JOIN deletedbiblio_metadata ON deletedbiblio.biblionumber = deletedbiblio_metadata.biblionumber
  LEFT JOIN deleteditems ON deletedbiblio_metadata.biblionumber = deleteditems.biblionumber
  LEFT JOIN deletedbiblioitems ON deletedbiblio_metadata.biblionumber = deletedbiblioitems.biblionumber
WHERE
  deletedbiblio.title = <<Enter a title>>

Withdrawn Titles List to Send to OCLC

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides title, author and OCLC number of withdrawn titles for sending to OCLC to update your holdings in a batch.
  • Status: Completed
select b.title, b.author, ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') AS 'OCLC Number' 
from biblio b 
left join items i using (biblionumber) 
left join biblioitems m using (biblionumber)
where i.withdrawn > 0

Records with Specific Attributes

Bibs with a specific MARC field

  • Developer: Cab Vinton
  • Module: Catalog
  • Purpose: This report lists all biblionumbers for records containing a specific MARC field
  • Status: Complete
SELECT
    biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS link
FROM
    biblio_metadata
WHERE
    ExtractValue(metadata,'//datafield/@tag') REGEXP <<MARC field>>

Bibs with a specific MARC field II

  • Developer: Pablo López Liotti - UNMDP - (based in previous Cab Vinton's work)
  • Module: Catalog
  • Purpose: Lists biblionumbers with links to records containing a specific MARC field given and field content (first ocurrence and all subfields) extracted from XML Marc.
  • Status: Complete
  • Version: 17.xx to 20.xx
SELECT
    CONCAT('<a target="_blank" title="Show record with THIS biblionumber" 
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
biblionumber, '\">', biblionumber, '</a>' ) AS Record,
    ExtractValue(metadata, "//datafield[@tag=<<MARC Field>>]/*") AS Field_Content
FROM
    biblio_metadata
WHERE
    ExtractValue(metadata,'//datafield/@tag') REGEXP <<MARC Field>>

All titles with 008 for Continuing Resource

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report lists all titles that are coded as continuing resources in the 008
  • Status: Complete
SELECT b.title, m.issn
FROM biblioitems m 
LEFT JOIN biblio b USING (biblionumber) 
WHERE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),22,1) in ('d','l','m','n','p','w')

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author, ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AS 'Μεταβλητή'
FROM biblio_metadata
JOIN biblio USING (biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

Biblios Leader position 06 and 942 subfield c

  • Developer: Elaine Bradtke
  • Module: Catalog
  • Purpose: This report displays the Biblionumber and contents of leader 06, selected by item type from 942$c, useful for finding mismatch in Leader and item type.
  • Status: Complete
SELECT biblio.biblionumber, SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]' ) = <<Item Type|itemtypes>>

Bibs Suppressed in OPAC

  • Developer: Chris Hobbs, New Haven Unified School District
  • Module: Catalog
  • Purpose: Finds all bibs that have been flagged as Suppressed in 942$n
  • Status: Completed
  SELECT concat( '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '">', biblio.title, '</a>' ) AS title, biblio.author
  FROM biblio_metadata
  JOIN biblio ON ( biblio_metadata.biblionumber = biblio.biblionumber )
  WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="n"]' )
  IN ('Y', '1')

Bibs with Series info

  • Developer: Joy Nelson, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records with series info
  • Status: Complete
SELECT i.biblionumber, i.itemnumber, i.barcode, i.itemcallnumber, i.location, i.itype, 
       b.title, b.author, i.enumchron, b.seriestitle, 
       ExtractValue(bi.marcxml,'//datafield[@tag="830"]/subfield[@code="a"]') AS Series 
FROM items i 
LEFT JOIN biblio b using (biblionumber) 
LEFT JOIN biblioitems bi on (b.biblionumber=bi.biblionumber)

Bibs with specific keyword in subjects

  • Developer: Chris Cormack & Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: This report shows all bib records with a subject that contains a specific keyword in the 650a
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber, lcsh 
FROM 
(SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code="a"]')
AS lcsh FROM biblioitems) 
AS subjects 
WHERE lcsh LIKE CONCAT( '%', <<Subject>>, '%' )

Find biblionumbers for marc records containing '�'

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Records imported with character-set mis-matches will often contain the unicode REPLACEMENT CHARACTER '�', represented in UTF-8 as 0xEFBFBD. This query can be used to find these characters.
  • Status: Complete
  • Notes: For some reason, this does not return records when run as a report. It works in koha-mysql however.
select
    biblionumber
from
    biblio_metadata
where
    hex(metadata) like '%EFBFBD%'

Find biblionumbers of marc records containing non-ascii characters

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Find marc records containing non-ascii characters.
  • Status: Complete
  • Notes: This will return biblionumbers for records that use the copyright symbol, so might not be as useful as it might otherwise be.
SELECT
    biblionumber
FROM
    biblio_metadata
WHERE
    metadata <> CONVERT(metadata USING ASCII)

Language Material Bibs

  • Developer: Chris Cormack, Catalyst and Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: List of biblionumbers where the leader says 'language material' and has a specific item type.
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
       AS biblionumber 
FROM biblioitems,
  (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,1) 
   AS leader6 FROM biblioitems) 
AS leaders 
WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND leaders.leader6 = 'a' 
      AND itemtype = <<Item Type|itemtypes>>

List all records with at least one subject

  • Developer: David Cook
  • Module: Catalog
  • Purpose: This report creates a list of all records with at least one subject tag. It also lists those subject tags and the biblionumber for the record.
  • Status: Complete
SELECT biblionumber,ExtractValue(marcxml,'//datafield[substring(@tag,1,1) = "6"]/@tag') as 'Subject Tags' -- ,marcxml
FROM biblioitems
WHERE ExtractValue(marcxml,'//datafield[substring(@tag,1,1) = "6"]/@tag')
ORDER BY `Subject Tags`;

List of all normalized ISBNs

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of all ISBNs in your system normalized using the code from Nick Clemens.
  • Status: Complete
SELECT
  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 
FROM biblioitems i 
WHERE i.isbn IS NOT NULL AND i.isbn != ''

List records with notes by note tag

  • Developer: David Cook, Prosentient Systems
  • Module: Catalog
  • Purpose: List bibliographic records that have notes fields, and list those note fields by tag number
  • Status: Complete
SELECT biblionumber,ExtractValue(marcxml,'//datafield/@tag/text()[substring(.,1,1) = "5"]') as 'notes'
FROM biblioitems
HAVING notes <> ''

List of URL's from 856

  • Developer: LibLime provided to David Schuster
  • Module: Catalog
  • Purpose: List of URL's from 856
  • Status: Complete
  • Notes: Updated by Barton Chittenden (BWS).
SELECT biblio.biblionumber, SUBSTRING(biblio_metadata.metadata, LOCATE('<subfield code="u">',
       biblio_metadata.metadata, LOCATE('<datafield tag="856"', biblio_metadata.metadata)+19),
       LOCATE('</subfield>', biblio_metadata.metadata, LOCATE('<subfield code="u">',
       biblio_metadata.metadata, LOCATE('<datafield tag="856"',
       biblio_metadata.metadata)+19)) - LOCATE('<subfield code="u">', biblio_metadata.metadata,
       LOCATE('<datafield tag="856"', biblio_metadata.metadata)+19)) AS url
FROM biblioitems, biblio, biblio_metadata
WHERE
       biblioitems.biblionumber = biblio.biblionumber
       AND biblioitems.biblionumber = biblio_metadata.biblionumber
       AND url IS NOT NULL

I *think* that this query is addressing the fact that ExtractValue concatenates multiple tags into a single fileld. These can be individually addressed using an array index after [@tag=856]:

ExtractValue( metadata, '//datafield[@tag=856][1]/subfield[@code="u"]' ) as '856$u'

The index is 1-based, [@tag=856][1] is the first tag, [@tag=856][2] is the second, etc. It's probably possible to do something tricky here with a MySQL variable. Because I can't *exactly* tell what the original query is doing, I'm a bit loathe to dive in too deep, but I'd love to see this particular report get re-written more clearly.

URLs in Catalog

  • Developer: Lenora Oftedahl
  • Module: Catalog
  • Purpose: URLs in Catalog
  • Status: Needs work as I only want the URLs, not all barcodes
  SELECT  items.barcode,biblioitems.url 
  FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
  WHERE items.homebranch=<<Home branch|branches>>

Bibs with 856s

  • Developer: Myka Kennedy Stephens, Lancaster Theological Seminary
  • Module: Catalog
  • Purpose: list of all 856s, including all subfields, for each biblio that has at least one 856
  • Status: Complete
SELECT biblionumber,
     ExtractValue(metadata,'count(//datafield[@tag="856"])') AS count856,
     ExtractValue(metadata,'//datafield[@tag="856"]/*') AS link 
FROM biblio_metadata
HAVING count856 > 0

Biblio records with 856 (url)

  • Developer: Caroline Cyr La Rose, inLibro
  • Module: Catalog
  • Purpose: list of all biblionumbers of records that have a url in 856$u, to be used in match modification; Note: 856$u must be mapped to biblioitems.url in Administration > Koha to MARC mapping for this report to work
  • Status: Complete
SELECT
    biblionumber
FROM
    biblioitems
WHERE
    url IS NOT NULL

Materials based on item type from fixed fields

  • Developer: Nicole C. Baratta, ByWater Solutions and Melia Meggs, ByWater Solutions
  • Module: Catalog
  • Purpose: count of the collection by bibliographic item type as cataloged in the fixed fields.
  • Status: Completed
SELECT 
CASE SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,2) 
      WHEN 'am' THEN 'Book'
      WHEN 'as' THEN 'Serial'
      WHEN 'cm' THEN 'Score'
      WHEN 'em' THEN 'Map'
      WHEN 'gm' THEN 'Video recording and motion pictures'
      WHEN 'im' THEN 'Non-music sound recording'
      WHEN 'jm' THEN 'Music sound recording'
      WHEN 'mm' THEN 'Computer file'
      WHEN 'rm' THEN 'Three Dimensional item'
      WHEN 'tm' THEN 'Manuscript'
      ElSE 'unknown' END
   AS bibtype, count(DISTINCT biblionumber) as bibs from biblioitems
GROUP BY bibtype

Titles by General Materials Designation (MARC 245$h)

  • Developer: Ian Walls, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows each distinct GMD value in the catalog, with a count of titles for that value. Good for profiling materials, and spotting minor spelling errors
  • Status: Completed
SELECT ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="h"]') AS GMD, 
       count(*) AS COUNT
       From biblio_metadata 
       GROUP BY GMD ORDER BY COUNT DESC

List of Language Codes in 008 Positions 35-37 With Count of Bibs per Language

  • Developer: Andrew Fuerste-Henry, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows 008 positions 35-37 for use in determining Advanced Search languages
  • Status: Completed
SELECT Substring(ExtractValue(metadata,'//controlfield[@tag=008]'),36,3) as LANG, count(biblionumber) as BIBS
FROM biblio_metadata
GROUP BY lang
ORDER BY bibs DESC

Count of records based on MARC21 LDR position 6

  • Developer: George Williams
  • Module: Catalog
  • Purpose: Count of records based on data in LDR position 6 - highlights position 6 with invalid characters
  • Status: Complete
  • Version: Koha 23.05
SELECT
  SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) AS metadata,
  Count(biblio_metadata.biblionumber) AS BIBS,
  CASE
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'a' THEN 'a - Language material' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'c' THEN 'c - Notated music' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'd' THEN 'd - Manuscript notated music' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'e' THEN 'e - Cartographic material' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'f' THEN 'f - Manuscript cartographic material' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'g' THEN 'g - Projected medium' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'i' THEN 'i - Nonmusical sound recording'
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'j' THEN 'j - Musical sound recording' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'k' THEN 'k - Two-dimensional nonprojectable graphic' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'm' THEN 'm - Computer file' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'o' THEN 'o - Kit' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'p' THEN 'p - Mixed materials' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 'r' THEN 'r - Three-dimensional artifact or naturally occurring object' 
    WHEN SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = 't' THEN 't - Manuscript language material' 
    ELSE Concat('<span style="text-decoration: underline; background-color: pink;">',  SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ), ' - Invalid code</span>')
  END AS LDR_POSITION_6
FROM
  biblio_metadata
GROUP BY
  SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 )
ORDER BY
  metadata

List Marc21 records with a character you specify in LDR position 6

  • Developer: George Williams
  • Module: Catalog
  • Purpose: Lists Marc21 biblio records with the character you specify in LDR position 6 (Type of record)
  • Status: Complete
  • Version: Koha 23.05
SELECT
  SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) AS LDR_POSITION_6,
  biblio_metadata.biblionumber AS BIBLIO_NUMBER,
  Concat(
    '<a class="next_btn next_green noprint"', 
    'href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
    biblio_metadata.biblionumber, 
    '" target="_blank">Title</a>'
  ) AS LINK,
  ExtractValue(biblio_metadata.metadata, '//leader') AS LDR,
  ExtractValue(metadata,'//controlfield[@tag="006"]') AS FIELD_006,
  ExtractValue(metadata,'//controlfield[@tag="007"]') AS FIELD_007,
  ExtractValue(metadata,'//controlfield[@tag="008"]') AS FIELD_008,
  biblio.author,
  biblio.title,
  ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') AS BIB_ITYPE
FROM
  biblio_metadata JOIN
  biblio ON biblio_metadata.biblionumber = biblio.biblionumber
WHERE
  SubString(ExtractValue(biblio_metadata.metadata, '//leader') FROM 7 FOR 1 ) = <<Enter code for position 6>>

Quality Control

Mismatches

Bibs with diff item types attached

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records that have items with different item types attached.
  • Status: Complete
select b.title, b.author, b.biblionumber, count(distinct i.itype) as 'item types', 
       count(i.itemnumber) as items 
from biblio b 
left join items i using (biblionumber) 
group by b.biblionumber 
having count(distinct i.itype) > 1

Bibs with Different Item Types

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records that have multiple item types attached.
  • Status: Complete
select b.title, b.biblionumber, count(distinct itype) as types 
from biblio b 
left join items i using (biblionumber) 
group by i.biblionumber 
having count(distinct itype) > 1

Identify records with mismatched 008 vs Copyright/Publication date

  • Developer: Liz Rea, Catalyst IT, for New Zealand Educational Institute
  • Module: Catalog
  • Purpose: This report shows records that have a mismatch between the 008 publication date and the catalogued biblio.copyrightdate. This report can help identify records that have incorrect 008 fields so that the sorting by publication date remains consistent.
  • Status: Complete
SELECT CONCAT('<a target="new" href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>') AS Title,
  biblio.copyrightdate,
  control008.008pubdate 
FROM (SELECT biblionumber,
  SUBSTR(ExtractValue(biblioitems.marcxml,'//controlfield[@tag="008"]'),8,4) AS 008pubdate
  FROM biblioitems) AS control008 
JOIN biblio USING(biblionumber)  
WHERE biblio.copyrightdate != control008.008pubdate 
AND control008.008pubdate != '';

Identify records with mismatched 008 vs 260c/264c

  • Developer: Jason Robb, Southeast Kansas Library System
  • Module: Catalog
  • Purpose: Compares year from the 008 to the first year in the 260$c and/or 264$c and lists mismatched info.
  • Status: Complete
SELECT b.biblionumber,
       b.title,
       b.copyrightdate,
       SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'),8,4) AS '008date',
       ExtractValue(metadata, '//datafield[@tag="260"]/subfield[@code="c"]') as '260c',
       REGEXP_SUBSTR(ExtractValue(metadata, '//datafield[@tag="260"]/subfield[@code="c"]'), "[0-9]{4}") AS '260cstrip',
       ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') as '264c',
       REGEXP_SUBSTR(ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]'), "[0-9]{4}") AS '264cstrip'
FROM biblio b
LEFT JOIN biblio_metadata b2 USING (biblionumber)
GROUP BY b.biblionumber
HAVING ((260c <> '' AND 008date <> 260cstrip) OR (264c <> '' AND 008date <> 264cstrip))
LIMIT 5000

Mismatched Callnumbers

  • Developer: Abdullrahman Hegazy, Hamada
  • Module: Catalog
  • Purpose: Cataloging quality control, it shows the mismatched items callnumber in the same record.
  • Status: Complete
SELECT concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',item1.biblionumber,'>', item1.biblionumber,'</a>') as record,
  item1.itemcallnumber,
  item2.itemcallnumber
FROM `items` as item1 ,`items` as item2
WHERE item1.biblionumber=item2.biblionumber and
  item1.itemcallnumber<>item2.itemcallnumber
GROUP BY item1.biblionumber

Mismatches between 2 fields and 2 subfields

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: This report displays the Biblionumber, Title, and Author of all Biblios that Do Not Match 2 sets of fields and subfields.
  • Status: Complete
SELECT
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">', biblio.biblionumber, '</a>') AS biblionumber,
  biblio.title,
  biblio.author 
FROM
  biblioitems 
  JOIN
    biblio 
    ON (biblioitems.biblionumber = biblio.biblionumber) 
WHERE
  ExtractValue(marcxml, " // datafield[@tag =<<1st Set Field 1 (XXX)>> ] / subfield[@code =<<Subfield(Y)>> ]") AND
  ExtractValue(marcxml, " // datafield[@tag =<<1st Set Field 2 (XXX)>> ] / subfield[@code =<<Subfield(Y)>> ]") NOT LIKE
  ExtractValue(marcxml, " // datafield[@tag =<<2nd Set Field 1(XXX)>> ] / subfield[@code =<<Subfield(Y)>> ]") AND
  ExtractValue(marcxml, " // datafield[@tag =<<2nd Set Field 2(XXX)>> ] / subfield[@code =<<Subfield(Y)>> ]")

Mismatches between 2 collection codes

  • Developer: Jason Robb (SEKLS)
  • Module: Catalog
  • Purpose: Compare two collection codes for mismatches on a single record
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',i.biblionumber,'\" target="_blank">'"view"'</a>') AS "view",
i.biblionumber,
SUM(CASE WHEN i.ccode= <<Collection code 1|ccode>> THEN 1 ELSE 0 END) AS Count1,
SUM(CASE WHEN i.ccode= <<Collection code 2|ccode>> THEN 1 ELSE 0 END) AS Count2,
GROUP_CONCAT(DISTINCT(i.homebranch))
FROM items i
GROUP BY i.biblionumber
HAVING Count1 >= 1 AND Count2 >= 1

Conditional searching with REGEXP

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: Displays biblionumber, title, and author of records matching 1st Regular Expression excluding matches on 2nd Regular Expression.
  • Status: Complete
  • Version: Koha 23.05
SELECT
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">', biblio.biblionumber, '</a>') AS biblionumber,
  biblio.title,
  biblio.author 
FROM
  biblio_metadata 
  JOIN
    biblio 
    ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE
  ExtractValue(metadata,"//datafield[@tag =<<1st Compare Field (XXX)>>]/subfield[@code =<<1st Compare Subfield(Y)>>]") REGEXP <<FIND REGEX 1>>
AND NOT
  ExtractValue(metadata,"//datafield[@tag =<<2nd Compare Field (XXX)>>]/subfield[@code =<<2nd Compare Subfield(Y)>>]") REGEXP <<EXCLUDE REGEX 2>>

Null Island

Missing indicators of field with regex match on subfield

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: Displays the biblionumber, title, and author of records missing indicators in the specified field with regex match on it's subfield.
  • Status: Complete
  • Version: Koha 23.05
SELECT
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">', biblio.biblionumber, '</a>') AS biblionumber,
  biblio.title,
  biblio.author 
FROM
  biblio_metadata 
JOIN
  biblio 
  ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE
  ExtractValue(metadata,"//datafield[@tag = <<Field>> AND @ind1 = '' AND @ind2 = '']/subfield[@code=<<Subfield>>]") REGEXP(<<REGEX Search>>)

Bibs without subjects

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows all bibs without subject headings
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>') AS bibnumber 
FROM (
  SELECT biblionumber, ExtractValue(metadata,'//datafield[@tag="650"]/subfield[@code>="a"]') AS sub1, 
    ExtractValue(metadata,'//datafield[@tag="651"]/subfield[@code>="a"]') AS sub2, 
    ExtractValue(metadata,'//datafield[@tag="600"]/subfield[@code>="a"]') AS sub3, 
    ExtractValue(metadata,'//datafield[@tag="610"]/subfield[@code>="a"]') AS sub4, 
    ExtractValue(metadata,'//datafield[@tag="611"]/subfield[@code>="a"]') AS sub5, 
    ExtractValue(metadata,'//datafield[@tag="630"]/subfield[@code>="a"]') AS sub6, 
    ExtractValue(metadata,'//datafield[@tag="648"]/subfield[@code>="a"]') AS sub7, 
    ExtractValue(metadata,'//datafield[@tag="653"]/subfield[@code>="a"]') AS sub8, 
    ExtractValue(metadata,'//datafield[@tag="654"]/subfield[@code>="a"]') AS sub9, 
    ExtractValue(metadata,'//datafield[@tag="655"]/subfield[@code>="a"]') AS sub10, 
    ExtractValue(metadata,'//datafield[@tag="656"]/subfield[@code>="a"]') AS sub11, 
    ExtractValue(metadata,'//datafield[@tag="657"]/subfield[@code>="a"]') AS sub12, 
    ExtractValue(metadata,'//datafield[@tag="658"]/subfield[@code>="a"]') AS sub13, 
    ExtractValue(metadata,'//datafield[@tag="662"]/subfield[@code>="a"]') AS sub14 
  FROM biblio_metadata) AS subjects 
WHERE sub1  = "" 
  AND sub2  = "" 
  AND sub3  = "" 
  AND sub4  = "" 
  AND sub5  = "" 
  AND sub6  = "" 
  AND sub7  = "" 
  AND sub8  = "" 
  AND sub9  = "" 
  AND sub10 = "" 
  AND sub11 = "" 
  AND sub12 = "" 
  AND sub13 = "" 
  AND sub14 = ""

Records without ISBN

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

Alternate from Fridolin Somers, using JOIN :

SELECT items.itemcallnumber, biblioitems.isbn, concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') AS Title
FROM biblio
JOIN biblioitems ON( biblio.biblionumber = biblioitems.biblionumber)
JOIN items ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
WHERE (biblioitems.isbn IS NULL OR biblioitems.isbn ='')
GROUP BY items.biblionumber

Records without items

  • Developer: Magnus Enger
  • Module: Catalog
  • Purpose: Records without items, with links to OPAC and Intranet
  • Status: Complete
  • Note: Revised by Jared Camins-Esakov to provide correct link to OPAC based on OPACBaseURL
SELECT b.title AS Title,
  CONCAT('<a href=\"', IF(CHAR_LENGTH(systempreferences.value),
  CONCAT(systempreferences.value), ''), '/cgi-bin/koha/opac-detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS OPAC,
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS Edit  
FROM systempreferences, biblio AS b 
  LEFT JOIN items AS i ON b.biblionumber = i.biblionumber 
WHERE i.itemnumber IS NULL AND
  systempreferences.variable='OPACBaseURL'

All bibs without items

  • Developer: Frédéric Demians
  • Module: Catalog
  • Purpose: Get biblionumber of biblio records without items and which itemtype doesn't belongs to a list
  • Status: Complete
SELECT
 biblio.biblionumber
FROM
 biblio
RIGHT JOIN
 biblioitems
ON
 biblio.biblionumber = biblioitems.biblionumber
LEFT JOIN
 items
ON
 biblio.biblionumber = items.biblionumber
WHERE
 items.biblionumber IS NULL
 AND
 itype NOT IN ('AGH', 'PER');

All bibs without items - Simple

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Bib records without items
  • Status: Complete
SELECT biblionumber, title 
FROM biblio 
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)

All bibs without items - With link to biblio

  • Developer: Tomás Cohen
  • Module: Catalog
  • Purpose: Get biblionumber of biblio records without items
  • Status: Complete
SELECT CONCAT('<a href="http://',
     (SELECT value FROM systempreferences WHERE variable='staffClientBaseURL'),
     '/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
     biblionumber,
     '">',
     biblionumber,
     '</a>') AS 'biblionumber', title
FROM biblio
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)

All bibs without items - With link to biblio's add items screen

  • Developer: Barton Chittenden
  • Module: Catalog
  • Purpose: Quick access to add items screen for itemless bibs.
  • Status: Complete
SELECT
    CONCAT(
         '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',
         biblionumber,
         '\">', 
         title, '</a>' 
    ) AS 'Add Item to'
FROM biblio 
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)

Bib records with no 245

  • Developer: ByWater Staff
  • Module: Catalog
  • Purpose: Identify bibliographic records without 245 (title and statement of responsibility) field
  • Status: Complete
SELECT CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'">',biblionumber,'</a>') AS biblionumber ,title, author,ExtractValue(metadata,'//datafield[@tag="880"]/*') AS field_880
FROM biblio
JOIN biblio_metadata USING (biblionumber)
WHERE ExtractValue(metadata,'//datafield[@tag="245"]/*')=""

Validation

Find unused sequential barcode ranges

  • Developer: Jared Camins-Esakov
  • Module: Catalog
  • Purpose: Find ranges of unused barcodes.
  • Status: Completed
  • Note: This query takes a *long* time. Minutes, not seconds. This query will only work on non-checksummed, sequential numeric barcodes
SELECT Convert(l.barcode, UNSIGNED) + 1 AS start, MIN(Convert(fr.barcode, UNSIGNED)) - 1 AS stop
FROM items AS l
    LEFT OUTER JOIN items AS r ON Convert(l.barcode, UNSIGNED) = Convert(r.barcode, UNSIGNED) - 1
    LEFT OUTER JOIN items AS fr ON Convert(l.barcode, UNSIGNED) < Convert(fr.barcode, UNSIGNED)
WHERE r.barcode IS NULL AND fr.barcode IS NOT NULL
GROUP BY l.barcode, r.barcode
ORDER BY l.barcode

Missing barcode in a range

SELECT (i1.barcode + 1) as gap_starts_at, 
       (SELECT MIN(i3.barcode) -1 FROM items i3 WHERE i3.barcode > i1.barcode) as gap_ends_at
FROM items i1
# Range to check:
WHERE i1.barcode between 0 AND 10000 
AND NOT EXISTS (SELECT i2.barcode FROM items i2 WHERE i2.barcode = i1.barcode + 1)
HAVING gap_ends_at IS NOT NULL

Invalid barcode length

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Cataloging quality control "You have to enter the length used in your library"
  • Status: Complete
SELECT items.itemcallnumber,
  concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') as Title,
  items.biblionumber,
  items.barcode,
  CHAR_LENGTH(REPLACE(items.barcode, ' ', '')) as Length
FROM items
  LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE CHAR_LENGTH(REPLACE(barcode, ' ', '')) !=<<Length>> OR
  barcode is null
ORDER BY items.barcode,
  items.itemcallnumber,
  CHAR_LENGTH(REPLACE(barcode, ' ', ''))

Validate Codabar barcodes used by North American libraries

  • Developer: Jared Camins-Esakov
  • Module: Catalog
  • Purpose: Identifies barcodes that are invalid based on the rules at http://www.mecsw.com/specs/codabar.html
  • Status: Completed
  • Note: Change '8060' to the 4-digit code used by your library
SELECT
  biblionumber,
  barcode,
  CONCAT_WS('; ', lengthproblem, typeproblem, libraryproblem, checksumproblem) 
FROM
  (SELECT
    items.biblionumber AS biblionumber,
    items.barcode AS barcode,
    IF(CHAR_LENGTH(TRIM(items.barcode)) <> 14, 'Barcode wrong length', NULL) AS lengthproblem,
    IF(SUBSTR(TRIM(items.barcode), 1, 1) <> '3', 'Not an item barcode', NULL) AS typeproblem,
    IF(SUBSTR(TRIM(items.barcode), 2, 4) <> '8060', 'Wrong library code', NULL) AS libraryproblem,
    IF(MOD(10 - MOD((IF(SUBSTR(TRIM(items.barcode), 1, 1) * 2 >= 10, 
    (SUBSTR(TRIM(items.barcode), 1, 1) * 2) - 9,
    SUBSTR(TRIM(items.barcode), 1, 1) * 2)) + (SUBSTR(TRIM(items.barcode), 2, 1)) + (IF(SUBSTR(TRIM(items.barcode), 3, 1) * 2 >= 10, 
    (SUBSTR(TRIM(items.barcode), 3, 1) * 2) - 9,
    SUBSTR(TRIM(items.barcode), 3, 1) * 2)) + (SUBSTR(TRIM(items.barcode), 4, 1)) + (IF(SUBSTR(TRIM(items.barcode), 5, 1) * 2 >= 10, 
    (SUBSTR(TRIM(items.barcode), 5, 1) * 2) - 9,
    SUBSTR(TRIM(items.barcode), 5, 1) * 2)) + (SUBSTR(TRIM(items.barcode), 6, 1)) + (IF(SUBSTR(TRIM(items.barcode), 7, 1) * 2 >= 10, 
    (SUBSTR(TRIM(items.barcode), 7, 1) * 2) - 9,
    SUBSTR(TRIM(items.barcode), 7, 1) * 2)) + (SUBSTR(TRIM(items.barcode), 8, 1)) + (IF(SUBSTR(TRIM(items.barcode), 9, 1) * 2 >= 10, 
    (SUBSTR(TRIM(items.barcode), 9, 1) * 2) - 9,
    SUBSTR(TRIM(items.barcode), 9, 1) * 2)) + (SUBSTR(TRIM(items.barcode), 10, 1)) + (IF(SUBSTR(TRIM(items.barcode), 11, 1) * 2 >= 10, 
    (SUBSTR(TRIM(items.barcode), 11, 1) * 2) - 9,
    SUBSTR(TRIM(items.barcode), 11, 1) * 2)) + (SUBSTR(TRIM(items.barcode), 12, 1)) + (IF(SUBSTR(TRIM(items.barcode), 13, 1) * 2 >= 10, 
    (SUBSTR(TRIM(items.barcode), 13, 1) * 2) - 9,
    SUBSTR(TRIM(items.barcode), 13, 1) * 2)), 10), 10) <> SUBSTR(TRIM(items.barcode), 14, 1), 'Check digit bad', NULL) AS checksumproblem 
  FROM items) AS quer 
WHERE
  lengthproblem IS NOT NULL 
  OR libraryproblem IS NOT NULL 
  OR checksumproblem IS NOT NULL

Largest Records

  • Developer: Kyle M Hall, ByWater Solutions
  • Module: Catalog
  • Purpose: Helps identify records that are too large for Zebra to handle
  • Status: Complete
SELECT CONCAT("<a href='/cgi-bin/koha/catalogue/detail.pl?biblionumber=", biblionumber, "'>", title, "</a>" ) AS Record, 
       Length(marcxml)   AS "MARC XML Size", 
       Count(itemnumber) AS Items 
FROM   biblioitems 
       LEFT JOIN biblio USING ( biblionumber ) 
       LEFT JOIN items USING ( biblionumber ) 
GROUP  BY biblionumber 
ORDER  BY Length(marcxml) DESC, 
          Count(itemnumber) DESC 
LIMIT  20

Records with non-ascii characters in RDA copyright field

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Find records with non-ascii characters in 264$c
  • Status: Complete
SELECT
    CONCAT(
        '<a href=\"/cgi-bin/koha/catalogue/MARCdetail.pl?biblionumber=', biblionumber, '\">',
        biblionumber, '</a>' 
    ) AS biblionumber,
    ExtractValue( marcxml, '//datafield[@tag=264]/subfield[@code="c"]' ) AS '264$c'
FROM
    biblioitems
    INNER JOIN biblio using (biblionumber)
WHERE
    ExtractValue( marcxml, '//datafield[@tag=264]/subfield[@code="c"]' ) <> CONVERT( ExtractValue( marcxml, '//datafield[@tag=264]/subfield[@code="c"]' )  USING ASCII)

Records without classification number

  • 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.biblionumber, '</a>') AS biblionumber,
  Concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '>', biblio.title, '</a>') AS Title,
  ExtractValue(marcxml, '//datafield[@tag="082"]/subfield[@code="a"]') AS ClassificationNumber
FROM
  biblioitems,
  biblio
WHERE
  biblio.biblionumber = biblioitems.biblionumber AND
  (ExtractValue(marcxml, '//datafield[@tag="082"]/subfield[@code="a"]') = '' OR
    ExtractValue(marcxml, '//datafield[@tag="082"]/subfield[@code="a"]') IS NULL)

Catalogers Gone Wild

Bibs marked as RDA

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: A report to find bibs marked as RDA per Leader or 040$a
  • Status: Complete
SELECT
    biblionumber,
    title,
    SUBSTRING(ExtractValue(metadata,'//leader'),18,1) != 'i' as 'Descriptive Cataloging Form',
    ExtractValue( metadata, '//datafield[@tag=040]/subfield[@code="e"]' ) as 'Description conventions'
FROM
    biblio_metadata
WHERE
    SUBSTRING(ExtractValue(metadata,'//leader'),18,1) != 'i'
    AND  ExtractValue( metadata, '//datafield[@tag=040]/subfield[@code="e"]' ) != 'rda'

Bibs without RDA specific fields

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: A report to find bibs that have RDA fields (336-339)
  • Status: Complete - Updated 2/21/19
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber
FROM 
(SELECT biblionumber, ExtractValue(metadata,'//datafield[@tag="336"]/subfield[@code>="a"]') AS rda1, 
ExtractValue(metadata,'//datafield[@tag="337"]/subfield[@code>="a"]') AS rda2, 
ExtractValue(metadata,'//datafield[@tag="338"]/subfield[@code>="a"]') AS rda3, 
ExtractValue(metadata,'//datafield[@tag="339"]/subfield[@code>="a"]') AS rda4 
FROM biblio_metadata) AS rda 
WHERE rda1 != "" 
OR rda2 != "" 
OR rda3 != "" 
OR rda4 != ""

Count of bibs modified by cataloger

  • Developer: Ramiro Uviña
  • Module: Catalog
  • Purpose: Asks for date range and shows you them with a count of bibs they've modified. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT user,
  COUNT(user) AS 'bibs modified'
FROM action_logs
WHERE module = 'CATALOGUING'
  AND info like '%BEFORE%'
  AND action = 'MODIFY'
  AND (
    timestamp BETWEEN <<Modified between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
  )
GROUP BY user

Catalog Modifications by Date Range with Librarian Info

  • Developer: Myka Kennedy Stephens, Fosgail LLC
  • Module: Catalog
  • Purpose: Displays list of bibliographic records modified in a date range with the borrower number of the librarian who last touched the record, for monitoring and troubleshooting
  • Status: Complete
  • Version: 24.05
SELECT b.biblionumber,
  b.timestamp,
  b.title,
  CONCAT ('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', ExtractValue(metadata,'//datafield[@tag="998"]/subfield[@code="d"]'), '\">', ExtractValue(metadata,'//datafield[@tag="998"]/subfield[@code="d"]'), '</a>' ) AS 'Librarian'
FROM biblio b
LEFT JOIN biblio_metadata m ON (m.biblionumber = b.biblionumber)
WHERE DATE(b.timestamp) BETWEEN <<Modified date between|date>> AND <<and|date>>
ORDER BY b.timestamp

Find Approved User Tags for Adding to Bib Record

  • Developer: Rob Hilliker, Edsel Ford Memorial Library, and Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: Identify approved tags that haven't already been added to a local index term field (690_4$a)
  • Status: Complete
SELECT
    CONCAT('<a target="_blank" href="/cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=',biblionumber,'&frameworkcode=&op=#tab6XX">Edit Bib</a>') AS 'Bib Record', 
    GROUP_CONCAT(term) AS 'User Tag', 
    ExtractValue(marcxml,'//datafield[@tag="690" AND @ind2="4"]/subfield[@code="a"]') AS 'Indexed Tags'
FROM tags_all t
    LEFT JOIN biblioitems b USING (biblionumber)
    LEFT JOIN tags_approval ta USING (term)
WHERE 
    approved='1' 
    AND ExtractValue(marcxml,'//datafield[@tag="690" AND @ind2="4"]/subfield[@code="a"]') NOT LIKE CONCAT('%',term,'%')
GROUP BY biblionumber

Find the import batch related to a title

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: See the batches containing a given title
  • Status: Complete
SELECT 
    CONCAT('<a href="/cgi-bin/koha/tools/manage-marc-import.pl?import_batch_id=',import_batch_id,'">Link to import</a>') AS Linker,
    title,
    import_record_id,
    matched_biblionumber,
    import_batch_id,
    file_name, comments 
FROM import_biblios 
JOIN import_records USING (import_record_id) 
JOIN import_batches USING (import_batch_id) 
WHERE title LIKE CONCAT('%',<<Enter partial or full title>>,'%')

Records Cataloged with a Specific Framework

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides a list of titles cataloged with a specific framework, handy for finding items added using Fast Add.
  • Status: Completed
Select title, author 
from biblio 
where frameworkcode=<<Enter Framework Code>>

Syntax-highlighted MARC XML

  • Developer: Eric Phetteplace, California College of the Arts
  • Module: Catalog
  • Purpose: See a record's full XML with highlighting that makes it easier to read
  • Status: Complete
SELECT CONCAT(
    '<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.5.0/styles/monokai-sublime.min.css">',
    '<script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.5.0/highlight.min.js"></script>',
    '<script>hljs.initHighlightingOnLoad();</script>',
    '<pre><code class="xml">',
    REPLACE(REPLACE(marcxml, '<', '&lt;'), '>', '&gt;'), '</code></pre>') as MARCXML
FROM biblioitems
JOIN biblio USING (biblionumber)
WHERE biblionumber = <<biblionumber>>

List Item Types, Collection Codes and Locations

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Show Item Types, Collection Codes and Locations by code and description.
  • Status: Complete
SELECT
    '<b>item types</b>' as code,
    '' as description
UNION
SELECT
    itemtype as code,
    description
FROM itemtypes
UNION
SELECT
    '<b>Collection Codes</b>' as code,
    '' as description
UNION
SELECT
    authorised_value as code,
    lib as description
FROM
    authorised_values
WHERE
    category = 'CCODE'
UNION
SELECT
    '<b>Location</b>' as code,
    '' as description
UNION
SELECT
    authorised_value as code,
    lib as description
FROM
    authorised_values
WHERE
    category = 'LOC'

Find records with excessive whitespace in the call number

  • Developer: George H. Williams (Next Search Catalog / Northeast Kansas Library System)
  • Module: Catalog
  • Purpose: Find records with excessive whitespace in the call number. Shows whitespace with pipes.
  • Status: Complete
SELECT
  items.itemnumber,
  Replace(items.itemcallnumber, ' ', '|') AS CALL_NUMBER_W_BREAKS,
  items.barcode AS ITEM_BARCODE,
  home_branches.branchname AS HOME_BRANCH,
  holding_branches.branchname AS HOLDING_BRANCH,
  If(
    perm_loc.lib = loc.lib, 
    perm_loc.lib, 
    Concat(perm_loc.lib, ' (', loc.lib, ')')
  ) AS LOCATION,
  itypes.description AS ITEM_TYPE,
  ccode.lib AS CCODE,
  If(
    items.copynumber IS NULL, 
    items.itemcallnumber,
    Concat(items.itemcallnumber, ' // Copy number: ', items.copynumber)
  ) AS CALL_NUMBER,
  biblio.author,
  biblio.title,
  cnlines.length AS SPINE_LABEL_LINES
FROM
  items LEFT JOIN
  biblio ON biblio.biblionumber = items.biblionumber LEFT JOIN
  biblioitems ON biblioitems.biblioitemnumber = items.biblionumber LEFT JOIN
  (
    SELECT
      branches.branchcode,
      branches.branchname
    FROM
      branches
    ) home_branches 
    ON home_branches.branchcode = items.homebranch
    LEFT JOIN
  (
    SELECT
      branches.branchcode,
      branches.branchname
    FROM
      branches) holding_branches 
      ON holding_branches.branchcode = items.holdingbranch 
      LEFT JOIN
  (
    SELECT
      authorised_values.category,
      authorised_values.authorised_value,
      authorised_values.lib,
      authorised_values.lib_opac
    FROM
      authorised_values
    WHERE
      authorised_values.category = 'LOC'
    ) perm_loc 
    ON perm_loc.authorised_value = items.permanent_location 
    LEFT JOIN
  (
    SELECT
      authorised_values.category,
      authorised_values.authorised_value,
      authorised_values.lib,
      authorised_values.lib_opac
    FROM
      authorised_values
    WHERE
      authorised_values.category = 'LOC'
    ) loc ON loc.authorised_value = items.location 
    LEFT JOIN
  (
    SELECT
      itemtypes.itemtype,
      itemtypes.description
    FROM
      itemtypes
    ) itypes ON itypes.itemtype = items.itype 
    LEFT JOIN
  (
    SELECT
      authorised_values.category,
      authorised_values.authorised_value,
      authorised_values.lib,
      authorised_values.lib_opac
    FROM
      authorised_values
    WHERE
      authorised_values.category = 'CCODE'
    ) ccode 
    ON ccode.authorised_value = items.ccode JOIN
  (
    SELECT
      items.itemnumber,
      items.barcode,
      (Length(items.itemcallnumber) - Length(Replace(items.itemcallnumber, ' ',
      '')) + 1) AS length,
      items.homebranch
    FROM
      items
    WHERE
      items.homebranch Like <<Choose your library|branches:all>> AND
      (Length(Replace(items.itemcallnumber, ' ', '-')) - Length(Replace(items.itemcallnumber, ' ', '')) + 1) > <<Greater than X lines on the spine label>>
    ) cnlines 
    ON cnlines.itemnumber = items.itemnumber AND
      cnlines.homebranch = items.homebranch
WHERE
  items.homebranch Like <<Choose your library|branches:all>>
GROUP BY
  items.itemnumber,
  cnlines.length,
  items.itemcallnumber
ORDER BY
  HOME_BRANCH,
  LOCATION,
  ITEM_TYPE,
  CCODE,
  CALL_NUMBER,
  biblio.author,
  biblio.title,
  items.itemnumber

Bibs with more items than an entered amount

  • Developer: Jason Robb (SEKLS)
  • Module: Catalog
  • Purpose: Find records with large number of items.
  • Status: Complete
SELECT biblio.biblionumber, title, author, count(*)
FROM items, biblio
WHERE biblio.biblionumber=items.biblionumber
GROUP BY biblio.biblionumber having count(*) > <<# of items/bib>>
ORDER BY count(*) DESC
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Cataloging_-_Bibliographic_Reports&oldid=38564"