SQL Reports Holds

From Koha Wiki

Jump to: navigation, search
Home > Documentation

This is a page that will contain the SQL Reports relating to Holds.

Contents

Holds

Holds by Date

Holds placed in date range

  • Developer: Nicole C. Engard, 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. Engard, 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

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. Engard, 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. Engard, 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


Holds to Pull

Holds to Pull

  • Developer: Nicole C. Engard, 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. Engard, 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 and Statistics

Top 10 Titles Placed on Hold in the Last 6 Months

  • Developer: Nicole C. Engard, 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. Engard, 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. Engard, 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: Nicole C. Engard, ByWater Solutions
  • Module: Holds
  • Purpose: Titles with more than the entered number of holds on them
  • Status: Complete
SELECT concat(b.title, ' ', ExtractValue(m.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, 
       count(h.reservedate) AS 'holds' 
FROM biblio b 
LEFT JOIN biblioitems m USING (biblionumber) 
LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber) 
GROUP BY b.biblionumber 
HAVING count(h.reservedate) >= <<Min number of holds>>

Holds Gone Wild

Anomalous holds go here

Overdues With Holds Waiting

  • Developer: Nicole C. Engard, 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
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

Cancelled Holds

  • Developer: Nicole C. Engard, 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
  hold_fill_targets.source_branchcode LIKE <<Select your branch|ZBRAN>> AND
  authorised_values.category = "ccode" AND
  IF(reserves.itemnumber IS NULL, hold_fill_targets.itemnumber,
  reserves.itemnumber) = hold_fill_targets.itemnumber
GROUP BY
  CALL_NUMBER,
  hold_fill_targets.source_branchcode,
  items.holdingbranch,
  biblio.author,
  biblio.title,
  items.barcode
Personal tools