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

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('<br />', 
      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('<br />', 
      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('<br />', 
      biblio.author, 
      Concat_Ws('<br />', 
        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('<br />', 
      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('<br />',
    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('<br />',
    items.location,
    authorised_values.lib,
    items.itemcallnumber
  ) AS CALL_NUMBER,
  Concat_Ws('<br />',
    biblio.author,
    (Concat_Ws('<br />',
      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('<br />',
    (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('<br />', 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

SQL Reports Holds

Holds

Holds Counts by Date

Holds placed in date range

Holds filled in date range

Monthly in holds placed by branch in current year

Monthly holds both placed and filled, by branch

Monthly holds filled by branch

Holds by Year, Month and Status

Patrons and Holds

Patrons Holds History List

Patrons with Holds Waiting at Library

List of all Patrons from a Single Branch with open Hold Requests

List of titles with holds and on loan in a specific cardnumber

Holds and Library Branches

Count of holds filled by another branch

List of all items currently on loan at another library

List of all items currently borrowed from another library

List of total Holds placed at a specific branch during a specified period

Holds to Pull

Holds to Pull

Holds to Pull at Branch

Holds to pull at a library with Code39 barcodes

Customizable Holds Queue

Holds and Statistics

Top 10 Titles Placed on Hold in the Last 6 Months

Count of Holds by Month

Single Title Holds

Holds Ratio by Homebranches

Average wait time on holds

Titles with more than X holds

Count of last month's filled requests by collection code

Last month's request statistics

Holds Gone Wild

Overdues With Holds Waiting

Holds Queue Workaround

Stale Holds

Stale Holds (Customized for Holds Cleanup)

Cancelled Holds

Duplicate Holds

Holds Queue Work-Around for Bug 18001

Unfilled Holds

Holds with completed branch transfers, still in status 'T'

Captured Holds Expired Yesterday and Cancelled Today

Waiting Holds Cancelled by a Cronjob Today

Holds to Call

Holds on not for loan statuses

Items with Holds that have a Lost Value

Holds on withdrawn items

Curbside

Curbside Appointments

Curbside Pickup Schedules Lookup

Navigation menu

Search