Koha Test Wiki MW Canasta on Koha Portainer

Test major Koha Wiki changes or bug fixes here without fear of breaking the production wiki.

For the current Koha Wiki, visit https://wiki.koha-community.org .

SQL Reports Holds

From Koha Test Wiki MW Canasta on Koha Portainer
Jump to navigation Jump to search

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(timestamp) 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('<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

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:
  1. PLACED_LM = number of reqeusts placed
  2. FILLED_LM = number of requests filled last month (requires HoldsLog set to "Log")
  3. CNX_FROM_HOLD_SHELF_LM = number of requests that were cancelled after the item was waiting for pickup
  4. CNX_IN_TRASIT_LM = number of requests that were cancelled while the item was in transit
  5. CNX_BEFORE_ACTION_LM = number of requests that were cancelled before an item was checked in to trigger the reqeust
  6. CNX_TOTAL_LM = number of total cancellations (CNX_FROM_HOLD_SHELF_LM + CNX_IN_TRASIT_LM + CNX_BEFORE_ACTION_LM = this column)
  7. EXPIRED_LM = number of requests that expired before an item was checked in to trigger the request (requires HoldsLog set to "Log")
  8. PLACED_BY_STAFF = requests placed through the intranet (requires HoldsLog set to "Log")
  9. 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
Retrieved from "https://wiki.test.koha-community.org/w/index.php?title=SQL_Reports_Holds&oldid=30501"