SQL Reports Holds

This is a page that will contain the SQL Reports relating to Holds.
SQL Reports Main Library | SQL Reports Patrons | SQL Reports Circulation
== Holds ==
===Holds Counts by Date===
==== Holds placed in date range ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds
* Purpose: This will ask you to enter the date range twice, use the same range both times. It will show you with a count of holds placed in that time period 
* Status: Complete
SELECT sum(count) AS holds
FROM (
SELECT count(*) AS count
FROM reserves
WHERE date(reservedate) BETWEEN <<Hold Placed Between (yyyy-mm-dd)|date>> and <<and
(yyyy-mm-dd)|date>>
UNION ALL
SELECT count(*) AS count
FROM old_reserves
WHERE date(reservedate) BETWEEN <<Repeat Hold Placed Between (yyyy-mm-dd)|date>> and
<<and (yyyy-mm-dd)|date>>
) AS myholds
==== Holds filled in date range ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds
* Purpose: It will show you with a count of holds filled in that time period 
* Status: Complete
SELECT count(*) as 'holds filled'
FROM old_reserves
WHERE date(waitingdate) BETWEEN <<Between (yyyy-mm-dd)|date>> 
 and <<and (yyyy-mm-dd)|date>> and found='F'
==== Monthly in holds placed by branch in current year ====
* Developer: Nick Clemens
* Module: Holds
* Purpose: Monthly holds placed by branch (counts holds placed in that month that have not been filled) 
* Status: Complete
SELECT count(*),branchcode
FROM reserves
WHERE
 month(reservedate) = <<Enter month MM>> AND year(reservedate)=year(now())
GROUP BY branchcode
==== Monthly holds both placed and filled, by branch ====
* Developer: Nick Clemens
* Module: Holds
* Purpose: Monthly holds both placed and filled by branch (counts holds both placed and filled in that month) 
* Status: Complete
SELECT count(*), branchcode, DATE_FORMAT( timestamp, "%m" )
FROM old_reserves
WHERE
 DATE_FORMAT( timestamp, "%m" )=<<Enter Month MM>> AND DATE_FORMAT( timestamp, "%Y" )=<<Enter Year YYYY>>
AND YEAR(reservedate)=DATE_FORMAT( timestamp, "%Y" ) AND MONTH(reservedate)=DATE_FORMAT( timestamp, "%m" )
AND Found = 'F'
GROUP BY branchcode
==== Monthly holds filled by branch ====
* Developer: Nick Clemens
* Module: Holds
* Purpose: Monthly holds filled by branch (counts all holds filled in that month regardless of when placed)
* Status: Complete
SELECT count(*),branchcode
FROM old_reserves 
WHERE
DATE_FORMAT( timestamp, "%m" )=<<Enter Month MM>> AND DATE_FORMAT( timestamp, "%Y" )=<<Enter Year YYYY>>
AND
 Found = 'F'
GROUP BY branchcode
==== Holds by Year, Month and Status ====
* Developer: Barton Chittenden, ByWater Solutions
* Module: Holds
* Purpose: Statistical overview of holds, broken out by YEAR, MONTH and Status
* Status: Completed
SELECT
 YEAR(timestamp),
 MONTH(timestamp),
 status,
 count(*) FROM (
 SELECT
 CASE
 WHEN found is NULL THEN 'PLACED'
 WHEN found = 'W' THEN 'WAITING'
 WHEN found = 'S' THEN 'SUSPENDED'
 WHEN found = 'T' THEN 'IN TRANSIT'
 END as status,
 timestamp
 FROM reserves
 UNION
 SELECT
 CASE
 WHEN found is NULL THEN 'CANCLED'
 WHEN found = 'F' THEN 'FILLED'
 END as status,
 timestamp
 FROM old_reserves
 ) as holds
GROUP BY
 YEAR(timestamp), MONTH(timestamp), status
ORDER BY
 YEAR(timestamp), MONTH(timestamp), status
=== Patrons and Holds ===
==== Patrons Holds History List ====
* Developer: B.Turnbull LiveWire CIC
* Module: Holds
* Purpose: List all holds for a cardnumber and links to the bib record. 
* Status: Complete
SELECT
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.title,'</a>') as "Title", 
b.author as "Author",
CONCAT ( p.surname ," ", p.firstname) as "Borrower",
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',p.borrowernumber,'\">',p.cardnumber,'</a>') AS 'Card Number',
DATE_FORMAT(r.cancellationdate,'%b %d %Y %h:%i %p') AS "Date Cancelled",
DATE_FORMAT(r.reservedate ,'%b %d %Y %h:%i %p') AS "Date reserved",
IF(r.itemnumber IS NULL, 'Next Available',barcode) AS 'Specific Item',
 r.waitingdate as "Awaiting collection since",
 r.branchcode as "Pickup Library",
 r.reservenotes as "Reservation Notes",
 r.priority as "Priority",
 CASE r.found 
 WHEN 'F' THEN "Completed"
 WHEN 'W' THEN "Waiting"
 WHEN 'T' THEN "In Transit"
 ELSE (IF ( r.cancellationdate > 0,"Cancelled","Unavailable" ))
 END AS "Status",
 r.expirationdate as "Patron Expire request",
 IF (r.suspend > 0,"Y", " ") as "Suspended",
 r.suspend_until as "Suspended Until"
 FROM reserves r 
 LEFT JOIN biblio b USING (biblionumber)
 LEFT JOIN borrowers p USING (borrowernumber)
LEFT JOIN biblioitems USING (biblionumber)
LEFT JOIN items USING (itemnumber)
 WHERE p.cardnumber = @cardNumber :=<<Card Number>> COLLATE utf8_unicode_ci
UNION
 SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.title,'</a>') as Title,
 b.author, 
CONCAT ( p.surname ," ", p.firstname) as "Borrower",
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',p.borrowernumber,'\">',p.cardnumber,'</a>') AS 'Card Number',
 s.cancellationdate as "Date Cancelled",
 s.reservedate as "Date reserved",
IF(s.itemnumber IS NULL, 'Next Available',barcode) AS 'Specific Item',
 s.waitingdate as "Awaiting collection since",
 s.branchcode as "Pickup Library",
 s.reservenotes as "Reservation Notes",
 s.priority as "Priority", 
CASE s.found 
 WHEN 'F' THEN "Completed"
 WHEN 'W' THEN "Waiting"
 WHEN 'T' THEN "In Transit"
 ELSE (IF ( s.cancellationdate > 0,"Cancelled","Unavailable" ))
 END AS "Status",
s.expirationdate as "Patron Expire request", 
 IF (s.suspend > 0,"Y", " ") as "Suspended",
 s.suspend_until as "Suspended Until"
 FROM old_reserves s 
 LEFT JOIN biblio b USING (biblionumber) 
 LEFT JOIN borrowers p USING (borrowernumber) 
LEFT JOIN biblioitems USING (biblionumber)
LEFT JOIN items USING (itemnumber)
 WHERE p.cardnumber = @cardNumber
==== Patrons with Holds Waiting at Library ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds
* Purpose: List of all patrons at branch with holds awaiting pickup. 
* Status: Complete
SELECT p.surname, p.firstname, p.email, p.cardnumber, 
 h.waitingdate AS 'hold date', i.barcode, b.title
FROM reserves h 
left join borrowers p using (borrowernumber) 
left join items i using (itemnumber) 
left join biblio b on (i.biblionumber=b.biblionumber) 
WHERE h.waitingdate IS NOT NULL and h.branchcode=<<Waiting at|branches>>
==== List of all Patrons from a Single Branch with open Hold Requests ====
* Developer: Nick Clemens
* Module: Holds
* Purpose: Unfilled holds for a specific branch 
* Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, reserves.reservedate 
AS 'date reserved', reserves.priority, biblio.title, biblioitems.isbn, IF(itemnumber IS NULL, 'Next Available',barcode) AS 'Specific Item'
FROM reserves
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN biblio USING (biblionumber)
LEFT JOIN biblioitems USING (biblionumber)
LEFT JOIN items USING (itemnumber)
WHERE reserves.branchcode = <<Branch|branches>>
==== List of titles with holds and on loan in a specific cardnumber ====
* Developer: François Charbonnier, InLibro
* Module: Holds
* Purpose: This report will give you a list of holds titles on loan in a specific user card (you will enter the cardnumber you're interested in). 
* Status: Complete
SELECT
 biblio.title,
 biblio.author,
 issues.date_due,
 CONCAT(bi.firstname, ' ', bi.surname, ' (',bi.cardnumber, ')') AS 'Prêté à',
 reserves.reservedate,
 CONCAT(br.firstname, ' ', br.surname, ' (',br.cardnumber, ')') AS 'Réservé pour'
FROM issues
 LEFT JOIN items USING(itemnumber)
 LEFT JOIN reserves USING(biblionumber)
 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
 LEFT JOIN borrowers AS bi ON issues.borrowernumber=bi.borrowernumber
 LEFT JOIN borrowers AS br ON reserves.borrowernumber=br.borrowernumber
WHERE bi.cardnumber=<<Numéro de carte>> AND reserves.biblionumber IS NOT NULL
=== Holds and Library Branches ===
==== Count of holds filled by another branch ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds
* Purpose: Holds filled by a branch other than the items homebranch for a year broken down by month 
* Status: Complete
select i.homebranch, h.branchcode, monthname(h.reservedate) as month, 
 year(h.reservedate) as year, count(*) 
from old_reserves h
left join items i using (itemnumber)
where h.branchcode != i.homebranch and year(h.reservedate) = <<Year>> 
group by month(h.reservedate), h.branchcode
==== List of all items currently on loan at another library ====
* Developer: Nick Clemens
* Module: Holds
* Purpose: List of all items currently on loan at another library (includes title and call #) 
* Status: Complete
SELECT
 biblio.title,
 items.itemcallnumber, 
 items.holdingbranch,
 items.homebranch, 
 items.barcode, 
 issues.issuedate 
FROM issues 
LEFT JOIN items ON issues.itemnumber=items.itemnumber 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE
 items.homebranch = <<Owning branch|branches>> AND issues.branchcode<>items.homebranch
ORDER BY
 items.homebranch, issues.issuedate, biblio.title
==== List of all items currently borrowed from another library ====
* Developer: Nora Blake and Bev Church
* Module: Holds
* Purpose: List of all items currently borrowed from another library (includes title and call #) 
* Status: Complete
SELECT
 biblio.title,
 items.itemcallnumber, 
 items.holdingbranch,
 items.homebranch, 
 items.barcode, 
 issues.issuedate 
FROM issues 
LEFT JOIN items ON issues.itemnumber=items.itemnumber 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE 
 issues.branchcode= <<Issuing branch|branches>>
 AND 
 items.holdingbranch != items.homebranch 
ORDER BY
 items.homebranch, issues.issuedate, biblio.title
==== List of total Holds placed at a specific branch during a specified period ====
* Developer: Liz Rea, Catalyst IT (For South Taranaki District Council Libraries)
* Module: Circulation
* Purpose: Shows the borrowers name, card number, title, and barcode of the item and the date the hold was placed. The person running the report is able to choose the branch and the date period when running the report
* Example: 
* Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, biblio.title, reserves.reservedate FROM reserves, borrowers, biblio 
WHERE reserves.borrowernumber = borrowers.borrowernumber
AND reserves.biblionumber = biblio.biblionumber 
AND reserves.branchcode = <<Select a library|branches>> 
AND date(reserves.reservedate) 
BETWEEN <<Hold Placed BETWEEN (yyyy-mm-dd)|date>> 
AND <<and (yyyy-mm-dd)|date>> 
UNION ALL SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, biblio.title, old_reserves.reservedate FROM old_reserves, borrowers, biblio 
WHERE old_reserves.borrowernumber = borrowers.borrowernumber
AND old_reserves.biblionumber = biblio.biblionumber 
AND old_reserves.branchcode = <<Select a library|branches>> 
AND date(old_reserves.reservedate) 
BETWEEN <<Hold Placed BETWEEN (yyyy-mm-dd)|date>> 
AND <<and (yyyy-mm-dd)|date>> 
=== Holds to Pull ===
==== Holds to Pull ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds/Circulation
* Purpose: List items that are on hold, not checked out and not waiting on the holds shelf.
* Status: Complete
select b.title, i.itemcallnumber, date(r.timestamp) as "hold date" 
from reserves r 
left join biblio b on (r.biblionumber=b.biblionumber) 
left join items i on (i.biblionumber=b.biblionumber) 
where i.itemnumber not in (select issues.itemnumber from issues) 
 and r.waitingdate is NULL 
order by r.timestamp asc
==== Holds to Pull at Branch ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds/Circulation
* Purpose: List items that are on hold, not checked out and not waiting on the holds shelf at a specific branch.
* Status: Complete
SELECT b.title, b.author, i.itemcallnumber, i.barcode, 
 date(r.timestamp) AS "hold date", r.branchcode AS 'pickup branch', 
 i.homebranch AS 'owning branch', p.surname, p.firstname, p.cardnumber
FROM reserves r
LEFT JOIN biblio b ON (r.biblionumber=b.biblionumber)
LEFT JOIN items i ON (i.biblionumber=b.biblionumber)
LEFT JOIN borrowers p USING (borrowernumber)
WHERE i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues)
 AND i.itemnumber NOT IN (SELECT branchtransfers.itemnumber from branchtransfers where datearrived is null) 
 AND r.waitingdate IS NULL AND i.homebranch=<<Branch filled at|branches>>
group by b.biblionumber,p.borrowernumber
ORDER BY i.itemcallnumber ASC 
==== Holds to pull at a library with Code39 barcodes ====
* Developer: George H. Williams, Next Search Catalog/Northeast Kansas Library System
* Module: Holds/Circulation
* Purpose: Items in the holds queue at your library with a Code39 barcodes
* Status: Complete
* Version: Developed on Koha 3.22 : tested and verified on Koha 21.05
 SELECT 
 /* Get library and last seen information */ 
 Concat_Ws('', 
 Concat('Current: ', hold_fill_targets.source_branchcode), 
 Concat('Owned by: ', items.homebranch), 
 Concat('Last seen: ', items.datelastseen), 
 Concat( 
 '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
 biblio.biblionumber, 
 '\" target="_blank">Go to biblio</a>' 
 ) 
 ) AS INFO, 
 /* Get item information in home library/permanent location/itype/ccode/call number/copy number/author/title format */ 
 Concat_Ws('', 
 If( 
 LOCATIONS.lib = PERM_LOCATIONS.lib, 
 LOCATIONS.lib, 
 Concat(PERM_LOCATIONS.lib, " (", LOCATIONS.lib, ")") 
 ), 
 ITEMTYPESS.description, 
 CCODES.lib, 
 items.itemcallnumber, 
 items.copynumber 
 ) AS CALL_NUMBER, 
 /* Get title information */ 
 Concat_Ws('', 
 biblio.author, 
 Concat_Ws('', 
 biblio.title, 
 ExtractValue(biblio_metadata.metadata, '//datafield[@tag="245"]/subfield[@code="h"]'), 
 ExtractValue(biblio_metadata.metadata, '//datafield[@tag="245"]/subfield[@code="b"]'), 
 ExtractValue(biblio_metadata.metadata, '//datafield[@tag="245"]/subfield[@code="p"]'), 
 ExtractValue(biblio_metadata.metadata, '//datafield[@tag="245"]/subfield[@code="n"]') 
 ) 
 ) AS AUTHOR_TITLE, 
 /* Barcode in Code39 format with leading and ending asterixes */ 
 Concat_Ws('', 
 Concat( 
 '<img src="/cgi-bin/koha/svc/barcode?barcode=', 
 '*', 
 Upper(items.barcode), 
 '*', 
 '&type=Code39"></img>' 
 ), 
 items.barcode 
 ) AS 
 BARCODE 
 FROM 
 biblio LEFT JOIN 
 ( 
 (hold_fill_targets LEFT JOIN 
 items ON hold_fill_targets.itemnumber = items.itemnumber 
 ) LEFT JOIN 
 biblio_metadata ON items.biblionumber = biblio_metadata.biblionumber 
 ) ON biblio.biblionumber = biblio_metadata.biblionumber LEFT JOIN 
 ( 
 SELECT 
 authorised_values.category, 
 authorised_values.authorised_value, 
 authorised_values.lib 
 FROM 
 authorised_values 
 WHERE 
 authorised_values.category = 'CCODE' 
 ) CCODES ON CCODES.authorised_value = items.ccode LEFT JOIN 
 ( 
 /* Gets permanent location description instead of code */ 
 SELECT 
 authorised_values.category, 
 authorised_values.authorised_value, 
 authorised_values.lib 
 FROM 
 authorised_values 
 WHERE 
 authorised_values.category = 'LOC' 
 ) PERM_LOCATIONS ON PERM_LOCATIONS.authorised_value = items.permanent_location LEFT JOIN 
 ( 
 /* Gets temporary location description instead of code */ 
 SELECT 
 authorised_values.category, 
 authorised_values.authorised_value, 
 authorised_values.lib 
 FROM 
 authorised_values 
 WHERE 
 authorised_values.category = 'LOC' 
 ) LOCATIONS ON LOCATIONS.authorised_value = items.location LEFT JOIN 
 ( 
 /* Get item type description instead of code */ 
 SELECT 
 itemtypes.itemtype, 
 itemtypes.description 
 FROM 
 itemtypes 
 ) ITEMTYPESS ON ITEMTYPESS.itemtype = items.itype 
 WHERE 
 hold_fill_targets.source_branchcode LIKE <<Choose your library|branches>> 
 GROUP BY 
 hold_fill_targets.itemnumber 
 ORDER BY 
 items.homebranch, 
 If( 
 LOCATIONS.lib = PERM_LOCATIONS.lib, 
 LOCATIONS.lib, 
 Concat(PERM_LOCATIONS.lib, " (", LOCATIONS.lib, ")") 
 ), 
 ITEMTYPESS.description, 
 CCODES.lib, 
 items.itemcallnumber, 
 biblio.author, 
 biblio.title, 
 items.barcode 
==== Customizable Holds Queue====
*Developer: Christopher Brannon, Couer d'Alene Public Library/Cooperative Information Network
*Module: Circulation
*Purpose: Recreate Holds Queue results that can be customized to your needs. This example adds statuses and report date, as well as tweaks the CART location. This serves as an example to how the report can be beefed up.
*Status: Complete
SELECT CONCAT_WS(' ',avl.lib,avd.lib,IF(i.location="CART","Recently Returned","")) AS 'Status',r.reservenotes AS Notes,concat(b.title, ' ',ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="b"]')) AS 'Title',b.author AS 'Author',(SELECT lib FROM authorised_values WHERE category="LOC" AND authorised_value=i.permanent_location) AS 'Location',i.itemcallnumber AS 'Call Number',i.barcode AS 'Barcode',CONCAT_WS(', ',p.surname,p.firstname) AS 'Patron',r.branchcode AS 'Send To',r.reservedate AS 'Date',if(r.itemnumber IS NULL,'Next Available','Item Level') AS 'Type',Date(Now()) AS 'Report Date'
FROM reserves r
LEFT JOIN hold_fill_targets hft ON r.biblionumber = hft.biblionumber AND r.borrowernumber = hft.borrowernumber
LEFT JOIN items i ON i.itemnumber = hft.itemnumber
LEFT JOIN authorised_values avl ON i.itemlost = avl.authorised_value
LEFT JOIN authorised_values avd ON i.damaged = avd.authorised_value
LEFT JOIN biblio b ON b.biblionumber = i.biblionumber
LEFT JOIN biblio_metadata bi ON b.biblionumber = bi.biblionumber
LEFT JOIN borrowers p ON p.borrowernumber = r.borrowernumber
WHERE i.holdingbranch = <<Library|branches>> AND hft.itemnumber IS NOT NULL AND avl.category = "LOST" AND avd.category = "DAMAGED"
GROUP BY Barcode, Patron
ORDER BY Location, i.itemcallnumber, i.enumchron, b.author, b.title ASC
=== Holds and Statistics ===
==== Top 10 Titles Placed on Hold in the Last 6 Months ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds/Circulation
* Purpose: Top 10 titles placed on hold in the last 6 months showing titles, authors and ccode.
* Status: Complete
SELECT count(*) as holds, title, author, ccode 
FROM (
SELECT biblio.title, biblio.author, items.ccode, biblio.biblionumber
FROM reserves 
LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
LEFT JOIN items ON (biblio.biblionumber=items.biblionumber)
WHERE DATE(reserves.timestamp) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH) 
 AND DATE(reserves.timestamp) <=CURRENT_DATE()
UNION ALL
SELECT biblio.title, biblio.author, items.ccode, biblio.biblionumber
FROM old_reserves 
LEFT JOIN biblio ON (old_reserves.biblionumber=biblio.biblionumber)
LEFT JOIN items ON (biblio.biblionumber=items.biblionumber)
WHERE DATE(old_reserves.timestamp) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH) 
 AND DATE(old_reserves.timestamp) <=CURRENT_DATE()
 ) AS myholds 
GROUP BY biblionumber 
ORDER BY holds DESC 
LIMIT 10
==== Count of Holds by Month ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds/Circulation
* Purpose: This report asks for you to enter a year (twice) and then shows you holds counts for all months that year.
* Status: Complete
SELECT month, sum(count) AS holds
FROM (
SELECT MONTHNAME(reservedate) as month, count(*) as count
FROM reserves
WHERE YEAR(reservedate) = <<Hold Year (yyyy)>>
GROUP BY month
UNION ALL
SELECT MONTHNAME(reservedate) as month, count(*) as count
FROM old_reserves
WHERE YEAR(reservedate) = <<Repeat Hold Year (yyyy)>>
GROUP BY month
) as myholds
GROUP BY month
ORDER BY month ASC 
==== Single Title Holds ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds
* Purpose: A list of titles that have only 1 item that have holds on them
* Status: Complete
select b.title, b.biblionumber, count(r.reservedate) as holds 
from reserves r 
left join biblio b using (biblionumber) 
where r.biblionumber in 
 (select b.biblionumber 
 from biblio b 
 left join items i using (biblionumber) 
 group by b.biblionumber 
 having count(i.itemnumber) = 1) 
group by b.biblionumber
==== Holds Ratio by Homebranches ====
* Developer: George H. Williams - Latah County Library District
* Module: Holds
* Purpose: Essentially the same as "Hold Ratios" in the circulation reports but with item Homebranch information instead of Holdingbranch
* Notes: I didn't build the link to the biblio in the title field (as it is in the circulation report) so it would be easy for our staff to download the file as a spreadsheet if necessary. I also included some very basic 'notforloan' data for their information.
* Status: Complete
SELECT count(DISTINCT reserves.borrowernumber) as HOLDCOUNT,
 count(DISTINCT items.itemnumber) as ITEMCOUNT,
 (COUNT(DISTINCT reserves.borrowernumber) / count(DISTINCT items.itemnumber)) AS RATIO,
 biblio.title,
 CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber,'\" target="_blank">', biblio.biblionumber, '</a>' ) AS 'LINK_TO_BIBLIO',
 GROUP_CONCAT(DISTINCT items.homebranch SEPARATOR ' // ') AS HOMEBRANCHES,
 GROUP_CONCAT(DISTINCT items.location SEPARATOR ' // ') AS LOCATIONS,
 GROUP_CONCAT(DISTINCT items.itype SEPARATOR ' // ') AS ITYPES,
 GROUP_CONCAT(DISTINCT items.itemcallnumber SEPARATOR ' // ') AS CALLNUMBERS,
 GROUP_CONCAT(DISTINCT items.notforloan SEPARATOR ' // ') AS NOTLOAN
 FROM reserves LEFT JOIN items ON items.biblionumber=reserves.biblionumber 
 LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber
 WHERE items.itemlost=0 
 AND items.damaged=0
 GROUP BY biblio.biblionumber
 HAVING (COUNT(DISTINCT reserves.borrowernumber) / count(DISTINCT items.itemnumber))>3
 ORDER BY RATIO DESC
==== Average wait time on holds ====
* Developer: Melia Meggs, ByWater Solutions
* Module: Holds
* Purpose: Average wait of a hold (from the date placed to the arrival of the item to the branch)
* Status: Complete
SELECT o.itemnumber, 
 (SELECT AVG(ABS(DATEDIFF(o.reservedate, o.waitingdate)))) AS 'Average days waiting for hold', 
 b.title, b.author, b.copyrightdate, i.barcode, i.dateaccessioned, i.ccode 
FROM old_reserves o 
LEFT JOIN items i ON (o.itemnumber = i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber = b.biblionumber) 
WHERE o.waitingdate IS NOT NULL AND o.cancellationdate IS NULL
 AND o.reservedate between <<Hold placed between (yyyy-mm-dd)|date>> 
 and <<and (yyyy-mm-dd)|date>> 
GROUP BY o.itemnumber, b.title, b.author, b.copyrightdate,
i.barcode, i.dateaccessioned, i.ccode 
ORDER BY (SELECT AVG(ABS(DATEDIFF(o.reservedate, o.waitingdate)))) ASC
==== Titles with more than X holds ====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Holds
* Purpose: Titles with more than the entered number of holds on them
* Status: Complete
* Note: Requires 19.11
select concat(b.title, ' ', b.subtitle) AS title, b.author, 
 count(h.reservedate) as 'holds' 
from biblio b 
left join reserves h on (b.biblionumber=h.biblionumber) 
group by b.biblionumber 
having count(h.reservedate) >= <<Min number of holds>>
==== Count of last month's filled requests by collection code ====
* Developer: George Williams - Northeast Kansas Library System
* Module: Holds
* Purpose: Counts the requests that were filled in the previous calendar month by collection code
* Status: Complete
* Version: Created on 18.11
* Requirements: You must have the system preference HoldsLog set to "Log"
* Notes: Our system has an "(Unclassified)" collection code using the authorised value "XXX." This report is built so that "(Unclassified)" works as a catch-all for items where a collection code can't be determined. If the report is unable to identify a collection code on an item that filled a request, it assumes that item's collection code is "XXX." If you want to use a catch-all for items counted on your system like this, you will need to update "Coalesce(items.ccode, deleteditems.ccode, "XXX") AS ccode" so that "XXX" matches the authorised value for the collection code you want to use on your system.This report is built to count the items that filled requests in the previous calendar month. If you want to go back further you'll need to update all of the instances of "INTERVAL 1 MONTH" to "INTERVAL 2 MONTH" to go back two months, "INTERVAL 3 MONTH" to go back three months, etc. In any event, you can only really go back as far as your action_logs are saved and your HoldsLog system preference must be set to "Log".
SELECT
 branchandcodes.branchcode,
 branchandcodes.lib AS CCODE,
 Count(requestdata_all.reserve_id) AS REQUESTS_FILLED_LM
FROM
 (
 SELECT
 branches.branchcode,
 ccodes.lib,
 ccodes.authorised_value
 FROM
 branches,
 (
 SELECT
 authorised_values.category,
 authorised_values.authorised_value,
 authorised_values.lib
 FROM
 authorised_values
 WHERE
 authorised_values.category = 'ccode'
 ) ccodes
 ORDER BY
 branches.branchcode,
 ccodes.lib
 ) branchandcodes
 LEFT JOIN (
 SELECT
 requests_filled.branchcode,
 requests_filled.reserve_id,
 requests_filled.itemnumber,
 Coalesce(items.ccode, deleteditems.ccode, "XXX") AS ccode
 FROM
 (
 SELECT
 old_reserves.branchcode,
 old_reserves.reserve_id,
 Coalesce(old_reserves.itemnumber, deleted_itemnumbers.itemnumber) AS itemnumber
 FROM
 old_reserves
 LEFT JOIN (
 SELECT
 action_logs.object AS reserve_id,
 SubString_Index(SubString_Index(action_logs.info, "itemnumber' => '", -1), "'", 1) AS itemnumber
 FROM
 action_logs
 WHERE
 action_logs.module = 'HOLDS' AND
 action_logs.info LIKE "%itemnumber' => '%" AND
 action_logs.action LIKE 'DEL%' AND
 Year(action_logs.timestamp) = Year(Now() - INTERVAL 1 MONTH) AND
 Month(action_logs.timestamp) = Month(Now() - INTERVAL 1 MONTH)
 GROUP BY
 action_logs.action_id
 ) deleted_itemnumbers
 ON deleted_itemnumbers.reserve_id = old_reserves.reserve_id
 JOIN action_logs
 ON action_logs.object = old_reserves.reserve_id
 WHERE
 Month(action_logs.timestamp) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(action_logs.timestamp) = Year(Now() - INTERVAL 1 MONTH) AND
 old_reserves.found = 'F' AND
 action_logs.module = 'HOLDS' AND
 action_logs.action LIKE "DEL%"
 GROUP BY
 old_reserves.branchcode,
 old_reserves.reserve_id,
 Coalesce(old_reserves.itemnumber, deleted_itemnumbers.itemnumber)
 ) requests_filled
 LEFT JOIN items
 ON items.itemnumber = requests_filled.itemnumber
 LEFT JOIN deleteditems
 ON deleteditems.itemnumber = requests_filled.itemnumber
 GROUP BY
 requests_filled.branchcode,
 requests_filled.reserve_id,
 requests_filled.itemnumber
 ) requestdata_all
 ON requestdata_all.ccode = branchandcodes.authorised_value AND
 requestdata_all.branchcode = branchandcodes.branchcode
WHERE
 branchandcodes.branchcode LIKE <<Choose your library|branches>>
GROUP BY
 branchandcodes.branchcode,
 branchandcodes.lib
==== Last month's request statistics ====
* Developer: George Williams - Northeast Kansas Library System
* Module: Holds
* Purpose: Captures a ton of statistics about requests placed, filled, cancelled, and expired in the previous calendar month
* Status: Complete
* Version: Created on 18.11
* Requirements: You must have the system preference HoldsLog set to "Log" to capture the most accurate data
* Notes: Gathers statistics for:
# PLACED_LM = number of reqeusts placed
# FILLED_LM = number of requests filled last month (requires HoldsLog set to "Log")
# CNX_FROM_HOLD_SHELF_LM = number of requests that were cancelled after the item was waiting for pickup
# CNX_IN_TRASIT_LM = number of requests that were cancelled while the item was in transit
# CNX_BEFORE_ACTION_LM = number of requests that were cancelled before an item was checked in to trigger the reqeust
# CNX_TOTAL_LM = number of total cancellations (CNX_FROM_HOLD_SHELF_LM + CNX_IN_TRASIT_LM + CNX_BEFORE_ACTION_LM = this column)
# EXPIRED_LM = number of requests that expired before an item was checked in to trigger the request (requires HoldsLog set to "Log")
# PLACED_BY_STAFF = requests placed through the intranet (requires HoldsLog set to "Log")
# PLACED_BY_PATRON = requests placed through the OPAC (requires HoldsLog set to "Log")
SELECT
 branches.branchcode,
 Sum(Coalesce(requests_placed.Count_reserve_id, 0)) AS PLACED_LM,
 Coalesce(requests_filled.Count_reserve_id, 0) AS FILLED_LM,
 Coalesce(requests_cancelled_from_hold_shelf.Count_reserve_id, 0) AS CNX_FROM_HOLD_SHELF_LM,
 Coalesce(requests_cancelled_in_transit.Count_reserve_id, 0) AS CNX_IN_TRASIT_LM,
 Coalesce(requests_cancelled_before_pulled.Count_reserve_id, 0) AS CNX_BEFORE_ACTION_LM,
 Coalesce(requests_cancelled.Count_reserve_id, 0) AS CNX_TOTAL_LM,
 Coalesce(requests_expired_unfilled.Count_reserve_id, 0) AS EXPIRED_LM,
 Sum(Coalesce(requests_placed_staff.Count_reserve_id, 0)) AS PLACED_BY_STAFF,
 (Sum(Coalesce(requests_placed.Count_reserve_id, 0)) - Sum(Coalesce(requests_placed_staff.Count_reserve_id, 0))) AS PLACED_BY_PATRON
FROM
 branches
 LEFT JOIN (
 SELECT
 reserves.branchcode,
 Count(reserves.reserve_id) AS Count_reserve_id
 FROM
 reserves
 WHERE
 Year(reserves.reservedate) = Year(Now() - INTERVAL 1 MONTH) AND
 Month(reserves.reservedate) = Month(Now() - INTERVAL 1 MONTH)
 GROUP BY
 reserves.branchcode
 UNION
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 WHERE
 Month(old_reserves.reservedate) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(old_reserves.reservedate) = Year(Now() - INTERVAL 1 MONTH)
 GROUP BY
 old_reserves.branchcode
 ) requests_placed
 ON branches.branchcode = requests_placed.branchcode
 LEFT JOIN (
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 JOIN action_logs
 ON action_logs.object = old_reserves.reserve_id
 WHERE
 Month(action_logs.timestamp) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(action_logs.timestamp) = Year(Now() - INTERVAL 1 MONTH) AND
 old_reserves.found = 'F' AND
 action_logs.module = 'HOLDS' AND
 action_logs.action LIKE "DEL%"
 GROUP BY
 old_reserves.branchcode
 ) requests_filled
 ON branches.branchcode = requests_filled.branchcode
 LEFT JOIN (
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 WHERE
 Month(old_reserves.cancellationdate) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(old_reserves.cancellationdate) = Year(Now() - INTERVAL 1 MONTH) AND
 (old_reserves.found <> 'F' OR
 old_reserves.found IS NULL)
 GROUP BY
 old_reserves.branchcode
 ) requests_cancelled
 ON branches.branchcode = requests_cancelled.branchcode
 LEFT JOIN (
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 WHERE
 Month(old_reserves.cancellationdate) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(old_reserves.cancellationdate) = Year(Now() - INTERVAL 1 MONTH) AND
 old_reserves.found = 'T'
 GROUP BY
 old_reserves.branchcode
 ) requests_cancelled_in_transit
 ON branches.branchcode = requests_cancelled_in_transit.branchcode
 LEFT JOIN (
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 WHERE
 Month(old_reserves.cancellationdate) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(old_reserves.cancellationdate) = Year(Now() - INTERVAL 1 MONTH) AND
 old_reserves.found = 'W'
 GROUP BY
 old_reserves.branchcode
 ) requests_cancelled_from_hold_shelf
 ON branches.branchcode = requests_cancelled_from_hold_shelf.branchcode
 LEFT JOIN (
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 WHERE
 Month(old_reserves.cancellationdate) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(old_reserves.cancellationdate) = Year(Now() - INTERVAL 1 MONTH) AND
 old_reserves.found IS NULL
 GROUP BY
 old_reserves.branchcode
 ) requests_cancelled_before_pulled
 ON branches.branchcode = requests_cancelled_before_pulled.branchcode
 LEFT JOIN (
 SELECT
 reserves.branchcode,
 Count(reserves.reserve_id) AS Count_reserve_id
 FROM
 reserves
 JOIN action_logs
 ON action_logs.object = reserves.reserve_id
 WHERE
 Year(reserves.reservedate) = Year(Now() - INTERVAL 1 MONTH) AND
 Month(reserves.reservedate) = Month(Now() - INTERVAL 1 MONTH) AND
 action_logs.module = 'HOLDS' AND
 action_logs.action LIKE 'CREAT%' AND
 action_logs.interface = 'intranet'
 GROUP BY
 reserves.branchcode
 UNION
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 JOIN action_logs
 ON action_logs.object = old_reserves.reserve_id
 WHERE
 Month(old_reserves.reservedate) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(old_reserves.reservedate) = Year(Now() - INTERVAL 1 MONTH) AND
 action_logs.module = 'HOLDS' AND
 action_logs.action LIKE 'CREAT%' AND
 action_logs.interface = 'intranet'
 GROUP BY
 old_reserves.branchcode
 ) requests_placed_staff
 ON requests_placed_staff.branchcode = branches.branchcode
 LEFT JOIN (
 SELECT
 old_reserves.branchcode,
 Count(old_reserves.reserve_id) AS Count_reserve_id
 FROM
 old_reserves
 JOIN action_logs
 ON action_logs.object = old_reserves.reserve_id
 WHERE
 Month(action_logs.timestamp) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(action_logs.timestamp) = Year(Now() - INTERVAL 1 MONTH) AND
 old_reserves.found IS NULL AND
 action_logs.module = 'HOLDS' AND
 action_logs.action = 'CANCEL' AND
 action_logs.user = 0
 GROUP BY
 old_reserves.branchcode
 ) requests_expired_unfilled
 ON requests_expired_unfilled.branchcode = branches.branchcode
GROUP BY
 branches.branchcode,
 Coalesce(requests_filled.Count_reserve_id, 0),
 Coalesce(requests_cancelled_from_hold_shelf.Count_reserve_id, 0),
 Coalesce(requests_cancelled_in_transit.Count_reserve_id, 0),
 Coalesce(requests_cancelled_before_pulled.Count_reserve_id, 0),
 Coalesce(requests_cancelled.Count_reserve_id, 0),
 requests_expired_unfilled.Count_reserve_id
=== Holds Gone Wild ===
Anomalous holds go here 
==== Overdues With Holds Waiting ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds/Circulation
* Purpose: A list of items that are overdue that have holds on them. A report to help you know who to call with overdues to tell them others are waiting for their items
* Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, 
 p.phone, p.address, p.city, p.zipcode, 
 c.date_due, 
 (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', 
 i.itype, b.title, b.author, i.itemcallnumber, 
 i.barcode, COUNT(h.biblionumber) AS 'holds' 
FROM borrowers p
LEFT JOIN issues c using (borrowernumber) 
LEFT JOIN items i using (itemnumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber) 
WHERE c.branchcode = <<Branch Code|branches>> AND c.date_due < curdate()
GROUP BY h.biblionumber 
HAVING COUNT(h.biblionumber) > 0 
ORDER BY p.surname ASC, c.date_due ASC
==== Holds Queue Workaround ====
* Developer: George H. Williams (Latah County Library District) and Christopher Brannon (Coeur d'Alene Public Library)
* Module: Holds
* Purpose: In Koha 3.10 there is a bug that is making the built in "Holds queue" report in the circulation module work incorrectly (bug 9950). This report can act as a replacement for the built in report until the bug is fixed. UPDATE The fix for bug 9950 is now available as of Koha 3.10.8.
* Status: Deprecated -- the workaround is no longer needed if you upgrade to 3.10.8 or later - Further depreciated now that Marc data has been moved to the biblio.metadata table
SELECT items.location, authorised_values.lib_opac AS 'COLLECTIONCODE', items.itemcallnumber, items.enumchron, biblio.author, concat(biblio.title, ' ',ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]')) AS 'TITLE',items.barcode, reserves.suspend
 FROM (((((reserves INNER JOIN biblio ON reserves.biblionumber = biblio.biblionumber) 
 INNER JOIN borrowers ON reserves.borrowernumber = borrowers.borrowernumber) 
 INNER JOIN items ON biblio.biblionumber = items.biblionumber) 
 LEFT JOIN biblioitems bi ON (biblio.biblionumber=bi.biblionumber)
 LEFT JOIN hold_fill_targets ON (reserves.biblionumber = hold_fill_targets.biblionumber) 
 AND (reserves.borrowernumber = hold_fill_targets.borrowernumber)) 
 LEFT JOIN branchtransfers ON items.itemnumber = branchtransfers.itemnumber) 
 LEFT JOIN authorised_values ON items.ccode = authorised_values.authorised_value
 WHERE (((authorised_values.category)="ccode") 
 And ((reserves.reservedate)<=now()) 
 And ((reserves.waitingdate) Is Null) 
 And ((reserves.priority)=1)
 And ((items.itemnumber NOT IN (SELECT itemnumber FROM reserves WHERE itemnumber=items.itemnumber AND found IS NOT NULL)))
 And ((items.notforloan)=0) 
 And ((items.damaged)=0) 
 And ((items.itemlost)=0)
 And ((items.wthdrawn)=0) 
 And ((items.onloan) Is Null) 
 And ((reserves.itemnumber) Is Not Null 
 And (reserves.itemnumber)=items.itemnumber) 
 And ((items.itype)<>"REF") 
 And ((hold_fill_targets.itemnumber) Is Null)) 
 Or (((authorised_values.category)="ccode") 
 And ((hold_fill_targets.itemnumber)=items.itemnumber))
 And ((reserves.found) IS NULL)
 GROUP BY items.holdingbranch, authorised_values.lib_opac, items.itemcallnumber, items.enumchron, biblio.author, biblio.title, items.barcode, reserves.branchcode
 HAVING items.holdingbranch=<<Branch filled at|branches>>
 And Count(branchtransfers.datesent)=Count(branchtransfers.datearrived)
 AND reserves.suspend=0
 ORDER BY items.location, authorised_values.lib_opac, items.itemcallnumber, items.enumchron, biblio.author, biblio.title ASC
==== Stale Holds ====
* Developer: Sean Park, Coos County Libraries
* Module: Holds
* Purpose: provides a list of holds that are older than 30 days, based on i.homebranch, sorted by oldest first.
* Status: Complete
SELECT
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>') AS 'bib',
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',p.borrowernumber,'\">',p.surname,'</a>') AS 'surname/patron link', p.firstname AS 'first name', p.email, p.phone, p.cardnumber AS 'patron barcode', h.reservedate AS 'hold placed date', i.homebranch, i.barcode AS 'item barcode'
FROM reserves h
LEFT JOIN borrowers p USING (borrowernumber)
LEFT JOIN items i ON (h.biblionumber=i.biblionumber)
LEFT JOIN biblio ON (i.biblionumber=biblio.biblionumber)
WHERE h.reservedate IS NOT NULL AND i.homebranch=<<library|branches>>
AND date (h.reservedate) < DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH)
AND h.waitingdate IS NULL
ORDER BY h.reservedate ASC
==== Stale Holds (Customized for Holds Cleanup) ====
* Developer: Alex Chen, Butte County Library
* Module: Holds
* Purpose: Provide a list of Expired Holds on a specified date, with detailed patron, item, date, and location info.
* Status: Complete
SELECT *
FROM
(
SELECT
	borrowers.cardnumber AS "Cardnumber",
	CONCAT(borrowers.surname, ', ', borrowers.firstname) AS "Patron",
	items.barcode "ItemBarcode",
	CONCAT(biblio.title, ' by ', biblio.author) AS "Title",
	old_reserves.reservedate AS "ReservedDate",
	old_reserves.waitingdate AS "WaitingDate",
	old_reserves.expirationdate AS "ExpirationDate",
	old_reserves.branchcode AS "PickupLocation"
FROM old_reserves
LEFT JOIN items ON (items.itemnumber = old_reserves.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber = old_reserves.biblionumber)
LEFT JOIN borrowers ON (borrowers.borrowernumber = old_reserves.borrowernumber)
WHERE
	old_reserves.expirationdate = (@HoldsExpirationDate := <<Select an expiration date |date>>) COLLATE utf8mb4_unicode_ci
	AND old_reserves.branchcode = (@PickupBranch := <<Select a pickup branch |branches>>) COLLATE utf8mb4_unicode_ci
UNION
SELECT
	borrowers.cardnumber "Cardnumber",
	CONCAT(borrowers.surname, ', ', borrowers.firstname) AS "Patron",
	items.barcode "ItemBarcode",
	CONCAT(biblio.title, ' by ', biblio.author) AS "Title",
	reserves.reservedate AS "ReservedDate",
	reserves.waitingdate AS "WaitingDate",
	reserves.expirationdate AS "ExpirationDate",
	reserves.branchcode AS "PickupLocation"
FROM reserves
LEFT JOIN items ON (items.itemnumber = reserves.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber = reserves.biblionumber)
LEFT JOIN borrowers ON (borrowers.borrowernumber = reserves.borrowernumber)
WHERE
	reserves.expirationdate = @HoldsExpirationDate COLLATE utf8mb4_unicode_ci
	AND reserves.branchcode = @PickupBranch COLLATE utf8mb4_unicode_ci
) as final
ORDER BY Patron, WaitingDate ASC
==== Cancelled Holds ====
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Holds
* Purpose: A list of holds that were cancelled by branch and date range
* Status: Complete
select b.title, b.author, p.surname, p.firstname, r.reservedate, 
 r.cancellationdate, r.branchcode 
from old_reserves r 
left join biblio b using (biblionumber) 
left join borrowers p using (borrowernumber) 
where r.cancellationdate between <<Cancelled between (yyyy-mm-dd)|date>> 
 and <<and (yyyy-mm-dd)|date>> and r.branchcode =<<Branch|branches>> 
==== Duplicate Holds ====
* Developer: Liz Rea (NEKLS)
* Module: Holds
* Purpose: This report will detect "double-click" placed duplicate holds. Do note that it doesn't necessarily mean anything (currently) as processes done on one of the duplicates will do the same to the other (deleting one deletes both, for example).
* Status: Completed
SELECT reserves.biblionumber, reserves.borrowernumber, biblio.title, borrowers.firstname, borrowers.surname 
FROM reserves 
LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber) 
LEFT JOIN borrowers ON (reserves.borrowernumber=borrowers.borrowernumber) 
GROUP BY reserves.borrowernumber, reserves.biblionumber 
HAVING (COUNT(reserves.borrowernumber)>=2 AND COUNT(reserves.biblionumber)>=2)
==== Holds Queue Work-Around for Bug 18001 ====
* Developer: George Williams, Northeast Kansas Library System
* Module: Holds
* Purpose: LocalHoldsPriority can cause multiple holds queue lines for same hold request (see community bug 18001). This report is a work-around report that generates the same data as the built-in holds queue report, just without the duplicate data
* Version: tested on 3.22.10 with LocalHoldsPriority set to GIVE priority for filling holds to patrons whose PICKUP LIBRARY matches the item's HOME LIBRARY
* Status: Complete
SELECT
 Concat_Ws('',
 hold_fill_targets.source_branchcode,
 items.homebranch,
 (Concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
 biblio.biblionumber,
 '\" target="_blank">Go to biblio</a>'))
 ) AS CURRENT_OWNING,
 Concat_Ws('',
 items.location,
 authorised_values.lib,
 items.itemcallnumber
 ) AS CALL_NUMBER,
 Concat_Ws('',
 biblio.author,
 (Concat_Ws('',
 biblio.title,
 ExtractValue(biblioitems.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]'),
 ExtractValue(biblioitems.marcxml, '//datafield[@tag="245"]/subfield[@code="p"]'),
 ExtractValue(biblioitems.marcxml, '//datafield[@tag="245"]/subfield[@code="n"]')
 ))) AS AUTHOR_TITLE,
 Concat_Ws('',
 (Concat('<img src="/cgi-bin/koha/svc/barcode?barcode=',
 '*',
 Upper(items.barcode), '*',
 '&type=Code39"></img>')
 ),
 items.barcode
 ) AS BARCODE
FROM
 biblio
 LEFT JOIN ((hold_fill_targets
 LEFT JOIN items ON hold_fill_targets.itemnumber = items.itemnumber)
 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber) ON biblio.biblionumber =
 biblioitems.biblionumber
 LEFT JOIN authorised_values ON items.ccode = authorised_values.authorised_value
 INNER JOIN reserves ON hold_fill_targets.borrowernumber = reserves.borrowernumber AND
 hold_fill_targets.biblionumber = reserves.biblionumber
WHERE
 If(reserves.itemnumber IS NULL, hold_fill_targets.itemnumber, reserves.itemnumber) = hold_fill_targets.itemnumber AND
 hold_fill_targets.source_branchcode LIKE <<Select your branch|ZBRAN>> AND
 authorised_values.category = "ccode"
GROUP BY
 Concat_Ws('', items.location, authorised_values.lib, items.itemcallnumber),
 hold_fill_targets.source_branchcode,
 items.holdingbranch,
 biblio.author,
 biblio.title,
 items.barcode
==== Unfilled Holds ====
* Developer: Nick Clemens, ByWater Solutions
* Module: Holds
* Purpose: This report will list all holds no waiting and provide info on whether it is a title or item level hold as well as showing the availability of items on the record
* Status: Completed
SELECT reserves.found,
 borrowers.surname,
 borrowers.firstname,
 borrowers.cardnumber,
 reserves.reservedate AS 'date reserved',
 reserves.priority,
 CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'">',biblio.title,'</a>') AS title,
 biblio.author,
 GROUP_CONCAT(items.barcode, '-',
 IF(r2.found IS NOT NULL,
 'reserved',
 IF(onloan IS NOT NULL,
 'onloan',
 IF(notforloan!=0,
 'notforloan',
 IF(itemlost!=0,
 'itemlost',
 'available')
 ))) SEPARATOR ' | ') AS itemstatuses,
 IF(reserves.itemnumber IS NULL,
 'Next Available',
 barcode
 ) AS 'Specific Item'
FROM reserves
 LEFT JOIN borrowers USING (borrowernumber)
 LEFT JOIN biblio USING (biblionumber)
 LEFT JOIN biblioitems USING (biblionumber)
 LEFT JOIN items USING (biblionumber)
 LEFT JOIN reserves r2 ON r2.itemnumber=items.itemnumber
WHERE (reserves.found IS NULL OR reserves.found != 'W')
GROUP BY reserves.reserve_id
ORDER BY title
==== Holds with completed branch transfers, still in status 'T' ====
* Developer: Barton Chittenden, ByWater Solutions
* Module: Holds
* Purpose: Holds with completed branch transfers, still in status 'T'.
* Status: Completed
SELECT 
 itemnumber,
 CONCAT( '<a href="/cgi-bin/koha/reserve/request.pl?biblionumber=', bib.biblionumber, '">' , bib.title, '</a>' ) as title,
 bib.title,
 i.holdingbranch,
 r.expirationdate
FROM
 reserves r
 INNER JOIN branchtransfers USING (itemnumber)
 INNER JOIN items i USING (itemnumber)
 INNER JOIN biblio bib ON (i.biblionumber = bib.biblionumber)
WHERE
 found='T'
 AND branchtransfers.datearrived IS NOT NULL
 AND NOT exists (
 SELECT * FROM branchtransfers
 WHERE r.itemnumber = branchtransfers.itemnumber AND branchtransfers.datearrived IS NULL 
 )
GROUP BY itemnumber
==== Captured Holds Expired Yesterday and Cancelled Today ====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Holds
* Purpose: For libraries with ExpireReservesMaxPickUpDelay set to allow, this report lists items with cancelled holds that need to be removed from the holds shelf. The "Holds" column counts un-filled holds on this bib record to indicate whether or not the item should be checked in to trigger another hold.
* Status: Completed
SELECT old_reserves.branchcode, biblio.title, author, itemcallnumber, barcode, cardnumber, surname, firstname, ifnull(r.holds,'None') as holds
FROM old_reserves
LEFT JOIN biblio USING (biblionumber) 
LEFT JOIN items ON (old_reserves.itemnumber=items.itemnumber) 
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN (select biblionumber, count(reserve_id) as holds from reserves where found is null group by biblionumber) r on (old_reserves.biblionumber=r.biblionumber)
WHERE found='w' AND expirationdate=curdate() - interval 1 day AND cancellationdate=curdate() and old_reserves.branchcode=<<Select Branch|branches>>
ORDER BY old_reserves.branchcode, cn_sort
==== Waiting Holds Cancelled by a Cronjob Today ====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Holds
* Purpose: For libraries with ExpireReservesMaxPickUpDelay set to allow, this report lists items with cancelled holds that need to be removed from the holds shelf. The "Holds" column counts un-filled holds on this bib record to indicate whether or not the item should be checked in to trigger another hold. This version relies on the action logs for more reliable data than the version above.
* Status: Completed
SELECT old_reserves.branchcode, biblio.title, author, itemcallnumber, barcode, cardnumber, surname, firstname, ifnull(r.holds_count,'None') as holds_count
FROM action_logs
 LEFT JOIN old_reserves ON (action_logs.object=old_reserves.reserve_id AND action_logs.module='holds')
 LEFT JOIN biblio USING (biblionumber) 
 LEFT JOIN items ON (old_reserves.itemnumber=items.itemnumber) 
 LEFT JOIN borrowers USING (borrowernumber)
 LEFT JOIN (select biblionumber, count(reserve_id) as holds_count from reserves where found is null group by biblionumber) r on (old_reserves.biblionumber=r.biblionumber)
WHERE action_logs.action='cancel'
 AND date(action_logs.timestamp)=curdate()
 AND action_logs.interface='cron'
 AND old_reserves.found='W'
ORDER BY old_reserves.branchcode, cn_sort
==== Holds to Call ====
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Holds
* Purpose: For libraries who prefer to call patrons without email/SMS, rather than sending a print notice. Set the content of the print HOLD notice to contain any bib/item information you'd like to include in your call
* Status: Completed
select firstname, surname, phone, content
from message_queue left join borrowers using (borrowernumber)
where date(time_queued)=<<Waiting since this date|date>> and message_transport_type='print' and letter_code='hold'
order by surname, firstname
==== Holds on not for loan statuses ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Holds
* Purpose: To get a list of holds that are on items that have a not for loan status (like "In processing")
* Status: Completed
SELECT
 biblio.title AS "Title",
 biblio.author AS "Author",
 borrowers.cardnumber AS "Cardnumber",
 borrowers.surname AS "Surname",
 borrowers.firstname AS "Firstname",
 notforloan.lib AS "Status",
 items.itemcallnumber AS "Call number",
 items.enumchron AS "Volume"
FROM
 reserves
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN biblio USING (biblionumber)
LEFT JOIN items USING (biblionumber)
LEFT JOIN (SELECT * FROM authorised_values WHERE category = "NOT_LOAN") notforloan ON (items.notforloan=notforloan.authorised_value)
WHERE items.notforloan !=0
==== Items with Holds that have a Lost Value ====
* Developer: Kelly McElligott, ByWater Solutions
* Module: Holds
* Purpose: This will list all the items in your system that are on hold and also are marked a lost status.
* Status: Completed
SELECT i.itemnumber, i.reserves, i.issues, i.datelastseen,i.dateaccessioned, i.ccode, b.title, b.author,i.itemcallnumber, i.barcode, v.lib,IF(h.reservedate IS NULL, , 'on hold') as holds
FROM items i
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN reserves h on (b.biblionumber=h.biblionumber)
WHERE h.reservedate != AND i.itemlost != 0 AND v.category='LOST'
==== Holds on withdrawn items ====
* Developer: Caroline Cyr La Rose, inLibro
* Module: Holds
* Purpose: Lists holds on records with withdrawn items
* Status: Completed
* Notes: reports headings are translated in French, you can change them as needed, or remove the AS "Xyz" parts. I just find that it looks nicer than database headers
SELECT
 biblio.title AS "Titre",
 biblio.author AS "Auteur",
 borrowers.cardnumber AS "Numéro de carte",
 borrowers.surname AS "Nom de famille",
 borrowers.firstname AS "Prénom",
 withdrawn.lib AS "Élagué",
 items.itemcallnumber AS "Cote",
 items.enumchron AS "Volume"
 FROM
 reserves
 LEFT JOIN borrowers USING (borrowernumber)
 LEFT JOIN biblio USING (biblionumber)
 LEFT JOIN items USING (biblionumber)
 LEFT JOIN (SELECT * FROM authorised_values WHERE category = "WITHDRAWN") withdrawn ON (items.withdrawn=withdrawn.authorised_value)
 WHERE items.withdrawn !=0
== Curbside ==
=== Curbside Appointments ===
==== Curbside Pickup Schedules Lookup ====
* Developer: Alex Chen, Butte County Library
* Module: Curbside
* Purpose: This report shows a list of scheduled pickups by a given date range at a branch. You can use this report to check on past and future scheduled pickups.
* Status: Completed
SELECT
 CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',curbside_pickups.borrowernumber,'\">', CONCAT(patronTable.firstname, ' ', patronTable.surname), '</a>') AS "Patron",
 patronTable.cardnumber AS "CardNumber",
 curbside_pickups.branchcode AS "Branch",
 curbside_pickups.scheduled_pickup_datetime AS "ScheduledPickupTime",
 curbside_pickups.staged_datetime AS "StagedTime",
 CONCAT(stagedByTable.firstname, ' ', stagedByTable.surname) AS "StagedBy",
 curbside_pickups.arrival_datetime AS "ArrivalTime",
 curbside_pickups.delivered_datetime AS "DeliveredTime",
 CONCAT(deliveredByTable.firstname, ' ', deliveredByTable.surname) AS "DeliveredBY",
 curbside_pickups.notes AS "Notes"
FROM curbside_pickups
LEFT JOIN borrowers patronTable ON (patronTable.borrowernumber = curbside_pickups.borrowernumber)
LEFT JOIN borrowers stagedByTable ON (stagedByTable.borrowernumber = curbside_pickups.staged_by)
LEFT JOIN borrowers deliveredByTable ON (deliveredByTable.borrowernumber = curbside_pickups.delivered_by)
WHERE
 curbside_pickups.scheduled_pickup_datetime BETWEEN <<Scheduled between: |date>> AND <<and: |date>>
 AND curbside_pickups.branchcode = <<At which branch? |branches>>
ORDER BY curbside_pickups.scheduled_pickup_datetime DESC
Category:Documentation