SEKnFind SQL

This page lists tips for writing SQL reports in Koha, as well as a selection of reports used in the SEKnFind Consortium.

Many were taken and adapted from the community SQL Reports Library.

Tips

Formatting notes fields

When writing a report, the notes field can include raw HTML. Coupled with in-line styles, emphasis can be placed on the notes. We use this capability to add suggestions and instructions for our libraries to use in conjunction with the reports.

For example, putting this in the notes field:

<p>Transfers being sent from your library that have not arrived at the other library.</p>
<p style="color:red;font-weight:bold;">Suggested action: Check your shelves for these items!</p>

Renders as:

Transfers being sent from your library that have not arrived at the other library.

Suggested action: Check your shelves for these items!

Parameters

Parameters allow users to make choices about what info the report retrieves.

Parameter formatting: <<Descriptive text|parameter>>

Vanilla Koha parameters:

Any authorized value category can be used as a parameter.

An authorized value of % creates a wildcard which can be used as an 'ALL' search. These parameters must be queried with LIKE <<parameter>> instead of = <<parameter>>.

Recipes

Random bits of SQL that can be used in various reports

SELECT Statement Links

View Bib Record

CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',items.biblionumber,'\" target="_blank">'"View"'</a>') AS "view"

Edit Item Record

CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=',b.biblionumber,'&itemnumber=',items.itemnumber,'\" target="_blank" >',"Edit",'</a>') AS "edit"

View Patron Account

CONCAT ('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',b.borrowernumber,'\" target="_blank">',"View Account",'</a>') AS "view"

Edit Patron Account

CONCAT('<a href=\"/cgi-bin/koha/members/memberentry.pl?op=modify&borrowernumber=',b.borrowernumber,'&step=1&guarantorid=',g.borrowernumber,'\" target="_blank">',"Edit",'</a>') AS "edit"

Edit Permissions

CONCAT('<a href=\"/cgi-bin/koha/members/member-flags.pl?member=',borrowernumber,'\" target="_blank">',"Edit",'</a>') AS "link"

Coalescence

COALESCE can be used to fill in blanks from other tables. This is good for querying deleted tables alongside their active counterparts.

Query collection codes from items & deleted items

SELECT COALESCE(items.ccode, deleteditems.ccode)

Date Range Picker

Date Range with Pop-up Calendars

WHERE date_due BETWEEN <<Between|date>> AND <<and|date>>

Indicate Clicked Links within Report

Adding styles to the notes field, and a class within the SELECT statement link can help distinguish between links that have and haven't been clicked within a report. This is useful on clean-up reports where the links open in new tabs. You can click the link, then close the tab and not lose your place within the report.

Add the style to the report notes field

<style>
.clicked:visited {
     color:hotpink;
 }
 </style>

Add a class to the link

CONCAT('<a class="clicked" target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=',b.biblionumber,'&itemnumber=',items.itemnumber,'\" >',"Edit",'</a>') AS "edit"

SUM CASE WHEN

Useful for categorizing multiple locations/ccodes/itypes together. For example, we have multiple YA shelf locations in two different AV ranges.

SELECT SUM(CASE WHEN (i.location BETWEEN '101800YAFIC' AND '102100YAFICSF')
	OR (i.location BETWEEN '121600YANFB' AND '121800YAREF') THEN 1 ELSE 0 END) as 'Young Adult Books'
FROM items i

In Transit Check

Checks whether the item is in transit and reports a 'yes' or 'no'.

SELECT IF(MAX(bt.datesent)>COALESCE(MAX(bt.datearrived),0),'Yes','No') as inTransit
FROM branchtransfers bt

Multiple Authorised Value Joins

Allows for cleaner reports by pulling descriptions rather than raw codes for different categories.

SELECT
     a.lib AS 'lost',
     a2.lib AS 'notloan',
     a3.lib AS 'withdrawn',
     a4.lib AS 'damaged',
 FROM biblio b
 LEFT JOIN items i USING (biblionumber)
 LEFT JOIN authorised_values a ON (a.authorised_value=i.itemlost AND a.category='LOST')
 LEFT JOIN authorised_values a2 ON (a2.authorised_value=i.notforloan AND a2.category='NOT_LOAN')
 LEFT JOIN authorised_values a3 ON (a3.authorised_value=i.withdrawn AND a3.category='WITHDRAWN')
 LEFT JOIN authorised_values a4 ON (a4.authorised_value=i.damaged AND a4.category='DAMAGED')

Reports

Acquisitions

Newest items added in a ccode

Finds the most recent items added in a collection code within the past year

SELECT b.title, b.author, b.biblionumber, b.datecreated, i.barcode, i.dateaccessioned
FROM biblio b
LEFT JOIN items i USING (biblionumber)
WHERE i.homebranch = <<Select Library|branches>>
	AND i.ccode = <<Collection Code|CCODE>>
	AND i.dateaccessioned > DATE_SUB(CURDATE(),INTERVAL 1 YEAR)
ORDER BY i.dateaccessioned DESC

Items added in a shelf location within a date range

Count of acquisitions in a selected yeah by shelf location

SELECT authorised_values.lib AS 'Shelving Location', COUNT(1) AS Count
FROM items
LEFT JOIN authorised_values ON (items.location=authorised_values.authorised_value)
WHERE items.homebranch = <<Library|branches>>
	AND items.dateaccessioned BETWEEN <<Start date|date>> AND <<End date|date>>
GROUP BY items.location
LIMIT 100

Items added to a shelf location within a specified year, summarized by month

Lists out items added each month for the specified year in the specified shelf location

SELECT YEAR(items.dateaccessioned) as year, MONTHNAME(items.dateaccessioned) as month,
	concat(biblio.title, ' ', ExtractValue((
	SELECT marcxml 
	FROM biblioitems
	WHERE biblio.biblionumber = biblioitems.biblionumber),'//datafield[@tag="245"]/subfield[@code="b"]')) AS FullTitle,
biblio.author, items.itemcallnumber, items.ccode, count(*) as quantity
FROM biblio, items 
WHERE biblio.biblionumber = items.biblionumber 
	AND YEAR(items.dateaccessioned) = << Enter the year to select (yyyy) >>
	AND items.homebranch = <<Select your library|branches>>  
	AND items.location = <<Shelf Location|LOC>>
GROUP BY  month, FullTitle, items.itemcallnumber
ORDER BY MONTH(items.dateaccessioned), biblio.title

Administrative

Authorized values list

Lists all authorized values and their descriptions

SELECT authorised_value, id, category, lib
FROM authorised_values
ORDER BY category

Patrons with permissions

Lists patrons who have staff permissions

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.branchcode, borrowers.categorycode,
borrowers.flags as 'permissions'
FROM borrowers
WHERE borrowers.branchcode = <<Select your library|branches>> 
	AND borrowers.flags > 0
ORDER BY borrowers.surname asc

All Patron Permissions

Lists out which permissions are on and off for each account with permissions

SELECT surname AS 'Last Name',firstname AS 'First Name',cardnumber AS 'Card Number',categorycode,userid AS 'UserID',branchcode AS 'Library',Super AS 'superlibrarian',A AS 'circulate',SubA AS 'circulate subs',B AS 'catalogue',C AS 'parameters',SubC AS 'parameters subs',D AS 'borrowers',E AS 'permissions',F AS 'reserveforothers',SubF AS 'reserveforothers subs',G AS 'borrow',I AS 'editcatalogue',SubI AS 'editcatalogue subs',J AS 'updatecharges',K AS 'acquisition',SubK AS 'acquisition subs',L AS 'management',M AS 'tools',SubM1 AS 'tools subs (Pt 1)',SubM2 AS 'tools subs (Pt 2)',N AS 'editauthorities',O AS 'serials',SubO AS 'serials subs',P AS 'reports',SubP AS 'reports subs',Q AS 'staffaccess'
FROM (SELECT b.surname,b.firstname,b.cardnumber,b.userid,b.branchcode,b.categorycode,@CHECK:=b.flags AS 'CheckQ',IF(@Check-131072>=0,@Q:='On',@Q:='Off') AS 'Q',
IF(@Check-131072>=0,@CHECK:=@Check-131072,@CHECK) AS 'CheckP',IF(@Check-65536>=0,@P:='On',@P:='Off') AS 'P',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='16' AND borrowernumber=b.borrowernumber) AS SubP,
IF(@Check-65536>=0,@CHECK:=@Check-65536,@CHECK) AS 'CheckO',IF(@Check-32768>=0,@O:='On',@O:='Off') AS 'O',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='15' AND borrowernumber=b.borrowernumber) AS SubO,
IF(@Check-32768>=0,@CHECK:=@Check-32768,@CHECK) AS 'CheckN',IF(@Check-16384>=0,@N:='On',@N:='Off') AS 'N',
IF(@Check-16384>=0,@CHECK:=@Check-16384,@CHECK) AS 'CheckM',IF(@Check-8192>=0,@M:='On',@M:='Off') AS 'M',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN 'a%' AND 'i%') AS SubM1,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN 'j%' AND 'z%') AS SubM2,
IF(@Check-8192>=0,@CHECK:=@Check-8192,@CHECK) AS 'CheckL',IF(@Check-4096>=0,@L:='On',@L:='Off') AS 'L',
IF(@Check-4096>=0,@CHECK:=@Check-4096,@CHECK) AS 'CheckK',IF(@Check-2048>=0,@K:='On',@K:='Off') AS 'K',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='11' AND borrowernumber=b.borrowernumber) AS SubK,
IF(@Check-2048>=0,@CHECK:=@Check-2048,@CHECK) AS 'CheckJ',IF(@Check-1024>=0,@J:='On',@J:='Off') AS 'J',
IF(@Check-1024>=0,@CHECK:=@Check-1024,@CHECK) AS 'CheckI',IF(@Check-512>=0,@I:='On',@I:='Off') AS 'I',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='9' AND borrowernumber=b.borrowernumber) AS SubI,
IF(@Check-512>=0,@CHECK:=@Check-512,@CHECK) AS 'CheckH',IF(@Check-256>=0,@H:='On',@H:='Off') AS 'H',
IF(@Check-256>=0,@CHECK:=@Check-256,@CHECK) AS 'CheckG',IF(@Check-128>=0,@G:='On',@G:='Off') AS 'G',
IF(@Check-128>=0,@CHECK:=@Check-128,@CHECK) AS 'CheckF',IF(@Check-64>=0,@F:='On',@F:='Off') AS 'F',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='6' AND borrowernumber=b.borrowernumber) AS SubF,
IF(@Check-64>=0,@CHECK:=@Check-64,@CHECK) AS 'CheckE',IF(@Check-32>=0,@E:='On',@E:='Off') AS 'E',
IF(@Check-32>=0,@CHECK:=@Check-32,@CHECK) AS 'CheckD',IF(@Check-16>=0,@D:='On',@D:='Off') AS 'D',
IF(@Check-16>=0,@CHECK:=@Check-16,@CHECK) AS 'CheckC',IF(@Check-8>=0,@C:='On',@C:='Off') AS 'C',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='3' AND borrowernumber=b.borrowernumber) AS SubC,
IF(@Check-8>=0,@CHECK:=@Check-8,@CHECK) AS 'CheckB',IF(@Check-4>=0,@B:='On',@B:='Off') AS 'B',
IF(@Check-4>=0,@CHECK:=@Check-4,@CHECK) AS 'CheckA',IF(@Check-2>=0,@A:='On',@A:='Off') AS 'A',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='1' AND borrowernumber=b.borrowernumber) AS SubA,	
IF(@Check-2>=0,@CHECK:=@Check-2,@CHECK) AS 'CheckSuper',IF(@Check>0,'On','Off') AS 'Super'
FROM borrowers b
LEFT JOIN categories USING (categorycode)
WHERE b.flags>0) AS MainFlags
ORDER BY surname, firstname ASC

Items added in a year

Count of items added at each library in a selected year based on acquisition date (migration libraries & retros will be inaccurate)

SELECT i.homebranch, COUNT(*)
FROM items i
WHERE YEAR(i.dateaccessioned) = <<Year>>
AND (i.ccode <> 'PER' AND i.ccode <> 'ILL' AND i.ccode <> 'ROT' OR i.ccode IS NULL)
GROUP BY i.homebranch
ORDER BY i.homebranch
LIMIT 50

Items deleted in a year

Count of items deleted at each library in a selected year, years prior to migration will be inaccurate

SELECT homebranch, COUNT(*)
FROM deleteditems
WHERE YEAR(timestamp)=<<Year (YYYY)>>
GROUP BY homebranch
ORDER BY homebranch
LIMIT 50

Catalog

Contents of an iType

Lists ALL items in an item type, may be dangerous on high use categories

SELECT  items.barcode, items.homebranch, items.location, items.itype, items.ccode, biblio.title, biblio.author 
FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
WHERE items.itype = <<Item Type|itemtypes>>

Contents of a Shelf Location

Lists ALL items in a shelf location, may be dangerous on high use categories

SELECT  items.barcode, items.homebranch, items.holdingbranch, items.location, items.ccode, items.itype,
items.itemcallnumber, biblioitems.itemtype, biblio.author, biblio.title, items.itemlost,
ExtractValue(marcxml,'//datafield[@tag="300"]/subfield[@code>="a"]') AS Description
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.location=<<Select Shelf Location|LOC>>

Item count by iType

Counts how many items are in each iType at selected library, includes total

SELECT itemtypes.description AS 'Item Type', COUNT(1) AS Count
FROM items
LEFT JOIN itemtypes ON (items.itype=itemtypes.itemtype)
WHERE items.homebranch = <<Choose Library|branches>>
GROUP BY itemtypes.description
UNION ALL
SELECT 'TOTAL' itype, COUNT(1)
FROM items
WHERE homebranch = <<Choose Library|branches>>

Item count by shelf location + item type

Counts how many items are each combination of shelf location and item type at a library

SELECT av.lib AS 'Shelving Location', it.description AS 'Item Type', COUNT(1) as Count
FROM items i
LEFT JOIN authorised_values av ON (i.location=av.authorised_value)
LEFT JOIN itemtypes it ON (i.itype=it.itemtype)
WHERE i.homebranch=<<Choose your library|branches>>
GROUP BY i.itype,i.location
ORDER BY it.description, av.lib ASC

Total number of items at each library

Total of each library's holdings

SELECT homebranch,count(*) as items 
FROM items 
GROUP BY homebranch 
ORDER BY homebranch asc 

Total number of items at each library excluding periodicals

Total of each library's holdings, minus periodicals

SELECT homebranch,count(itemnumber) as items 
FROM items 
WHERE items.ccode <> 'PER'
GROUP BY homebranch 
ORDER BY homebranch asc

Total bib count

Number of bibs in the entire collection

SELECT COUNT(1) as Records
FROM biblio

Total item count

Number of items in the entire collection

SELECT COUNT(1) as Items 
FROM items

Lost report (hyperlinked)

Modified Items Lost report, includes call# & # of checkouts with links

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',title,'</a>') AS Title,
biblio.author, authorised_values.lib as 'Lost Code',
CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?biblionumber=',biblio.biblionumber,'\">',barcode,'</a>') AS Barcode,
items.itemcallnumber,  items.datelastseen, items.price, items.homebranch as 'Library',  items.holdingbranch,
items.itemnotes, items.issues as 'Checkouts'
FROM items 
LEFT JOIN biblio on (items.biblionumber = biblio.biblionumber)   
LEFT JOIN authorised_values ON (items.itemlost=authorised_values.authorised_value) 
WHERE  items.homebranch = <<Select your library|branches>>
	AND items.itemlost != 0
	AND authorised_values.category='LOST'
ORDER BY biblio.title asc

Lost report (no links)

Modified Items Lost report, includes call# & # of checkouts

SELECT biblio.title, biblio.author, authorised_values.lib as 'Lost Code', items.barcode,  items.itemcallnumber,
items.datelastseen, items.price, items.homebranch as 'Library',  items.holdingbranch, items.itemnotes,
items.issues as 'Checkouts'
FROM items 
LEFT JOIN biblio on (items.biblionumber = biblio.biblionumber)   
LEFT JOIN authorised_values ON (items.itemlost=authorised_values.authorised_value) 
WHERE  items.homebranch = <<Select your library|branches>>
AND items.itemlost != 0
AND authorised_values.category='LOST'
ORDER BY biblio.title asc

Circulation

Average circs each hour

Averages how many circulations occured each hour in a date range at a specific library. Can be used to locate your busiest times of day in terms of checkouts.

SELECT date_format(`datetime`,'%H') as 'Hour', count(*) as 'Checkouts and Renews' 
FROM statistics
WHERE datetime BETWEEN <<Starting date|date>> AND <<Ending date|date>>
	AND branch=<<Choose your library|branches>>
	AND type in ('issue','renew')
GROUP BY date_format(`datetime`,'%H')

Titles with holds

Gives a complete list of bibs with holds including the title, number of holds, and number of items associated with each bib.

SELECT biblio.biblionumber, biblio.title, biblio.author, authorised_values.lib as collection,
count(DISTINCT reserves.borrowernumber) as reservecount, count(DISTINCT items.itemnumber) as itemcount
FROM reserves
LEFT JOIN items ON items.biblionumber=reserves.biblionumber 
LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber
LEFT JOIN authorised_values ON items.ccode=authorised_values.authorised_value
GROUP BY reserves.biblionumber
ORDER BY reservecount DESC

Top 10 Circulating Items by Shelf Location

Reports the 10 most circulated items in a shelf location in a date range.

SELECT count(o.issuedate) AS circs,
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',title,'</a>') AS Title, b.author
FROM old_issues o
LEFT JOIN items i ON (i.itemnumber=o.itemnumber) 
LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber) 
WHERE DATE(o.issuedate) > <<Range start date|date>> 
	AND DATE(o.issuedate) <= <<Range end date|date>> 
	AND i.homebranch = <<Library|branches>>
	AND i.location = <<Shelf location|LOC>>
	AND o.itemnumber IS NOT NULL
GROUP BY b.biblionumber
ORDER BY circs DESC 
LIMIT 10

Collection Analysis

Age stats from call# range

Shows the average age of the books in a call range based on publication dates. Useful for weeding and collection development.

SELECT AVG(copyrightdate)
FROM biblio
LEFT JOIN items ON biblio.biblionumber=items.biblionumber 
WHERE items.itemcallnumber BETWEEN <<starting call number>> AND <<ending call number>>
	AND items.homebranch LIKE <<Select Library|branches>>
	AND biblio.copyrightdate IS NOT NULL

Items with subject of 'Christian Fiction'

Finds all items associated with records that have a 650a of Christian Fiction. Useful for breaking a collection into a new shelf location.

SELECT items.barcode, items.itemcallnumber, items.location, biblio.title, biblio.author,
ExtractValue( biblioitems.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]' ) AS 'lcsh'
FROM biblioitems
JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
JOIN items ON ( biblioitems.biblionumber = items.biblionumber )
WHERE items.homebranch = <<Library|branches>>
	AND ExtractValue( biblioitems.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]' ) LIKE '%Christian fiction%'

Items with subject of 'Graphic Novel'

Finds all items associated with records that have a 650a of Graphic Novel. Useful for breaking a collection into a new shelf location.

SELECT items.itemcallnumber, biblio.biblionumber, biblio.title, biblio.author,	
ExtractValue( biblioitems.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]' ) AS 'lcsh'
FROM biblioitems
JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
JOIN items ON ( biblioitems.biblionumber = items.biblionumber )
WHERE items.homebranch = <<Library|branches>>
	AND ExtractValue( biblioitems.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]' ) LIKE '%Graphic novel%'

Link tracker

Counts how many times each 856u is clicked

SELECT  MAX(timeclicked) AS 'Most Recent Click', url, COUNT(*) as Clicks
FROM linktracker
GROUP BY url
ORDER BY MAX(timeclicked)

Database Clean-Up

Bad Cataloging

Runs comparisons on various authorized values to locate miscataloged items

SELECT i.barcode, b.title, i.ccode, a.lib, i.homebranch, i.dateaccessioned, i.booksellerid AS Whodunnit
FROM items i
LEFT JOIN biblio b ON i.biblionumber=b.biblionumber
LEFT JOIN authorised_values a ON i.location=a.authorised_value
WHERE (i.location NOT LIKE '%STORED%'
	AND i.location NOT LIKE '%ILL%'
	AND i.location NOT LIKE '%NEW%'
	AND i.location NOT LIKE '%BKDISCUSS%'
	AND i.location NOT LIKE '%OFFICE%'
	AND i.location NOT LIKE '%KSHC%'
	AND i.location NOT LIKE '%COMPUSE%'
	AND i.location NOT LIKE '%DISPLAY%'
	AND i.location NOT LIKE '%ERYBODY%'
	AND i.location NOT LIKE '%LEASED%'
	AND i.location NOT LIKE '%SRAREA%'
	AND i.location NOT LIKE '%GAMEPUZ%')
AND ((i.ccode LIKE 'AB' AND i.location NOT BETWEEN '100100ADFICCF' AND '121800YAREF') 
	OR (i.ccode LIKE 'AUD' AND i.location NOT LIKE '%AUD%')
	OR (i.ccode LIKE 'BLU' AND i.location NOT LIKE '%BLU%')
	OR (i.ccode LIKE 'DEF')
	OR (i.ccode LIKE 'DVD' AND i.location NOT LIKE '%DVD%')
	OR (i.ccode LIKE 'ER' AND i.location NOT LIKE '%ER%')
	OR (i.ccode LIKE 'GAM' AND i.location NOT LIKE '%SOF%')
	OR (i.ccode LIKE 'ILL' AND i.location NOT LIKE '%ILL%')
	OR (i.ccode LIKE 'JB' AND i.location NOT BETWEEN '140100JUVFICBR' AND '160400JUVNFILL')
	OR (i.ccode LIKE 'MIC' AND i.location NOT LIKE '%MICRO%')
	OR (i.ccode LIKE 'MUS' AND i.location NOT LIKE '%AUDM%')
	OR (i.ccode LIKE 'PER' AND i.location NOT LIKE '%MAG%')
	OR (i.ccode LIKE 'SOF' AND i.location NOT LIKE '%SOF%')
	OR (i.ccode LIKE 'STT')
	OR (i.ccode LIKE 'VHS' AND i.location NOT LIKE '%VHS%')
	OR (i.ccode LIKE 'XXX'))
ORDER BY a.lib

Bibs added in a time frame

Pulls all bibs created within a selected window of time

SELECT b.biblionumber, b.title, b.author,
ExtractValue(marcxml,'//datafield[@tag="300"]/subfield[@code>="a"]') AS Description,  b.datecreated, b.timestamp
FROM biblioitems bi
LEFT JOIN biblio b on (bi.biblionumber=b.biblionumber) 
WHERE b.datecreated BETWEEN <<Between|date>> AND <<and|date>> 
ORDER BY b.datecreated ASC

Bibs with duplicate ISBNs

Finds cases where multiple records have the same ISBN and links to a search for them. Used to find records to merge.

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/search.pl?q=',isbn,'\" target="_blank">',isbn,'</a>') AS 'ISBN Search',
COUNT(*) AS 'No. of Matching Records'
FROM biblioitems
GROUP BY isbn
HAVING COUNT(*) > 1

Bibs with many items

Finds bibs with more than X amount of items. Useful for finding records that are bogged down, esp. periodicals.

SELECT biblio.biblionumber, title, author, count(*)
FROM items, biblio
WHERE biblio.biblionumber=items.biblionumber
GROUP BY biblio.biblionumber having count(*) > <<Find bibs with more than XXX items>>
ORDER BY count(*) DESC

Deleted items for specific bib

Useful for re-adding items that accidentally get deleted

SELECT *
FROM deleteditems
LEFT JOIN biblio USING (biblionumber)
WHERE biblionumber = <<Enter Bib Number>>

Empty bibs

Bibs without items attached

SELECT biblionumber, title, datecreated 
FROM biblio 
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)

Items without replacement prices filtered on shelf location

Pulls any legacy data from the 541 field to speed up cleanup

SELECT i.barcode, i.itemcallnumber, b.author, b.title, i.replacementprice,
ExtractValue(bi.marcxml, "//datafield[@tag='541']/subfield[@code='a']" ) AS 'Source',
ExtractValue(bi.marcxml, "//datafield[@tag='541']/subfield[@code='d']" ) AS 'Date',
ExtractValue(bi.marcxml, "//datafield[@tag='541']/subfield[@code='f']" ) AS 'Owner',
ExtractValue(bi.marcxml, "//datafield[@tag='541']/subfield[@code='h']" ) AS 'Purchase Price'
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
LEFT JOIN biblioitems bi ON (i.biblionumber=bi.biblionumber)
WHERE i.homebranch = <<Library|branches>>
	AND i.location = <<Shelf Location|LOC>>
	AND i.ccode <> 'ILL'
	AND i.ccode <> 'ROT'
	AND b.title NOT LIKE '%Floating playaway%'
	AND i.replacementprice IS NULL
ORDER BY i.itemcallnumber

Multi-null report - hyperlinked

Checks for null shelf locations, itypes, ccodes and barcodes

SELECT CONCAT('<a target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=',biblio.biblionumber,'&itemnumber=',items.itemnumber,'\">',barcode,'</a>') AS Barcode,
items.homebranch, items.holdingbranch, items.itemcallnumber, items.location, items.ccode, items.itype,
CONCAT('<a target="_blank" href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',title,'</a>') AS Title, biblio.author 
FROM items  
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
WHERE items.location IS NULL OR items.ccode IS NULL OR items.itype IS NULL OR items.barcode IS NULL
	AND items.homebranch <> 'STATELIB'
	AND items.itype <> 'EBOOK'
ORDER BY items.homebranch

Inventory

Items not scanned in a shelf location

Generates a list of everything that wasn't seen when scanning based on the date inventoried. Use to search for missing items.

SELECT i.itemcallnumber, b.title, b.author, i.barcode, datelastseen, a.lib, i.onloan
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)
LEFT JOIN authorised_values a ON (a.authorised_value=i.itemlost)
WHERE datelastseen < <<Date you started inventory/Date last seen before|date>> 
	AND i.homebranch=<<Home branch|branches>>
	AND i.location=<<Location|LOC>>
	AND i.onloan IS NULL
	AND a.category='LOST'
GROUP BY i.itemnumber
ORDER BY onloan DESC, i.itemcallnumber ASC

Items not scanned

Generates a complete list of items not scanned during inventory, best run at the very end using the inital start date.

SELECT i.itemcallnumber, b.title, i.barcode, datelastseen, a.lib
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)
LEFT JOIN authorised_values a ON (a.authorised_value=i.itemlost)
WHERE datelastseen < <<Date you started inventory/Date last seen before|date>> 
	AND i.homebranch=<<Home branch|branches>>
	AND i.onloan IS NULL
	AND a.category='LOST'
GROUP BY i.itemnumber
ORDER BY onloan DESC, i.itemcallnumber ASC

Monthly

Long overdues AT other libraries

Overdue items that are owned by your library, but checked out elsewhere. Enter your library twice.

SELECT items.barcode, biblio.title, items.itemcallnumber, items.homebranch AS "Owned by", issues.date_due,
items.holdingbranch AS "Checked out at" 
FROM items 
LEFT JOIN  biblio on(items.biblionumber=biblio.biblionumber) 
LEFT JOIN  issues on(items.itemnumber=issues.itemnumber) 
LEFT JOIN  borrowers on(issues.borrowernumber=borrowers.borrowernumber) 
WHERE  items.homebranch = <<Choose Library|branches>>
	AND  items.holdingbranch != <<Choose Library|branches>>
	AND  issues.date_due != ' '
	AND  issues.date_due is not NULL 
	AND  issues.date_due < DATE_SUB(curdate(), INTERVAL '99' DAY)  
ORDER BY  items.holdingbranch

Long overdues FROM other libraries

Overdue items that are owned by someone else, but checked out by your patron. Enter your library twice.

SELECT borrowers.cardnumber,  borrowers.firstname,  borrowers.surname,  borrowers.phone,  borrowers.email, items.barcode,
biblio.title, items.itemcallnumber, items.homebranch AS "Owned by", issues.date_due, items.holdingbranch AS "Checked out at" 
FROM items 
LEFT JOIN  biblio on(items.biblionumber=biblio.biblionumber) 
LEFT JOIN  issues on(items.itemnumber=issues.itemnumber) 
LEFT JOIN  borrowers on(issues.borrowernumber=borrowers.borrowernumber) 
WHERE  items.homebranch != <<Choose Library|branches>>
	AND  items.holdingbranch = <<Choose Library|branches>>
	AND  items.homebranch <> 'ROTATION'
	AND  issues.date_due !=  
	AND  issues.date_due is not NULL 
	AND  issues.date_due < DATE_SUB(curdate(), INTERVAL '99' DAY)  
ORDER BY  items.homebranch

Lost items AT other libraries

Items that were marked lost with a holding location different than the home location.

SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, items.barcode, authorised_values.lib,
items.homebranch, items.holdingbranch
FROM items 
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) 
LEFT JOIN authorised_values ON (items.itemlost=authorised_values.authorised_value) 
WHERE items.itemlost <> '0' AND items.itemlost <> '2' AND authorised_values.category='LOST'
	AND items.homebranch <> items.holdingbranch
	AND items.homebranch = <<Choose Library|branches>>
ORDER BY items.holdingbranch

Missing call number

Items at a specified library without call numbers

SELECT CONCAT('<a target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=',biblio.biblionumber,'&itemnumber=',items.itemnumber,'\">',barcode,'</a>') AS Barcode,
items.itemcallnumber, CONCAT('<a target="_blank" href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',title,'</a>') AS Title, biblio.author 
FROM items  
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
WHERE items.itemcallnumber IS NULL
	AND items.homebranch = <<Choose Library|branches>>
ORDER BY items.itemcallnumber

Missing collection code

Items at a specified library missing a collection code

SELECT CONCAT('<a target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=',biblio.biblionumber,'&itemnumber=',items.itemnumber,'\">',barcode,'</a>') AS Barcode,
items.itemcallnumber, items.ccode,
CONCAT('<a target="_blank" href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',title,'</a>') AS Title, biblio.author 
FROM items  
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
WHERE items.ccode IS NULL
	AND items.homebranch = <<Choose Library|branches>>
ORDER BY items.itemcallnumber

Missing item type

Items at a specified library missing an item type

SELECT CONCAT('<a target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=',biblio.biblionumber,'&itemnumber=',items.itemnumber,'\">',barcode,'</a>') AS Barcode,
items.itemcallnumber, items.itype,
CONCAT('<a target="_blank" href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',title,'</a>') AS Title,
biblio.author 
FROM items  
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
WHERE items.itype IS NULL
	AND items.homebranch = <<Choose Library|branches>>
ORDER BY items.itemcallnumber

Missing shelf location

Items at a specified library missing a shelf location

SELECT CONCAT('<a target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=',biblio.biblionumber,'&itemnumber=',items.itemnumber,'\">',barcode,'</a>') AS Barcode,
items.itemcallnumber, items.location, CONCAT('<a target="_blank" href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',title,'</a>') AS Title,
biblio.author 
FROM items  
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
WHERE items.location IS NULL
	AND items.homebranch = <<Choose Library|branches>>
ORDER BY items.itemcallnumber

Old holds

Holds placed XX months ago or more

SELECT CONCAT('<a target="_blank" href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',p.borrowernumber,'\">',p.borrowernumber,'</a>') AS 'Link to Patron',
p.cardnumber, p.firstname, p.surname, r.reservedate, b.title
FROM reserves r
LEFT JOIN borrowers p USING (borrowernumber)
LEFT JOIN biblio b USING (biblionumber)
WHERE r.reservedate < ADDDATE(NOW(), INTERVAL -<<Holds over (XX) months old>> MONTH)
	AND r.branchcode = <<Select your library|branches>>     
ORDER BY r.reservedate

Transfers FROM your library

Transfers being sent from your library that have not arrived yet

SELECT b.datesent, b.frombranch, b.tobranch, items.homebranch, items.barcode, biblio.title, biblio.author,
items.itemcallnumber, DATEDIFF(b.datesent, (DATE_SUB(curdate(), INTERVAL 5 day)) ) * -1 as dayslate
FROM branchtransfers as b
LEFT JOIN items on (b.itemnumber = items.itemnumber)
LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber)
LEFT JOIN biblio on (biblio.biblionumber = biblioitems.biblionumber)
WHERE b.frombranch = <<Select your library|branches>>
	AND b.datearrived is NULL
	AND items.homebranch <> 'ROTATION'
	AND DATEDIFF(b.datesent, (DATE_SUB(curdate(), INTERVAL 5 day)) ) * -1 > '0'
ORDER BY dayslate DESC

Transfers TO your library

Transfers being sent to your library that have not arrived yet

SELECT  b.datesent, b.frombranch, b.tobranch, items.homebranch, items.barcode, biblio.title, biblio.author,
items.itemcallnumber, DATEDIFF(b.datesent, (DATE_SUB(curdate(), INTERVAL 5 day)) ) * -1 as dayslate
FROM branchtransfers as b
LEFT JOIN items on (b.itemnumber = items.itemnumber)
LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber)
LEFT JOIN biblio on (biblio.biblionumber = biblioitems.biblionumber)
WHERE b.tobranch = <<Select your library|branches>>
	AND b.datearrived is NULL
	AND items.homebranch <> 'ROTATION'
	AND DATEDIFF(b.datesent, (DATE_SUB(curdate(), INTERVAL 5 day)) ) * -1 > '0'
ORDER BY dayslate DESC

Patrons

Patrons without cardnumber

Finds patrons with blank cardnumbers, useful for self-registration cleanup.

SELECT surname as 'Surname', firstname as 'First Name', dateexpiry as 'Expiry Date', branchcode as 'Library'
FROM borrowers 
WHERE (cardnumber = ' ' OR cardnumber IS NULL)
ORDER BY branchcode, surname asc
LIMIT 100

Patrons added in date range

Counts patrons added in date range at selected library.

SELECT categorycode, COUNT(branchcode) as 'New Patrons Added' 
FROM borrowers 
WHERE borrowers.dateenrolled BETWEEN <<Patrons added between|date>> AND <<and|date>>
	AND borrowers.branchcode=<<Your branch|branches>>
GROUP BY branchcode, categorycode

Year-End

Yearly circulation stats by collection code

Gives total checkouts + renewals in a year, broken down by ccode, with a grand total at the end.

SELECT a.lib AS 'Collection Code', COUNT(1) AS 'Circs'
FROM statistics s
LEFT JOIN items i ON s.itemnumber=i.itemnumber
LEFT JOIN authorised_values a ON i.ccode=a.authorised_value
WHERE YEAR(s.datetime) = <<Enter year (YYYY)>>
	AND s.branch = <<Choose Library|branches>>
	AND s.type IN ('issue', 'renew')
GROUP BY a.lib
UNION ALL
SELECT 'TOTAL' ccode, COUNT(1) AS 'Circs'
FROM statistics
WHERE YEAR(datetime) = <<Enter Year(YYYY)>>
	AND branch = <<Choose Library|branches>>
	AND type IN ('issue', 'renew')

Patron count by patron category

Totals number of patrons in each category at a selected library with a grand total at the end.

SELECT c.description AS 'Patron Category', COUNT(*)
FROM borrowers b
LEFT JOIN categories c ON (b.categorycode = c.categorycode)
WHERE b.branchcode = <<Choose Library|branches>&gr;
GROUP BY b.categorycode
UNION ALL
SELECT 'TOTAL' categories, COUNT(*)
FROM borrowers
WHERE branchcode = <<Choose Library|branches>>

Item count by collection code

Gives total number of items in each collection code with a grand total at the end.

SELECT authorised_values.lib AS 'Collection Code', COUNT(1) AS Count
FROM items
LEFT JOIN authorised_values ON (items.ccode=authorised_values.authorised_value)
WHERE items.homebranch = <<Choose Library|branches>>
GROUP BY items.ccode
UNION ALL
SELECT 'TOTAL' ccode, COUNT(1)
FROM items
WHERE homebranch = <<Choose Library|branches>>

Item count by shelf location

Gives total number of items in each shelf location with a grand total at the end.

SELECT authorised_values.lib AS 'Shelving Location', COUNT(1) AS Count
FROM items
LEFT JOIN authorised_values ON (items.location=authorised_values.authorised_value)
WHERE items.homebranch = <<Choose Library|branches>>
GROUP BY items.location
UNION ALL
SELECT 'TOTAL' location, COUNT(1)
FROM items
WHERE homebranch = <<Choose Library|branches>>
LIMIT 100

Loans to libraries outside the consortium

Counts checkouts to patrons with the ILL category

SELECT COUNT(*) AS 'Loans'
FROM statistics s
JOIN borrowers b USING (borrowernumber) 
WHERE b.categorycode = 'ILL'
	AND s.type IN ('issue','renew') 
	AND s.branch = <<Choose Your Library|branches>>
	AND YEAR(s.datetime) = <<Enter Year (YYYY)>>	

Borrows from libraries outside the consortium

Counts checkouts of ILL cards used to circulate non-consortium items to consortium patrons

SELECT COUNT(*) AS 'Borrows'
FROM statistics s
JOIN items i USING (itemnumber) 
WHERE i.ccode = 'ILL'
	AND s.type IN ('issue','renew') 
	AND s.branch = <<Choose Your Library|branches>>
	AND YEAR(s.datetime) = <<Enter Year (YYYY)>>	

Circulation of Juv/YA materials in given year

Sums circs for each shelf location with a grand total at the end.

SELECT COALESCE(a.lib, 'TOTAL') AS 'Shelf Location',  COUNT(*) AS 'Circs' 
FROM statistics s
LEFT JOIN items i ON (s.itemnumber=i.itemnumber)
LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
WHERE 1=1  
	AND YEAR(s.datetime) = <<Year (YYYY)>>
	AND s.branch LIKE <<Home branch|branches>>
	AND s.type IN ('issue', 'renew')
	AND (i.location LIKE '%YA%'  OR  i.location LIKE '%JUV%')
GROUP BY a.lib
WITH ROLLUP
LIMIT 50