SQL Reports Library

From Koha Wiki

Jump to: navigation, search
Home > Documentation
Home > Documentation > Tips & Tricks
Home > Koha Versions > 3.10
Home > Koha Versions > 3.12
Home > Koha Versions > 3.14

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Contents

SQL Reports

Tips

Documentation on SQL tables and fields

Here the Koha DB schema

The wiki page on DB Schema

A link to more specific schemas (Acquisitions only now)

Links

If you want to put links to your report, you can use the SQL's CONCAT keyword in your SELECT clause.

for example, the following SQL Report will list all your biblio with a link to each of them.

SELECT
  biblionumber, 
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',title,'</a>') AS Title 
FROM biblio 
ORDER BY biblionumber

Query MARC

MySQL has some XML handling functions (since MySQL 5.1.5): http://dev.mysql.com/doc/refman/5.6/en/xml-functions.html

For example:

 
SELECT 
  ExtractValue((
    SELECT marcxml
    FROM biblioitems
    WHERE
      biblionumber=14),
      '//datafield[@tag="952"]/subfield[@code>="a"]') AS ITEM;

or the equivalent

SELECT
  ExtractValue(marcxml,'//datafield[@tag="952"]/*') AS ITEM
FROM biblioitems
WHERE biblionumber=14;

return the entire 952 data for all 952 fields for biblionumber 14 (without delimiting).

 
SELECT 
  ExtractValue((
    SELECT marcxml 
    FROM biblioitems 
    WHERE biblionumber=14),
      '//datafield[@tag="260"]/subfield[@code="b"]') AS PUBLISHER;

returns the 260$b data for biblionumber 14.

 
SELECT biblionumber, ExtractValue(marcxml, 'count(//datafield[@tag="505"])') AS count505 
FROM biblioitems
HAVING count505 > 1;

will give a list of biblionumbers along with the number of 505 fields (only when there is more than one 505 field)

Runtime Parameters

If you feel that your report might be too resource intensive you might want to consider using runtime parameters to your query. Runtime parameters basically make a filter appear before the report is run to save your system resources.

There is a specific syntax that Koha will understand as 'ask for values when running the report'. The syntax is <<Question to ask|authorized_value>>.

  • The << and >> are just delimiters. You must put << at the beginning and >> at the end of your parameter
  • The 'Question to ask' will be displayed on the left of the string to enter.
  • The authorized_value can be omitted if not applicable. If it contains an authorized value category, or the exact word branches (for libraries), itemtypes (for item types), date (for a date pop up) or categorycode (for patron category), a list with the Koha authorized values or the branches, item types, or patron categories will be displayed instead of a free field Note that you can have more than one parameter in a given SQL Note that entering nothing at run time won't probably work as you expect. It will be considered as "value empty" not as "ignore this parameter". For example entering nothing for : "title=<<Enter title>>" will display results with title='' (no title). If you want to have to have something not mandatory, use "title like <<Enter title>>" and enter a % at run time instead of nothing

Examples:

SELECT surname,firstname 
FROM borrowers 
WHERE branchcode=<<Enter patrons library|branches>> AND surname LIKE <<Enter filter FOR patron surname (% IF none)>>


SELECT * 
FROM items 
WHERE homebranch = <<Pick your branch|branches>> AND barcode LIKE <<Partial barcode value here>>

Tip:

You have to put "%" in a text box to 'leave it blank'. Otherwise, it literally looks for "" (empty string) as the value for the field.

SQL Report Summary Template

Report Title

  • Developer: Name of SQL query developer
  • Module: Main module queried by SQL
  • Purpose: Purpose of the SQL query
  • Status: Completed / In progress
Some SQL code


Holds

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)


List of Patrons with Holds Awaiting Pickup

  • Developer: Bev Church
  • Module: Holds
  • Purpose: List of all patrons at branch with holds awaiting pickup. So list can be exported from system and merged with a word processing notification document
  • Status: Complete
SELECT
  borrowers.surname,
  borrowers.firstname, 
  borrowers.address, 
  borrowers.city, 
  borrowers.zipcode, 
  reserves.waitingdate AS 'hold date',
  items.barcode,
  biblio.title
FROM
  reserves,
  borrowers,
  items,
  biblio 
WHERE
  reserves.borrowernumber = borrowers.borrowernumber
AND
  reserves.itemnumber = items.itemnumber
AND
  items.biblionumber = biblio.biblionumber
AND
  priority = 0
AND
  waitingdate IS NOT NULL
AND
  reserves.branchcode = 'XXXXX'


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>>

Count of hold 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 Patrons from a Single Branch with open Hold Requests

  • Developer: Jane Wagner, PTFS
  • Module: Holds
  • Purpose: Monthly holds placed by branch (counts holds placed in that month that have not been filled)
  • Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, reserves.reservedate 
AS 'date reserved', reserves.priority, biblio.title, 
IF( LOCATE('<datafield tag="020"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="020"', biblioitems.marcxml)), '', 
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="020"', biblioitems.marcxml)) + 19, 
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) + 19))) 
AS ISBN FROM reserves, borrowers, biblio, biblioitems WHERE reserves.borrowernumber = borrowers.borrowernumber 
AND reserves.biblionumber = biblio.biblionumber AND reserves.biblionumber = biblioitems.biblionumber 
AND reserves.branchcode = <<Branch|branches>> AND reserves.priority = 0

List of all items currently on loan to another library

  • Developer: Nora Blake and Bev Church
  • Module: Holds
  • Purpose: List of all items currently on loan to 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.homebranch = <<Owning branch|branches>>
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 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 holds placed by branch

  • Developer: Jane Wagner, PTFS
  • 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
 reservedate >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND
 reservedate <= LAST_DAY(now() - interval 1 month)
GROUP BY branchcode

Monthly holds placed and filled by branch

  • Developer: Jane Wagner, PTFS
  • Module: Holds
  • Purpose: Monthly holds placed and filled by branch (counts holds both placed and filled in that month)
  • Status: Complete
SELECT count(*), branchcode
FROM old_reserves
WHERE
 (timestamp LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%')))
AND
  (reservedate >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND
 reservedate <= LAST_DAY(now() - interval 1 month))
AND Found = 'F'
GROUP BY branchcode

Monthly holds filled by branch

  • Developer: Jane Wagner, PTFS
  • 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
 (timestamp LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%')))
AND
 Found = 'F'
GROUP BY branchcode


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

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


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

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


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>>


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


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

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>>


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

Patron Reports

New Patron List (previous month)

  • Developer: Jane Wagner, PTFS
  • Module: circ
  • Purpose:
  • Status: Complete


SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.dateenrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
ORDER BY borrowers.surname ASC

Patron Birthday Report

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: patrons
  • Purpose: Patrons who are under the age of 17 that have a birthday this month
  • Status: Complete


SELECT firstname, surname, address, address2, city, 
       zipcode, dateofbirth 
FROM borrowers 
WHERE MONTH(dateofbirth) = <<Month (mm)>> 
      AND DATEDIFF(<<Last date of month (yyyy-mm-dd)|date>>, dateofbirth) < ((17*365)+4)


Patrons of specific age range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: patrons
  • Purpose: This report shows patrons between the age of 12 and 13
  • Status: Complete
SELECT cardnumber, firstname, surname, dateofbirth, 
      (YEAR(CURDATE( )) - YEAR(dateofbirth) - IF(RIGHT(CURDATE( ),5) < RIGHT(dateofbirth,5),1,0)) AS 'age in years', 
      categorycode 
FROM borrowers 
WHERE DATEDIFF(now(), dateofbirth) < ((13*365)+4) 
      AND DATEDIFF(now(), dateofbirth) > ((12*365)+4)

Patrons with Staff Permissions

  • Developer: Ian Walls, ByWater Solutions
  • Module: Patrons
  • Purpose: List of patrons/staff with their permission levels
  • Status: Complete
SELECT borrowernumber, firstname, surname, categorycode, 
       description, flags 
FROM borrowers 
JOIN user_permissions USING (borrowernumber) 
JOIN permissions USING (code) 
UNION (
      SELECT borrowernumber, firstname, surname, 
             categorycode, 'module-level permissions; 
             1 is superlibrarian' AS description, flags 
      FROM borrowers 
      WHERE flags > 0) 
ORDER BY borrowernumber ASC

Superlibrarians

  • Developer: Nicole C. Engard, Joy Nelson and Elliott Davis, ByWater Solutions
  • Module: Patrons
  • Purpose: List of patrons/staff with superlibrarian permission
  • Status: Complete
SELECT borrowernumber, firstname, surname, categorycode, flags 
FROM borrowers 
WHERE flags%2=1 
ORDER BY borrowernumber ASC

Patrons with staff permission, and if they are superlibrarians

  • Developer: Tomás Cohen Arazi, Universidad Nacional de Córdoba
  • Module: Patrons
  • Purpose: Patrons with staff permission, and if the are superlibrarians
  • Status: Complete
SELECT
  surname AS 'Surname',
  firstname AS 'Firstname',
  Cardnumber,
  categories.description AS 'Patron type',
  Superlibrarian
FROM (
  SELECT
    surname, firstname,
    CONCAT('<a href="http://',(SELECT value FROM systempreferences WHERE variable='staffClientBaseURL'),
    '/cgi-bin/koha/members/moremember.pl?borrowernumber=',bn.borrowernumber,'">',cardnumber,'</a>') AS 'Cardnumber',
    CASE WHEN flags%2=1 THEN 'yes' WHEN flags%2=0 THEN 'no' END AS Superlibrarian, categorycode
  FROM (
    SELECT borrowernumber
    FROM borrowers
    WHERE flags > 0
    UNION
    SELECT DISTINCT borrowernumber
    FROM user_permissions) bn 
    LEFT JOIN borrowers ON (borrowers.borrowernumber=bn.borrowernumber)
) a
LEFT JOIN categories
ON (a.categorycode=categories.categorycode)
ORDER BY surname ASC

New Patrons by Category at Branch

  • Developer: Sharon Moreland
  • Module: Circulation
  • Purpose: New patrons added
  • Status: Complete


  SELECT branchcode,categorycode,COUNT(*)
  FROM borrowers WHERE MONTH(dateenrolled) = <<Month enrolled (mm)>> AND YEAR(dateenrolled)= <<Year enrolled (yyyy)>>
  GROUP BY branchcode,categorycode 
  ORDER BY branchcode


New Patrons by Category in Date Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: New patrons added by category in a date range (even if they have been deleted since)
  • Status: Complete


SELECT categorycode, COUNT(borrowernumber) AS 'new patrons'
FROM (SELECT borrowernumber, categorycode, dateenrolled FROM borrowers 
UNION ALL
SELECT borrowernumber, categorycode, dateenrolled FROM deletedborrowers) AS patrons 
WHERE dateenrolled BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>  
GROUP BY categorycode

Expired Patrons w/out Checkouts

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: List patrons expired in a specific year who do not currently have any checkouts
  • Status: Complete
  SELECT surname, firstname, borrowernumber 
  FROM borrowers 
  WHERE borrowernumber 
  NOT IN (SELECT borrowernumber FROM issues) 
  AND YEAR(dateexpiry) = <<Year>>

Missing Emails

  • Developer: Sharon Moreland
  • Module: Patrons
  • Purpose: Missing e-mails
  • Status: Complete


 SELECT cardnumber, surname, firstname, branchcode, debarred, dateexpiry 
  FROM borrowers 
  WHERE ' ' IN (email)

Patrons w/ Checked Out Items

  • Developer: Nora Blake
  • Module: Circulation
  • Purpose: List of items checked out to patrons
  • Status: Complete


  SELECT issues, biblio.title, author, surname, firstname, borrowers.sort1, 
         items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate 
  FROM issues 
  LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber 
  LEFT JOIN items ON issues.itemnumber=items.itemnumber 
  LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
  WHERE issues.branchcode=<<Checked out at|branches>>
  ORDER BY issues.branchcode, borrowers.sort1, borrowers.surname, issues.issuedate, biblio.title

New Patron Count (previous month)

  • Developer: Jane Wagner, PTFS
  • Module: Patron
  • Purpose:
  • Status: Complete


SELECT COUNT(*) AS 'New Patrons Last Month' 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month)

New Patron Count (by Branch/Category) (previous month)

  • Developer: Jane Wagner, PTFS
  • Module: Patron
  • Purpose: Count of new patrons enrolled in the previous month, by branch and category code
  • Status: Complete


SELECT branchcode, categorycode, COUNT(branchcode) AS NumberEnrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
GROUP BY branchcode, categorycode

New Patrons by Branch (year to date)

  • Developer: Jane Wagner, PTFS
  • Module: Patron
  • Purpose:
  • Status: Complete


SELECT branchcode, categorycode, COUNT(branchcode) AS NumberEnrolled 
FROM borrowers 
WHERE YEAR(borrowers.dateenrolled) = YEAR(NOW()) 
GROUP BY branchcode, categorycode


Count of New Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: Count of patrons added between two dates at a branch
  • Status: Complete
SELECT COUNT(borrowernumber) AS 'New Patrons' 
FROM borrowers 
WHERE dateenrolled BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND branchcode=<<Branch|branches>>

Count of Expired Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: Count of patrons who's cards have expired before today
  • Status: Complete
SELECT COUNT(cardnumber) AS count
FROM borrowers  
WHERE dateexpiry > <<Today's Date (yyyy-mm-dd)|date>>

Patrons with All Attribute Values

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: Patron list with the value of all of their custom patron attributes
  • Status: Complete
SELECT p.surname, p.firstname, p.cardnumber, a.code, a.attribute 
FROM borrowers p
LEFT JOIN borrower_attributes a USING (borrowernumber)
GROUP BY a.attribute 
ORDER BY p.surname, p.firstname ASC

Patrons with a Specific Attribute Value

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: Patron list with the value of one of their custom patron attributes (student id)
  • Status: Complete
SELECT borrowers.surname, borrowers.firstname, 
       borrowers.cardnumber, borrower_attributes.attribute AS 'Attribute' 
FROM borrowers 
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) 
LEFT JOIN borrower_attribute_types ON (borrower_attribute_types.code=borrower_attributes.code) 
WHERE borrower_attributes.code = <<Attribute Code>>

Duplicate Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: List of patrons who are potentially duplicates
  • Status: Complete
SELECT surname, firstname, GROUP_CONCAT(cardnumber SEPARATOR ', ') AS barcodes,
GROUP_CONCAT(borrowernumber SEPARATOR ', ') AS borrowers 
FROM borrowers 
GROUP BY CONCAT(trim(surname),"/",trim(firstname),"/") 
HAVING COUNT(CONCAT(trim(surname),"/",trim(firstname),"/"))>1

Restricted Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: List of patrons who have been marked as restricted
  • Status: Complete
SELECT cardnumber, surname, firstname,
       debarred, debarredcomment 
FROM borrowers 
WHERE branchcode=<<Select your branch|branches>> AND debarred IS NOT NULL
ORDER BY surname ASC, firstname ASC

Patrons with notes or messages

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: Patrons with notes and messages on their accounts
  • Status: Completed
SELECT b.cardnumber, b.surname, b.firstname,
b.opacnote, b.borrowernotes, group_concat(DISTINCT m.message separator ', ') AS circmesages
FROM borrowers b
LEFT JOIN messages m USING (borrowernumber)
WHERE b.branchcode=<<Branch|branches>> AND ((b.opacnote IS NOT
NULL AND b.opacnote != '') OR (b.borrowernotes IS NOT NULL AND
b.borrowernotes != '') OR (m.message IS NOT NULL AND
m.message != '')) GROUP BY b.borrowernumber ORDER BY b.surname ASC,
b.firstname ASC

Patrons with No Checkouts

  • Developer: Nicole C. Engard, ByWater Solutions, revised Nick Clemens, VOKAL Library System
  • Module: Patrons
  • Purpose: Patrons who haven't checked out in a specific timeframe
  • Status: Completed
SELECT surname,firstname,cardnumber
FROM borrowers b
LEFT OUTER JOIN (SELECT DISTINCT borrowernumber  FROM statistics WHERE datetime BETWEEN <<Date1|date>> AND <<Date 2|date>>  AND type="issue") foo ON b.borrowernumber=foo.borrowernumber
WHERE foo.borrowernumber IS NULL

Patron with messages but no email

  • Developer: Amy Boisvert, VOKAL
  • Module: Patrons
  • Purpose: Patrons with email addresses that do not have the patron messaging preference for holds checked.
  • Status: Completed
SELECT b.surname, b.firstname, b.cardnumber, b.email
FROM borrowers b
     LEFT JOIN (SELECT p.borrowernumber
                FROM borrower_message_preferences p 
                INNER JOIN borrower_message_transport_preferences t
                ON p.borrower_message_preference_id=t.borrower_message_preference_id
                WHERE p.message_attribute_id=4) e 
     ON b.borrowernumber=e.borrowernumber
WHERE b.branchcode=<<Your branch|branches>>
AND IFNULL(b.email,'') <>'' AND e.borrowernumber IS NULL

Count Active Patrons

  • Developer: Mike Hafen
  • Module: Patrons
  • Purpose: A report for finding patrons who are checking out materials
  • Status: Completed
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber)
FROM (
  SELECT issuedate, borrowernumber FROM old_issues
 UNION ALL
  SELECT issuedate, borrowernumber FROM issues
) AS all_issues
LEFT JOIN borrowers USING (borrowernumber)
GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode

Count Active Patrons by Category for a Specific Month

  • Developer: Jesse Weaver
  • Module: Statistical (Circulation, Reports)
  • Purpose: Count Active Patrons by Category for a Specific Month
  • Status: Complete
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) 
FROM old_issues
  LEFT JOIN borrowers USING (borrowernumber) 
GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode

List Active Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Circulation, Reports)
  • Purpose: List Active Patrons since a specific date
  • Status: Complete
SELECT DISTINCT surname, firstname, cardnumber, email, address, 
                address2, city, state, zipcode 
FROM borrowers
WHERE borrowernumber IN 
      (SELECT borrowernumber 
       FROM statistics 
       WHERE borrowernumber = borrowernumber 
             AND datetime >= <<Has activity since (YYYY-MM-dd)|date>>) 
ORDER BY surname, firstname

Patron search by sort1

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: A search of patrons using the sort1 field that show checkouts and overdues
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',p.borrowernumber,'\">',p.surname, ', ', p.firstname,'</a>') AS patron, p.cardnumber, 
          REPLACE((SELECT count(c.itemnumber) FROM issues c WHERE p.borrowernumber=c.borrowernumber AND c.date_due >= now()),'0','') AS checkouts, 
          REPLACE(CONCAT('<div style=\"color:#f11\">',(SELECT count(i.itemnumber) FROM issues i WHERE p.borrowernumber=i.borrowernumber AND i.date_due < now()),'</div>'),'0','') AS overdues 
FROM borrowers p 
WHERE p.sort1=<<Sort 1 value>> 
ORDER BY p.surname, p.firstname


Expired Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: A list of expired patrons with the money they owe and their guarantor information
  • Status: Complete
SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname, 
       p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, ' (', g.cardnumber, ')'),'') AS guarantor, 
       p.relationship, ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
WHERE p.dateexpiry < NOW() 
GROUP BY p.borrowernumber
ORDER BY p.dateexpiry ASC


Guarantor List

  • Developer: Bernardo Gonzalez Kriegel
  • Module: Patrons
  • Purpose: A list of guarantors and guarantees
  • Status: Complete
SELECT 
  IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'') AS guarantor,
  IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'') AS guarantee
FROM
(  
  SELECT * 
  FROM borrowers
  WHERE guarantorid  IS NOT NULL
) AS p
LEFT JOIN borrowers AS g 
  ON p.guarantorid  = g.borrowernumber
ORDER BY g.borrowernumber

Patron Permissions

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

Permissions Check

  • Developer: Christopher Brannon
  • Module: Patrons
  • Purpose: Search for ANY patrons that have some dangerous flags set. Modify the WHERE statement to watch for flags you want to keep an eye on.
  • Status: Complete
SELECT surname AS 'Last Name',firstname AS 'First Name',cardnumber AS 'Card Number',categorycode,userid AS 'UserID',branchcode AS 'Library',Super AS 'superlibrarian',A AS 'circulate',SubA AS 'circulate subs',B AS 'catalogue',C AS 'parameters',SubC AS 'parameters subs',D AS 'borrowers',E AS 'permissions',F AS 'reserveforothers',SubF AS 'reserveforothers subs',G AS 'borrow',I AS 'editcatalogue',SubI AS 'editcatalogue subs',J AS 'updatecharges',K AS 'acquisition',SubK AS 'acquisition subs',L AS 'management',M AS 'tools',SubM1 AS 'tools subs (Pt 1)',SubM2 AS 'tools subs (Pt 2)',N AS 'editauthorities',O AS 'serials',SubO AS 'serials subs',P AS 'reports',SubP AS 'reports subs',Q AS 'staffaccess'
FROM (SELECT b.surname,b.firstname,b.cardnumber,b.userid,b.branchcode,b.categorycode,@CHECK:=b.flags AS 'CheckQ',IF(@Check-131072>=0,@Q:="On",@Q:="Off") AS 'Q',
IF(@Check-131072>=0,@CHECK:=@Check-131072,@CHECK) AS 'CheckP',IF(@Check-65536>=0,@P:="On",@P:="Off") AS 'P',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='16' AND borrowernumber=b.borrowernumber) AS SubP,
IF(@Check-65536>=0,@CHECK:=@Check-65536,@CHECK) AS 'CheckO',IF(@Check-32768>=0,@O:="On",@O:="Off") AS 'O',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='15' AND borrowernumber=b.borrowernumber) AS SubO,
IF(@Check-32768>=0,@CHECK:=@Check-32768,@CHECK) AS 'CheckN',IF(@Check-16384>=0,@N:="On",@N:="Off") AS 'N',
IF(@Check-16384>=0,@CHECK:=@Check-16384,@CHECK) AS 'CheckM',IF(@Check-8192>=0,@M:="On",@M:="Off") AS 'M',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN "a%" AND "i%") AS SubM1,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN "j%" AND "z%") AS SubM2,
IF(@Check-8192>=0,@CHECK:=@Check-8192,@CHECK) AS 'CheckL',IF(@Check-4096>=0,@L:="On",@L:="Off") AS 'L',
IF(@Check-4096>=0,@CHECK:=@Check-4096,@CHECK) AS 'CheckK',IF(@Check-2048>=0,@K:="On",@K:="Off") AS 'K',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='11' AND borrowernumber=b.borrowernumber) AS SubK,
IF(@Check-2048>=0,@CHECK:=@Check-2048,@CHECK) AS 'CheckJ',IF(@Check-1024>=0,@J:="On",@J:="Off") AS 'J',
IF(@Check-1024>=0,@CHECK:=@Check-1024,@CHECK) AS 'CheckI',IF(@Check-512>=0,@I:="On",@I:="Off") AS 'I',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='9' AND borrowernumber=b.borrowernumber) AS SubI,
IF(@Check-512>=0,@CHECK:=@Check-512,@CHECK) AS 'CheckH',IF(@Check-256>=0,@H:="On",@H:="Off") AS 'H',
IF(@Check-256>=0,@CHECK:=@Check-256,@CHECK) AS 'CheckG',IF(@Check-128>=0,@G:="On",@G:="Off") AS 'G',
IF(@Check-128>=0,@CHECK:=@Check-128,@CHECK) AS 'CheckF',IF(@Check-64>=0,@F:="On",@F:="Off") AS 'F',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='6' AND borrowernumber=b.borrowernumber) AS SubF,
IF(@Check-64>=0,@CHECK:=@Check-64,@CHECK) AS 'CheckE',IF(@Check-32>=0,@E:="On",@E:="Off") AS 'E',
IF(@Check-32>=0,@CHECK:=@Check-32,@CHECK) AS 'CheckD',IF(@Check-16>=0,@D:="On",@D:="Off") AS 'D',
IF(@Check-16>=0,@CHECK:=@Check-16,@CHECK) AS 'CheckC',IF(@Check-8>=0,@C:="On",@C:="Off") AS 'C',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='3' AND borrowernumber=b.borrowernumber) AS SubC,
IF(@Check-8>=0,@CHECK:=@Check-8,@CHECK) AS 'CheckB',IF(@Check-4>=0,@B:="On",@B:="Off") AS 'B',
IF(@Check-4>=0,@CHECK:=@Check-4,@CHECK) AS 'CheckA',IF(@Check-2>=0,@A:="On",@A:="Off") AS 'A',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='1' AND borrowernumber=b.borrowernumber) AS SubA,
IF(@Check-2>=0,@CHECK:=@Check-2,@CHECK) AS 'CheckSuper',IF(@Check>0,"On","Off") AS "Super"
FROM borrowers b
LEFT JOIN categories USING (categorycode)) AS MainFlags
WHERE MainFlags.Super="On" OR MainFlags.E="On" OR MainFlags.M="On" OR MainFlags.SubM1 LIKE "%delete_anonymize_patrons%" OR MainFlags.SubM1 LIKE "%edit_calendar%" OR MainFlags.SubM1 LIKE "%edit_news%" OR MainFlags.SubM1 LIKE "%edit_notice_status_triggers%" OR MainFlags.SubM1 LIKE "%edit_notices%" OR MainFlags.SubM1 LIKE "%edit_news%" OR MainFlags.SubM1 LIKE "%items_batchdel%" OR MainFlags.SubM1 LIKE "%schedule_tasks%" OR MainFlags.SubM1 LIKE "%view_system_logs%" OR MainFlags.SubP LIKE "%create_reports%"
ORDER BY surname, firstname ASC

Patron without image

  • Developer: Md. Zahid Hossain Shoeb, Independent University, Bangladesh (IUB)
  • Module: Patrons
  • Purpose: To list the patrons whose images have not been uploaded.
  • Status: Complete
SELECT cardnumber, borrowernumber, surname, firstname FROM borrowers 
WHERE borrowernumber
NOT IN (SELECT borrowernumber FROM patronimage)

Missing or invalid email format

  • Developer: Md. Zahid Hossain Shoeb, Independent University, Bangladesh (IUB)
  • Module: Patrons
  • Purpose: To list the patrons missing or invalid email format.
  • Status: Complete
SELECT surname AS Surname, firstname AS "First Name", cardnumber AS "Card Number", email AS Email
FROM borrowers
WHERE email NOT LIKE '%_@__%.__%'
ORDER BY email DESC

Ex Heavy Borrowers

  • Developer: Chris Hall for Horowhenua Library Trust, Catalyst
  • Module: Borrowers
  • Purpose: To show which borrowers, given a certain date range, have transformed from heavy borrowers to light borrowers. The report takes two ranges of dates, calculates the number of issues in each, and according to the thresholds set in the report, shows us borrowers who used to borrow a lot, but now don't borrow as much.
  • Status: Completed
SELECT first.borrowernumber, first.firstname, first.surname, first.cardnumber, issues_before, issues_after FROM(
SELECT borrowernumber, firstname, surname, cardnumber, count(old_issues.itemnumber) AS issues_before
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of first range (yyyy-mm-dd)|date>>
AND issuedate < <<End of first range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS first
JOIN (
SELECT borrowernumber, count(old_issues.itemnumber) AS issues_after
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of second range (yyyy-mm-dd)|date>>
AND issuedate < <<End of second range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS second
WHERE first.borrowernumber = second.borrowernumber
AND issues_after < 20 AND issues_before > 60 -- borrowers borrowed fewer than 20 items in the second range, and more than 60 in the first range.

Circulation Reports

Date Wise List of Checked In Books

  • Developer: Vimal Kumar V., Mahatma Gandhi University Library
  • Module: Circulation
  • Purpose: To generate date wise list of checked in books
  • Example: List of checked in books between 2013-07-20 to 2013-07-25
  • Status: Complete
SELECT old_issues.returndate,items.barcode,biblio.title,biblio.author,borrowers.firstname,borrowers.surname,borrowers.cardnumber,borrowers.categorycode 
FROM old_issues  LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber 
LEFT JOIN items ON old_issues.itemnumber=items.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE old_issues.returndate 
BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>  ORDER BY old_issues.returndate DESC

Date Wise List of Checked Out Books

  • Developer: Arslan Farooq, SZABIST Islamabad Library
  • Module: Circulation
  • Purpose: To generate date wise list of checked out books (based on Nicole's "All Checked Out Books" report)
  • Status: Complete
SELECT DATE_FORMAT(c.issuedate, "%d %b %Y %h:%i %p") AS Issue_Date, DATE_FORMAT(c.date_due, "%d %b %Y") AS Due_Date, 
i.barcode AS Barcode,
b.title AS Title,
b.author AS Author, 
p.cardnumber AS Card_No,
p.firstname AS First_Name, 
p.surname AS Last_Name 
FROM issues c 
LEFT JOIN items i ON (c.itemnumber=i.itemnumber) 
LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE c.issuedate 
BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>  ORDER BY c.issuedate DESC

Circulation numbers in a time frame for all itemtypes, per branch

  • Developer: Liz Rea, Catalyst IT, and David Cook, Prosentient, for Horowhenua Library Trust
  • Module: Circulation
  • Purpose: Generate a statistical report for all itemtypes, per branch
  • Example:
  • Status: Complete
SELECT itemtypes.itemtype,branches.branchname,IFNULL(TotalIssues,'0') AS 'Issues/Renews' FROM itemtypes
JOIN branches
LEFT JOIN
(SELECT t.branchcode, coalesce(statistics.count,0) AS TotalIssues,statistics.itemtype FROM branches t
LEFT JOIN (SELECT s.branch, count(s.datetime) AS count,itemtype FROM statistics s WHERE s.type IN ('issue','renew') AND s.datetime BETWEEN <<between (date)|date>> AND <<and|date>>
GROUP BY s.branch,s.itemtype)statistics ON t.branchcode = statistics.branch)issuestats ON issuestats.itemtype = itemtypes.itemtype AND issuestats.branchcode = branches.branchcode

Circulation of Two Call Numbers

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Circulation using two call numbers (or call number ranges) for a specific time frame
  • Example: Using YA and J as the call numbers gets you all Juvenile materials if your library uses Dewey because the wildcard is being added in by default
  • Status: Complete
SELECT s.branch, month(s.datetime) AS month, 
       year(s.datetime) AS year, count(s.datetime) AS count 
FROM statistics s
LEFT JOIN items i USING (itemnumber)
WHERE s.type LIKE 'issue' 
       AND (i.itemcallnumber LIKE concat(<<Call number like>>, '%') OR i.itemcallnumber LIKE concat(<<Second call number like>>, '%') 
       AND date(s.datetime) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY s.branch, year, month 
ORDER BY year, month DESC, s.branch ASC

Track In House Use

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Find out local use circ numbers for a specific time frame
  • Status: Complete
SELECT count(*) 
FROM statistics 
WHERE type='localuse' 
AND datetime BETWEEN <<Local USE BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

Track In House Use Hourly

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Number of internal circs on a particular day in a particular time range
  • Status: Complete
SELECT hour(datetime) AS hour, count(*) AS count 
FROM statistics 
WHERE type='localuse' AND date(datetime)=<<Date (yyyy-mm-dd)|date>> 
      AND time(datetime) BETWEEN <<Time BETWEEN (hh:mm)>> 
      AND <<and (hh:mm)>>


Track In House Use in Hourly Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Number of internal circs broken out by hour on a particular day
  • Status: Complete
SELECT hour(datetime) AS hour, count(*) AS count 
FROM statistics 
WHERE type='localuse' AND date(datetime)=<<Date (yyyy-mm-dd)|date>>

Track In House Use for a Title

  • Developer: Denise Hannibal
  • Module: Circulation
  • Purpose: Number of in house uses for a barcode (track Reference Collection use)
  • Status: Complete
SELECT i.location, i.barcode, i.itemcallnumber, b.title, count(s.datetime) AS count 
FROM items i LEFT JOIN statistics s USING (itemnumber) LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE s.type='localuse' AND s.branch=<<Enter the branch>>  AND i.barcode=<<Enter the barcode TO CHECK IN house use>>
GROUP BY i.itemnumber

Checkouts by Hour in selected date range

  • Developer: Agnes Rivers-Moore
  • Module: Circ
  • Purpose: Statistical report counting items checked out by hour in specified period.
  • Status: Complete
  • Note: Identifies busy or quiet times of day. Choose a short start and end period to test.
SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour, 
count(*) AS Checkouts  
FROM statistics  
WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09 16:59:59' 
AND statistics.type = 'issue'
GROUP BY Year, Month, Day, Hour


All Checked Out Books

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A report to show you all items that are currently checked out and who they're checked out to
  • Status: Complete
SELECT c.date_due, p.surname, p.firstname,
       p.phone, p.email, b.title, b.author,
       i.itemcallnumber, i.barcode, i.location 
FROM issues c
LEFT JOIN items i ON (c.itemnumber=i.itemnumber) 
LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
ORDER BY c.date_due ASC

Overdues w/ Contact Info

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A report that shows items overdue more than a specific number of days for contacting the patrons.
  • Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, 
       p.phone, p.email, c.date_due, 
       (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', 
       b.title, b.author, i.itemcallnumber, 
       i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) 
LEFT JOIN items i ON (c.itemnumber=i.itemnumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>>
ORDER BY p.surname ASC, p.firstname ASC, c.date_due ASC


Overdues Call List w/ Guarantor

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A report of patrons to call with overdues. Will show guarantor for the young patrons.
  • Status: Complete
SELECT p.surname, p.firstname, p.phone, p.phonepro AS 'second phone', b.title, b.author, i.barcode, c.date_due, i.replacementprice, p.categorycode, IFNULL(concat(g.surname, ', ', g.firstname, ' (', g.cardnumber, ')'),'') AS guarantor, ifnull(p.relationship,'') AS relationship
FROM issues c
LEFT JOIN items i USING (itemnumber)
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN borrowers p USING (borrowernumber)
LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
WHERE c.date_due < NOW() 
GROUP BY p.borrowernumber
ORDER BY p.cardnumber ASC

Overdues by Homeroom/Attribute

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A report that shows overdues alongside patron attributes (in this case GRADELVL and HOMEROOM)
  • Status: Complete
SELECT p.cardnumber, p.surname, p.firstname,
c.date_due, g.attribute AS grade, h.attribute AS teacher,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
b.title, b.author, i.itemcallnumber,
i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
LEFT JOIN borrower_attributes g ON (p.borrowernumber=g.borrowernumber)
LEFT JOIN borrower_attributes h ON (p.borrowernumber=h.borrowernumber)
WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>> AND g.code="GRADELVL" AND h.code="HOMEROOM"
ORDER BY g.attribute, h.attribute, p.surname ASC

Overdues w/item info when patron has no email

  • Developer: Doug Kingston
  • Module: Circulation
  • Purpose: A report that shows items overdue more than a specific number of days for patrons with no email address.
  • Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, 
       p.phone, p.email, c.date_due, 
       (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', 
       b.title, b.author, i.itemcallnumber, 
       i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) 
LEFT JOIN items i ON (c.itemnumber=i.itemnumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>> 
AND p.email = ""
ORDER BY p.surname ASC, p.firstname ASC, c.date_due ASC

Overdues by Item Type

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A report that shows overdue items based on item type.
  • 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', 
       b.title, b.author, i.itemcallnumber, 
       i.barcode
FROM borrowers p
LEFT JOIN issues c USING (borrowernumber) 
LEFT JOIN items i USING (itemnumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE i.itype = <<Item Type|itemtypes>>
ORDER BY p.surname ASC, c.date_due ASC

Checkouts by Call Number (previous month)

  • Developer: Jane Wagner, PTFS
  • Module: Circ
  • Purpose:
  • Status: Complete


SELECT count(statistics.type) AS 'Checkouts',items.itemcallnumber 
FROM borrowers 
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) 
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) 
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) 
WHERE statistics.type = 'issue' 
  AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') 
  AND statistics.datetime <= LAST_DAY(now() - interval 1 month) 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber ASC

Renewals by Call Number (previous month)

  • Developer: Jane Wagner, PTFS
  • Module: Circ
  • Purpose:
  • Status: Complete


SELECT count(statistics.type) AS 'Renewals',items.itemcallnumber 
FROM borrowers 
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) 
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) 
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) 
WHERE statistics.type = 'renew'
  AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
  AND statistics.datetime <= LAST_DAY(now() - interval 1 month) 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber ASC


Checkouts by Item Type (previous month)

  • Developer: Galen Charlton, Equinox
  • Module: Circ
  • Purpose:
  • Status: Complete
  • Note: This can take a while to run because of the union of items and deleteditems, but has the advantage that items that get circulated, then deleted, during the previous month will get reported using their correct item type.
SELECT  all_items.itype AS "Item Type" ,count(*) AS 'Checkouts' 
FROM statistics 
JOIN (
  SELECT itemnumber, itype FROM deleteditems
  UNION
  SELECT itemnumber, itype FROM items 
) AS all_items USING (itemnumber)
WHERE statistics.type = 'issue' 
AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') 
AND statistics.datetime <= LAST_DAY(now() - interval 1 month)
GROUP BY all_items.itype 
ORDER BY all_items.itype ASC;

Previous Day's Circ Stats

  • Developer: Jane Wagner, PTFS
  • Module: Circ
  • Purpose:
  • Status: Complete


SELECT count(statistics.type) AS 'Total', statistics.type 
FROM statistics WHERE statistics.datetime LIKE concat(date_format(LAST_DAY(now() - interval 1 day),'%Y-%m-%')) 
GROUP BY statistics.type 
ORDER BY statistics.type ASC

Previous Month's Circ Stats

  • Developer: Jane Wagner, PTFS
  • Module: Circ
  • Purpose:
  • Status: Complete


SELECT count(statistics.type) AS 'Total', statistics.type 
FROM statistics 
WHERE statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND statistics.datetime <= LAST_DAY(now() - interval 1 month) 
GROUP BY statistics.type 
ORDER BY statistics.type ASC

Previous Month's Checkouts/Renewals by Collection Code

  • Developer: Jane Wagner, PTFS
  • Module: Circ
  • Purpose:
  • Status: Complete
SELECT items.ccode AS Collection, COUNT( statistics.itemnumber ) AS Count 
FROM statistics 
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) 
WHERE  (statistics.datetime LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))) AND statistics.type IN ('issue','renew') 
GROUP BY items.ccode

Previous Month Checkouts/Renews by Patron Category

  • Developer: Jane Wagner, PTFS
  • Module: Circ
  • Purpose:
  • Status: Complete
SELECT borrowers.categorycode AS PatronType, COUNT( statistics.itemnumber ) AS Count 
FROM statistics 
  LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) 
WHERE (statistics.datetime LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))) 
  AND statistics.type IN ('issue','renew') 
GROUP BY borrowers.categorycode

All Circ Actions on Date

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circ
  • Purpose: Give you stats for all circ actions on a specific date
  • Status: Complete
SELECT type AS action, COUNT(datetime) AS count 
FROM statistics 
WHERE DATE(datetime)=<<Date (yyyy-mm-dd)|date>>
GROUP BY type;

All Circ Actions on Patron categories and Date

  • Developer: Bengal Library Association, Kolkata
  • Module: Circ
  • Purpose: All circ actions on a specific Patron categories and Date
  • Status: Complete
SELECT count(statistics.type), statistics.type,statistics.datetime,borrowers.categorycode FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE borrowers.categorycode=<<categorycode|categorycode>> 
AND
 DATE(statistics.datetime)=<<Date (yyyy-mm-dd)|date>>
GROUP BY statistics.type

Checkouts & Renewals in Date Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circ
  • Purpose: Asks for date range and shows you the checkouts and renewals
  • Status: Complete
SELECT type, count(datetime) AS count 
FROM statistics 
WHERE datetime BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND type IN ('issue','renew') 
GROUP BY type

Weekly Checked Out by Branch

  • Developer: vishnuperumal
  • Module: Circulation
  • Purpose: number of checkouts detail made by branch(Weekly Report)
  • Status: Complete


SELECT borrowers.surname, borrowers.firstname, borrowers.phone, 
       borrowers.cardnumber, borrowers.address, borrowers.city, 
       borrowers.zipcode, issues.date_due, items.itype, items.itemcallnumber, 
       items.barcode, items.homebranch, biblio.title, biblio.author
FROM borrowers 
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (issues.issuedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() 
      AND issues.branchcode = <<Issuing branch|branches>>)
ORDER BY borrowers.surname ASC, issues.date_due ASC

Number of Checkouts by Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Statistical Count by month of number of checkouts made by each branch all in one report
  • Status: Complete


  SELECT branch, month(datetime) AS month, year(datetime) AS year, count(datetime) AS count 
  FROM statistics 
  WHERE type LIKE 'issue' 
  GROUP BY branch, year, month 
  ORDER BY year, month DESC, branch ASC


Not Circulating Items (Date Specific)

  • Developer: Bev Church, Joe Tholen
  • Module: Circulation
  • Purpose: List items not circulated in specific date range, by shelf location (weeding tool)
  • Status: Needs Work


  SELECT barcode, homebranch AS 'branch', itemcallnumber, title 
  FROM biblio, items 
  WHERE items.biblionumber = biblio.biblionumber AND homebranch = <<Home branch|branches>> AND location = <<Shelving location|LOC>> AND itemnumber NOT IN 
  (SELECT itemnumber FROM issues) UNION 
  (SELECT barcode, homebranch AS 'branch', itemcallnumber, title 
  FROM biblio, items 
  WHERE items.biblionumber = biblio.biblionumber AND homebranch = <<Home branch again|branches>> AND location = <<Shelving location again|LOC>> AND itemnumber NOT IN 
  (SELECT itemnumber FROM old_issues WHERE date(issuedate) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) ) 
  ORDER BY itemcallnumber, barcode

Non Circulating Items

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: List items that have never circulated
  • Status: Complete


SELECT b.title, b.author, i.barcode, i.itemcallnumber 
FROM items i
LEFT JOIN biblio b USING (biblionumber)
WHERE i.itemnumber NOT IN (SELECT itemnumber FROM issues WHERE itemnumber IS NOT NULL)
              AND i.itemnumber NOT IN (SELECT itemnumber FROM old_issues WHERE itemnumber IS NOT NULL)

Non Circulating Items in X Years

  • Developer: Marion J. Makarewicz and Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: List items that have not circulated in X Years
  • Status: Complete


SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber,
       i.barcode, i.datelastborrowed, i.issues AS totalcheckouts,
       i.dateaccessioned
FROM items i
LEFT JOIN issues
USING (itemnumber)
LEFT JOIN biblio b
USING (biblionumber)
WHERE i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) 
      AND  YEAR(NOW())-YEAR(i.datelastborrowed) > <<Years NOT circulated>>
UNION 
SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber,
       i.barcode, i.datelastborrowed, i.issues AS totalcheckouts,
       i.dateaccessioned
FROM items i
LEFT JOIN issues
USING (itemnumber)
LEFT JOIN biblio b
USING (biblionumber)
WHERE i.itemnumber NOT IN (SELECT old_issues.itemnumber FROM old_issues) 
      AND YEAR(NOW())-YEAR(i.datelastborrowed) > <<Years NOT circulated (again)>>

Patrons w/ Books Due Tomorrow

  • Developer: Nicole C. Engard, ByWater Solutions, Koha List
  • Module: Circulation
  • Purpose: List patrons with books due tommorrow
  • Status: Complete


SELECT p.cardnumber, p.surname, p.branchcode, p.firstname, 
       co.date_due, i.barcode, b.title, b.author
FROM borrowers p
LEFT JOIN issues co ON (co.borrowernumber=p.borrowernumber)
LEFT JOIN items i ON (co.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber)
WHERE DATE(co.date_due) = DATE_ADD(curdate(), INTERVAL 1 DAY)
      AND i.homebranch = <<Branch|branches>>
ORDER BY p.surname ASC

Patrons w/ Items Issued Today

  • Developer: MJ Ray, software.coop
  • Module: Circulation
  • Purpose: List patrons and the items issued to them, to aid mailing items out
  • Status: Development
  SELECT borrowers.surname,borrowers.firstname,borrowers.title,borrowers.othernames,borrowers.initials,borrowers.streetnumber,borrowers.streettype,borrowers.address,borrowers.address2,borrowers.city,borrowers.zipcode,borrowers.country,items.barcode,items.itemcallnumber,biblioitems.isbn,biblioitems.issn FROM borrowers
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)
  WHERE statistics.type='issue' AND statistics.datetime > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  ORDER BY items.barcode ASC

Count of Transfers by Other Branches

  • Developer: Joe Tholen
  • Module: Circulation
  • Purpose: Count total transfers from other branches, by branches, by month
  • Status: Completed


  SELECT frombranch, monthname(datesent) month,COUNT(*) 
  FROM branchtransfers 
  WHERE tobranch=<<Transferred TO|branches>> 
        AND datesent BETWEEN <<Sent BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
  GROUP BY month


List of Transfers to Other Branches

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: List transferred items sent from your branch to another in a date range
  • Status: Completed


SELECT t.datesent, t.frombranch, t.tobranch, b.title, 
       b.author, i.barcode, i.itemcallnumber  
FROM branchtransfers t 
LEFT JOIN items i USING (itemnumber) 
LEFT JOIN biblio b USING (biblionumber) 
WHERE frombranch=<<Transferred FROM|branches>> 
      AND t.datesent BETWEEN <<Sent BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY t.datesent

Transfers as Interlibrary Loans

  • Developer: Sharon Moreland
  • Module: Circulation
  • Purpose: Counts transfers of Library A's materials to a library that is not Library A
  • Status: This is done over the previous YEAR. Not for the current one. ILL Loans.


SELECT items.homebranch, COUNT(*) 
FROM branchtransfers 
LEFT JOIN items ON (branchtransfers.itemnumber=items.itemnumber) 
WHERE (items.homebranch != branchtransfers.tobranch) 
AND (branchtransfers.frombranch != branchtransfers.tobranch) AND YEAR(datesent)=YEAR(NOW())-1 
GROUP BY items.homebranch

Transfers as Interlibrary Borrows

  • Developer: Sharon Moreland
  • Module: Circulation
  • Purpose: Counts when materials that are not Library A's are transferred to Library A.
  • Status: This is done over the previous YEAR. Not for the current one. ILL Borrows.


SELECT branchtransfers.tobranch, COUNT(*) 
FROM branchtransfers 
LEFT JOIN items ON (branchtransfers.itemnumber=items.itemnumber) 
WHERE (branchtransfers.tobranch != items.homebranch) 
AND (branchtransfers.tobranch != branchtransfers.frombranch) AND YEAR(datesent)=YEAR(NOW())-1 
GROUP BY branchtransfers.tobranch

Materials Checked out to Other Libraries

  • Developer: Scotty Zollars
  • Module: Circulation
  • Purpose: List interlibrary loan materials check out to other libraries, by month
  • Status: Be warned this is done over the previous YEAR. Not for the current one. ILL record keeping


SELECT  monthname(datesent) month,COUNT(*) 
  FROM branchtransfers WHERE frombranch=<<Transferred FROM|branches>> AND YEAR(datesent)=YEAR(NOW())-1 
  GROUP BY month ORDER BY month(datesent)


List that totals the circulation of each Dewey section, F, and periodicals, by month

  • Developer: Joe Atzberger
  • Module: Statistical (Circulation, Reports)
  • Purpose: List that totals the circulation of each Dewey section, F, and periodicals, by month
  • Status: Complete
  SELECT DATE(datetime) AS date, substring(itemcallnumber,1,1) AS 'Call# range', count(*) AS count  
  FROM statistics 
  LEFT JOIN items USING (itemnumber) 
  WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = <<Year (yyyy)>> AND MONTH(datetime) = <<Month (mm)>>  
  GROUP BY DATE(datetime), substring(itemcallnumber,1,1)

List that totals the circulation of each Dewey section, F, and periodicals, by day

  • Developer: Joe Atzberger, Scotty Zollars
  • Module: Statistical (Circulation, Reports)
  • Purpose: List that totals the circulation of each Dewey section, F, and periodicals, by day
  • Status: Complete
  SELECT DATE(datetime) AS date, substring(itemcallnumber,1,1) AS 'Call# range', count(*) AS count  
  FROM statistics 
  LEFT JOIN items USING (itemnumber) 
  WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = <<Year (yyyy)>> AND MONTH(datetime) = <<Month (mm)>> AND DAY(datetime) = <<Day (dd)|date>>
  GROUP BY DATE(datetime), substring(itemcallnumber,1,1)

Overdue materials

  • Developer: Sharon Moreland
  • Module: Circulation
  • Purpose: Overdue materials
  • Status: Complete


SELECT borrowers.surname, borrowers.firstname, borrowers.phone, borrowers.cardnumber, 
       borrowers.address, borrowers.city, borrowers.zipcode, issues.date_due, 
       (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', items.itype, 
       items.itemcallnumber, items.barcode, items.homebranch, biblio.title, biblio.author 
FROM borrowers 
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber) 
LEFT JOIN items ON (issues.itemnumber=items.itemnumber) 
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) 
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > '30' AND issues.branchcode = <<Issuing branch|branches>>
ORDER BY borrowers.surname ASC, issues.date_due ASC

Long Overdues

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Items that are long overdue
  • Status: Complete


SELECT p.surname, p.firstname, p.phone, p.cardnumber, 
       c.date_due, b.title, b.author, i.itemcallnumber, i.barcode, 
       i.homebranch, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue' 
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) 
LEFT JOIN items i ON (c.itemnumber=i.itemnumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > <<Due date more than (enter IN days)>> 
AND (TO_DAYS(curdate())-TO_DAYS(date_due)) < <<Due date less than (enter IN days)>>
ORDER BY p.surname ASC, c.date_due ASC

Count of Circ by Alpha Call Number Prefix

  • Developer: Jared Camins-Esakov and Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A statistical report showing how many items with a specific alphabetical prefix have circulated in a month
  • Status: Complete
SELECT SUBSTRING_INDEX(itemcallnumber, ' ', 1) AS 'Call# range', count(*) AS count 
FROM statistics 
LEFT JOIN items USING (itemnumber) 
WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = <<Year (yyyy)>> AND 
      MONTH(datetime) = <<Month (mm)>> AND SUBSTRING_INDEX(itemcallnumber, ' ', 1) RLIKE '[a-z]' 
GROUP BY SUBSTRING_INDEX(itemcallnumber, ' ', 1)

Circ by Literary Form

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A statistical report showing how many checkouts & renewals by literary form (008/33) in a date range
  • Status: Complete
SELECT 
CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),34,1)
      WHEN '0' THEN 'non fiction'
      WHEN '1' THEN 'fiction'
      WHEN 'd' THEN 'drama'
      WHEN 'e' THEN 'essay'
      WHEN 'f' THEN 'novel'
      WHEN 'h' THEN 'humor'
      WHEN 'i' THEN 'letter'
      WHEN 'j' THEN 'short stories'
      WHEN 'm' THEN 'mixed'
      WHEN 'p' THEN 'poetry'
      WHEN 's' THEN 'speeches'
      ElSE 'unknown' END
   AS bibtype, count(s.itemnumber) AS circs 
FROM biblioitems m 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN items i ON (b.biblionumber=i.biblionumber) 
LEFT JOIN statistics s USING (itemnumber) 
WHERE s.type IN ('issue','renew') AND 
      s.datetime BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY bibtype

Top 10 Circulating Books

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Top 10 circulating books for the last 6 months
  • Status: Complete
SELECT count(s.datetime) AS circs, b.title, b.author,
       i.ccode 
FROM statistics s
JOIN items i ON (i.itemnumber=s.itemnumber) 
LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber) 
WHERE DATE(s.datetime) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH) 
      AND DATE(s.datetime)<=CURRENT_DATE() AND 
      s.itemnumber IS NOT NULL 
GROUP BY b.biblionumber 
ORDER BY circs DESC 
LIMIT 10

Low Circulating Items

  • Developer: Jared Camins-Esakov, ByWater Solutions
  • Module: Circulation
  • Purpose: A list of materials (title, author, barcode, call number) that have only gone out X number of times during X dates, from X item type
  • Status: Complete
SELECT biblio.title, biblio.author, items.barcode, items.itemcallnumber 
FROM old_issues 
LEFT JOIN items ON (items.itemnumber=old_issues.itemnumber) 
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber) 
WHERE old_issues.issuedate BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
AND items.itype=<<Item Type Code>> 
GROUP BY old_issues.itemnumber HAVING COUNT(old_issues.issuedate) = <<Total Issues>> 
ORDER BY biblio.title ASC

Overdues at a Specific Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Patron and item info for books that are overdue from one specific branch.
  • Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, 
       p.phone, p.address, p.city,  
       p.zipcode, c.issuedate, c.date_due, 
       (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', b.title, 
       i.barcode 
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) 
LEFT JOIN items i ON (c.itemnumber=i.itemnumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE c.branchcode = <<Branch|branches>> AND 
      (TO_DAYS(curdate())-TO_DAYS( date_due)) > 0 
ORDER BY p.surname ASC, c.date_due ASC

Items with no Circs in a specific timeframe

  • Developer: Nicole C. Engard and Ian Walls, ByWater Solutions
  • Module: Circulation
  • Purpose: Titles that haven't checked out in a specific period of time
  • Status: Complete
SELECT b.title, b.author, i.itemcallnumber, i.barcode 
FROM biblio b
JOIN items i USING (biblionumber) 
WHERE i.itype = <<Item type|itemtypes>> AND itemnumber NOT IN 
     (SELECT DISTINCT itemnumber 
      FROM statistics 
      WHERE type = 'issue' AND 
      datetime BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>)

Checkout by Shelving Location

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A count of checkouts by shelving location at a specific branch in a specific timeframe.
  • Status: Complete
SELECT i.location, count(s.datetime) AS count 
FROM items i
LEFT JOIN statistics s USING (itemnumber) 
WHERE date(s.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND s.type='issue' AND s.branch=<<Pick your branch|branches>> 
GROUP BY i.location 
ORDER BY i.location ASC

Checkins by Shelving Location

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A count of checkins by shelving location at a specific branch in a specific timeframe.
  • Status: Complete


SELECT i.location, count(s.datetime) AS count 
FROM items i 
LEFT JOIN statistics s USING (itemnumber) 
WHERE date(s.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND s.type='return' AND s.branch=<<Pick your branch|branches>> 
GROUP BY i.location 
ORDER BY i.location ASC

Renewals by Shelving Location

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A count of renewals by shelving location at a specific branch in a specific timeframe.
  • Status: Complete
SELECT items.location, count(statistics.datetime) AS count 
FROM items LEFT JOIN statistics USING (itemnumber) 
WHERE date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND statistics.type='renew' AND statistics.branch=<<Pick your branch|branches>> 
GROUP BY items.location 
ORDER BY items.location ASC


Local Use by Shelving Location

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A count of in house use by shelving location at a specific branch in a specific timeframe.
  • Status: Complete
SELECT i.location, count(s.datetime) AS count 
FROM items i LEFT JOIN statistics s USING (itemnumber) 
WHERE date(s.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND s.type='localuse' AND s.branch=<<Pick your branch|branches>> 
GROUP BY i.location 
ORDER BY i.location ASC

Circ Transaction Counts

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A count of circulation transactions at a specific branch in a specific timeframe.
  • Status: Complete
SELECT type AS 'Transaction type', count(datetime) AS count 
FROM statistics 
WHERE date(datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND branch==<<Pick your branch|branches>> 
GROUP BY type 
ORDER BY type ASC

Checkouts & Renewal Counts by Call Number

  • Developer: VOKAL
  • Module: Circulation
  • Purpose: A count of checkouts and renewals (and a total of both) in a specific month.
  • Status: Complete
SELECT LEFT(i.itemcallnumber,3) AS "Call No.", 
       SUM(IF(s.type = 'issue', 1, 0)) AS Checkout, 
       SUM(IF(s.type = 'renew', 1, 0)) AS Renewal, 
       SUM(IF((s.type = 'renew' OR s.type='issue'), 1, 0)) AS TOTAL
FROM items i 
LEFT JOIN statistics s 
ON i.itemnumber=s.itemnumber
WHERE year(s.datetime)=<<Year (yyyy)>> AND month(s.datetime)=<<Month (mm)>> 
      AND i.homebranch=<<Branch|branches>> 
GROUP BY LEFT(i.itemcallnumber,3) 
WITH ROLLUP

Detailed report of long-overdues charged-off in the last week

  • Developer: D Ruth Bavousett, ByWater Solutions
  • Module: Circulation
  • Purpose: List all borrowers/items that have been marked as Lost--and remain unpaid--from the last seven days.
  • Status: Completed
SELECT cardnumber AS "Borrower Barcode",
               surname AS "Last Name", 
               firstname AS "First Name", 
               ROUND(amountoutstanding,2) AS "Amount Due", 
               biblio.title AS "Title", 
               author AS "Author",
               barcode AS "Item Barcode"
   FROM accountlines
   JOIN borrowers USING (borrowernumber) LEFT JOIN items USING (itemnumber) JOIN biblio USING (biblionumber) 
WHERE accounttype = "L" AND date > DATE_SUB(CURRENT_DATE(),INTERVAL 7 DAY)


Average Checkouts

  • Developer: Katrin Fischer and Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: Average number of checkouts in time period
  • Status: Complete
SELECT avg(counter) AS average 
FROM 
   (SELECT borrowernumber, date(datetime) AS ckodate, 
           count(*) AS counter 
    FROM statistics 
    WHERE date(datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd>> 
          AND <<and (yyyy-mm-dd)|date>> AND type='issue'
    GROUP BY borrowernumber, ckodate) 
temp

Self Check Circ Stats

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: Find the circulation numbers for specific staff logins. Often used to track the checkouts at self check machine. Replace BORROWERNUMBER-# with the borrowernumber for the self check machine (add as many as you have separated by commas).
  • Status: Complete
SELECT p.cardnumber, count(l.timestamp) AS circs 
FROM action_logs l 
LEFT JOIN borrowers p ON (p.borrowernumber=l.user) 
WHERE l.module='CIRCULATION' AND l.action='ISSUE' 
      AND l.user IN (BORROWERNUMBER-1, BORROWERNUMBER-2) 
      AND l.timestamp BETWEEN <<Between (yyyy-mmd-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> 
GROUP BY p.cardnumber

Old circulation issues since the beginning

  • Developer: Schnydszch
  • Module: Statistical (Circulation)
  • Purpose: Find all issues now closed from the start of your Koha installation. It extracts the more useful data and also the title (245$a) from biblioitems.marcxml
  • Status: Complete
SELECT old_issues.*, borrowers.cardnumber, borrowers.surname,
       borrowers.firstname, borrowers.sort1, borrowers.sort2, items.barcode,
       items.datelastborrowed, items.datelastseen, items.itemcallnumber,
       items.issues, items.holdingbranch, items.location, items.permanent_location,
       items.onloan, items.ccode, items.itype, items.enumchron, items.copynumber,
       items.location, b.totalissues, EXTRACTVALUE( b.marcxml, '//datafield[@tag="245"]/subfield[@code>="a"]' ) AS TITLE 
FROM old_issues
LEFT JOIN borrowers ON ( borrowers.borrowernumber = old_issues.borrowernumber ) 
LEFT JOIN items ON ( items.itemnumber = old_issues.itemnumber ) 
LEFT JOIN biblioitems AS b ON ( items.biblioitemnumber = b.biblioitemnumber)

List of total check-out books (which are not returned, sort datewise and branchwise)

  • Developer: Nikunj Tyagi, Delhi Public Library
  • Module: Circulation
  • Purpose: To generate checked out books list which not recieved back, sort by days and Branch wise
  • Example: List of not retured books from 10, 20, 30 days and in particular branch, type branch code
  • Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber,
issues.date_due,(TO_DAYS(curdate())-TO_DAYS(date_due)) AS 'days overdue',items.itemcallnumber, items.barcode, biblio.title, biblio.author
FROM borrowers
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= <<Books NOT received (XX) Days>> AND homebranch =<<Type Homebranch code(XXXX)>>
ORDER BY issues.date_due ASC, borrowers.surname ASC


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>>

Issues placed at a specific branch during a specified period

  • Developer: Liz Rea, Catalyst IT (For South Taranaki District Council Libraries)
  • Module: Circulation
  • Purpose: lists borrowers card number, title, and barcode number of items that were issued, and the date they were issued. The person running the report is able to choose the branch and the date period when running the report.
  • Example:
  • Status: Complete
SELECT statistics.datetime AS 'Issue Date', borrowers.surname,borrowers.firstname, borrowers.cardnumber, items.barcode, biblio.title FROM borrowers 
LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) 
LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) 
LEFT JOIN biblio ON (biblio.biblionumber = items.biblionumber) 
WHERE statistics.type='issue' 
AND date(statistics.datetime) 
BETWEEN <<Item issued BETWEEN (yyyy-mm-dd)|date>> 
AND <<and (yyyy-mm-dd)|date>> 
AND statistics.branch= <<Select library|branches>> 
ORDER BY borrowers.surname ASC

Percentage of circs by collection code

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Percentage of circs by collection code in a date range
  • Status: Complete
SELECT
   x.ccode AS collection,
   x.issues AS 'Circ ',
   (x.issues * 100)/(
     SELECT COUNT(s.datetime) AS 'total'
     FROM statistics s
     WHERE type = 'issue'
     AND s.datetime BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
   ) AS 'Percentage of total circ'
FROM
   (SELECT
     i.ccode,
     COUNT(s.datetime) AS 'issues'
   FROM statistics s
   JOIN items i USING (itemnumber)
   WHERE s.datetime BETWEEN <<Enter the same dates again BETWEEN (yyyy-mm-dd)|date>> AND
     <<and (yyyy-mm-dd)|date>> AND s.type='issue'
   GROUP BY i.ccode) x

Borrower count by age group and each groups circulation count for a duration, for those cardholders which have circulated during the given duration

  • Developer: Olli-Antti Kivilahti, Vaarakirjastot.fi
  • Module: Circulation
  • Purpose: Display count of borrowers by age category and each category's circulation count
  • Example:
  • Status: Complete, depends on borrower circulation history not being anonymized.
SELECT
  /* Get the statistics for over 65 year olds */
  SUM( IF(  stat.circ > 0 && dateofbirth < DATE_SUB(CURDATE(), INTERVAL 64 YEAR)  ,1,0)) AS '65+',
  SUM( IF(  stat.circ > 0 && dateofbirth < DATE_SUB(CURDATE(), INTERVAL 64 YEAR)  ,stat.circ,0)) AS '65+ circ',
 
  /* Get the statistics for ages 16-64 */
  SUM( IF(  stat.circ > 0 && dateofbirth BETWEEN DATE_SUB(CURDATE(), INTERVAL 64 YEAR) AND DATE_SUB(CURDATE(), INTERVAL 16 YEAR)  ,1,0)) AS '16-64',
  SUM( IF(  stat.circ > 0 && dateofbirth BETWEEN DATE_SUB(CURDATE(), INTERVAL 64 YEAR) AND DATE_SUB(CURDATE(), INTERVAL 16 YEAR)  ,stat.circ,0)) AS '16-64 circ',
 
  /* Get the statistics for under 16 year olds */
  SUM( IF(  stat.circ > 0 && dateofbirth > DATE_SUB(CURDATE(), INTERVAL 16 YEAR)  ,1,0)) AS '0-16',
  SUM( IF(  stat.circ > 0 && dateofbirth > DATE_SUB(CURDATE(), INTERVAL 16 YEAR)  ,stat.circ,0)) AS '0-16 circ'
 
/* Get the circulation count for each borrower, join using borrowernumber */
FROM borrowers b 
  LEFT JOIN (SELECT borrowernumber,count(*) AS circ
               FROM statistics s WHERE s.type IN ('issue','renew') AND datetime BETWEEN <<alkupäivä|date>> AND <<loppupäivä|date>>
               GROUP BY s.borrowernumber
            )
  AS stat ON stat.borrowernumber = b.borrowernumber
;

Course Reserves Reports

All course reserve items

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Course Reserves
  • Purpose: All titles on reserve


SELECT b.title, i.itype, t.itype AS 'course item type', i.ccode, t.ccode AS 'course ccode', 
i.itemcallnumber, i.barcode, i.itemnotes, c.course_name 
FROM course_items t 
LEFT JOIN  items i USING (itemnumber) 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN course_reserves r USING (ci_id) 
LEFT JOIN courses c USING (course_id)

Catalog/Bibliographic Reports

Barcode Search Report

  • Developer: Ata ur Rehman (ata.rehman@gmail.com)
  • Module: Catalog
  • Purpose: Barcode search report. To verify if a record available against provided barcode. Barcode can be searched with wild cards '%' or '_'
  • Status: Complete
SELECT  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers, items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate 
FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.homebranch =<<Branch|branches>> AND items.barcode LIKE <<Enter Barcode>>
ORDER BY LPAD(items.barcode,30,' ') ASC

Accession Register Sorted by Barcode Number Report

  • Developer: Ata ur Rehman (ata.rehman@gmail.com)
  • Module: Catalog
  • Purpose: To create an Accession Register Sorted by Barcode Number Report
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers, 
       items.barcode, items.dateaccessioned, items.itemcallnumber, biblioitems.isbn, biblio.author, biblio.title, biblioitems.pages, 
       biblioitems.publishercode, biblioitems.place, biblio.copyrightdate 
FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.homebranch =<<Branch|branches>> 
ORDER BY LPAD(items.barcode,40,' ') ASC

Total collection size

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Total collection size
  • Status: Complete
  SELECT count(i.biblionumber) AS added, i.itype, i.homebranch, i.location 
  FROM items i 
  WHERE i.dateaccessioned < <<Acquired before (yyyy-mm-dd)|date>>  
  GROUP BY i.homebranch,i.itype,i.location 
  ORDER BY i.homebranch,i.itype,i.location ASC

Total Collection Size by Date

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Total collection size by item type and branch by a specific date (for example first of the month)
  • Status: Complete
SELECT COALESCE(homebranch,'*GRAND TOTAL*') AS homebranch, 
       IFNULL(itype, "") AS itype, count(itype) AS count 
FROM items 
WHERE dateaccessioned < <<Added before (yyyy-mm-dd)|date>> 
GROUP BY homebranch, itype 
WITH rollup

URLs in Catalog

  • Developer: Lenora Oftedahl
  • Module: Catalog
  • Purpose: URLs in Catalog
  • Status: Needs work as I only want the URLs, not all barcodes
  SELECT  items.barcode,biblioitems.url 
  FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)   
  WHERE items.homebranch=<<Home branch|branches>>

Null Item Type

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Null Item Type
  • Status: Complete
  SELECT  items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)  
  WHERE items.itype IS NULL AND items.homebranch=<<Home branch|branches>>

Null Barcodes

  • Developer: Rachel Hollis
  • Module: Catalog
  • Purpose: Null Barcodes
  • Status: Complete
  SELECT items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate FROM biblio
  JOIN items USING(biblionumber)
  WHERE (items.barcode IS NULL OR items.barcode = '')

Items with "X" CCode

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Items with "X" CCode
  • Status: Complete
  SELECT  items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)  
  WHERE items.homebranch=<<Home branch|branches>> AND items.ccode=<<Collection|CCODE>> 
  ORDER BY items.dateaccessioned DESC

Items with "X" & "Y" ITypes

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Items with "X" & "Y" ITypes
  • Status: Complete
  SELECT  items.dateaccessioned,items.itype,items.itemcallnumber,items.barcode,biblio.author,biblio.title, biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE (items.homebranch=<<Home branch|branches>> AND items.itype=<<Item type|itemtypes>>) 
        OR (items.homebranch=<<Second home branch|branches>> AND items.itype=<<Second item type|itemtypes>>) 
  ORDER BY items.dateaccessioned DESC

Call Numbers

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Call Numbers
  • Status: Complete
  SELECT items.itype,items.itemcallnumber,items.barcode,biblio.title,biblio.copyrightdate 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE items.homebranch=<<Home branch|branches>> AND items.itemcallnumber LIKE concat(<<Call number like>>, '%')
  ORDER BY items.itemcallnumber ASC

Complete Shelf list

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: Complete Shelf list
  • Status: Complete
  SELECT  items.price,items.replacementprice,biblio.title,biblio.author,items.itemcallnumber 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE items.homebranch=<<Home branch|branches>> 
  ORDER BY items.itemcallnumber ASC

All Barcodes

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: All Barcodes
  • Status: Complete
  SELECT items.barcode,items.location,biblio.title,items.itemcallnumber 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)  
  WHERE items.homebranch=<<Home branch|branches>>

New Bib Records

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List new bibs in specific time frame
  • Status: Complete
SELECT monthname(datecreated) AS month, year(datecreated) AS year, count(biblionumber) AS count 
FROM biblio 
WHERE datecreated BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY year(datecreated), month(datecreated)

Average Age by Collection Code

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: This report averages the publication year of titles in your collection to get an age report
  • Status: Complete
SELECT round(avg(b.copyrightdate)) AS 'average year' 
FROM biblio b
LEFT JOIN items i 
USING (biblionumber) 
WHERE b.copyrightdate IS NOT NULL AND i.ccode = <<Collection|CCODE>>

Bibs Marked On Order

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List all the bib records that have been marked as on order
  • Status: Complete
SELECT b.title, b.author, i.barcode, 
       i.itemcallnumber, b.copyrightdate 
FROM biblio b
LEFT JOIN items i USING (biblionumber)
WHERE i.notforloan = '-1' 
ORDER BY b.title

List new items

  • Developer: Sharon Moreland
  • Module: Catalog
  • Purpose: List new items
  • Status: Complete
  SELECT items.dateaccessioned,biblio.title,items.itemcallnumber 
  FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE DATE (items.dateaccessioned)  BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND items.homebranch=<<Home branch|branches>> 
  ORDER BY items.itemcallnumber ASC

Another new items report

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List new items between specific dates
  • Status: Complete
SELECT monthname(timestamp) AS month, year(timestamp) AS year, count(itemnumber) AS count 
FROM items 
WHERE timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY year(timestamp), month(timestamp)

List of Items added to catalog in last 30 days

  • Developer: Nora Blake
  • Module: Catalog
  • Purpose: List of Items added to catalog in last 30 days (includes bibliographic info)
  • Status: Complete
  SELECT items.dateaccessioned,items.itemcallnumber,biblio.title,biblio.author 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE items.homebranch=<<Home branch|branches>> AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= items.dateaccessioned 
  ORDER BY biblio.title ASC

Count of all items

  • Developer: Michael Hafen
  • Module: Catalog
  • Purpose: Count of all items
  • Status: Complete
  SELECT COUNT(barcode) AS Count FROM items WHERE barcode <> '' AND barcode IS NOT NULL

Count of all items by Item Type

  • Developer: Michael Hafen
  • Module: Catalog
  • Purpose: Count of all items by Item Type
  • Status: Complete
  SELECT itype AS 'Item Type',COUNT(barcode) AS Count FROM items WHERE barcode <> ''
  AND barcode IS NOT NULL GROUP BY itype

Count of all items and broken down by branch

  • Developer: Zachary Spalding, SENYLRC
  • Module: Catalog
  • Purpose: Count of all items by Item and broken down by branch
  • Status: Complete
SELECT items.homebranch,branches.branchname, count(items.itemnumber) AS items FROM items,branches WHERE items.homebranch=branches.branchcode GROUP BY homebranch ORDER BY homebranch ASC

Count of all titles

  • Developer: Michael Hafen
  • Module: Catalog
  • Purpose: Count of all titles
  • Status: Complete
  SELECT COUNT(biblio.title) AS Count FROM biblio

Count of all Bibs and Items per Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A count of all unique bibs and total items held at each branch
  • Status: Complete
SELECT homebranch, count(DISTINCT biblionumber) AS bibs, 
       count(itemnumber) AS items 
FROM items 
GROUP BY homebranch 
ORDER BY homebranch ASC


Count of all Bibs and Items by item type

  • Developer: Agnes Rivers-Moore
  • Module: Catalog
  • Purpose: A count of titles and items by item type, with item type descriptions.
  • Status: Complete
SELECT items.itype, itemtypes.description, count(DISTINCT items.biblionumber) AS bibs, 
count(items.itemnumber) AS items 
FROM items, itemtypes
WHERE items.itype=itemtypes.itemtype AND items.barcode IS NOT NULL 
GROUP BY items.itype
ORDER BY itemtypes.description

Statistical Count of total number of items held by each branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Statistical Count of total number of items held by each branch all in one report
  • Status: Complete
  SELECT homebranch,count(itemnumber) AS items 
  FROM items 
  GROUP BY homebranch 
  ORDER BY homebranch ASC

All bibs without items

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Bib records without items
  • Status: Complete
SELECT biblionumber, title 
FROM biblio 
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)

All bibs where last item deleted

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: All bibs without items where the last item was deleted
  • Status: Complete
SELECT b.biblionumber, b.title, b.author
FROM biblio b
LEFT JOIN items i USING (biblionumber) 
WHERE i.itemnumber IS NULL 
      AND b.biblionumber IN (SELECT biblionumber FROM deleteditems)
GROUP BY b.biblionumber

All bibs where last item deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: All bibs without items where the last item was deleted in a specific timeframe (often used for notifying OCLC of holdings changes)
  • Status: Complete
SELECT b.biblionumber, b.title, b.author
FROM biblio b
LEFT JOIN items i USING (biblionumber) 
WHERE i.biblionumber NOT IN (SELECT biblionumber FROM items) AND 
      b.biblionumber IN (SELECT biblionumber FROM deleteditems WHERE date(timestamp) = <<Deleted ON (yyyy-mm-dd)|date>>)

Weeding tool

  • Developer: Kathy Rippel
  • Module: Catalog
  • Purpose: Weeding tool, we call this the SuperWeeder because it includes all sorts of data to help in decision making
  • Status: Complete
SELECT CONCAT( '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=', biblio.biblionumber,'\">', 
       items.barcode, '</a>' ) AS 'Barcode', items.itemcallnumber, biblio.title, 
       biblio.copyrightdate AS 'Copyright', items.dateaccessioned AS 'Accessioned', items.itype, 
       items.issues, items.renewals, (IFNULL(items.issues, 0)+IFNULL(items.renewals, 0)) AS Total_Circ, 
       items.datelastborrowed, items.itemlost, items.onloan, items.damaged, items.itemnotes
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype= <<Item type code|itemtypes>> AND items.holdingbranch=<<Branch code|branches>> 
      AND items.itemcallnumber BETWEEN <<Call number between>> AND <<and>>
ORDER BY items.itemcallnumber

Inventory Report

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Find all items that haven't been seen since a specific date
  • Status: Complete
SELECT b.title, i.barcode, i.itemcallnumber, i.itemlost, i.damaged,
      IF(i.onloan IS NULL, '', 'checked out') AS onloan
 FROM biblio b
 LEFT JOIN items i USING (biblionumber)
 WHERE datelastseen < <<Last seen before (yyyy-mm-dd)|date>> 
                 AND i.homebranch=<<Home branch|branches>>
 ORDER BY datelastseen DESC, i.itemcallnumber ASC

Inventory Report by Location

  • Developer: Jason O'Neil. Original by Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Find all items that haven't been seen since a specific date, filtered by shelving location, and with borrower details for items that are currently checked out.
  • Status: Complete
SELECT b.title, i.barcode, i.itemcallnumber, datelastseen, i.location,
      IF(i.onloan IS NULL, '', 'checked out') AS onloan,
      IF(p.cardnumber IS NULL, '', p.cardnumber) AS cardnumber,
      IF(p.firstname IS NULL, '', p.firstname) AS firstname,
      IF(p.surname IS NULL, '', p.surname) AS surname
 FROM biblio b
 LEFT JOIN items i USING (biblionumber)
 LEFT JOIN issues c ON (i.itemnumber=c.itemnumber) 
 LEFT JOIN borrowers p ON (p.borrowernumber=c.borrowernumber) 
 WHERE datelastseen < <<Last seen before (yyyy-mm-dd)|date>> 
                 AND i.homebranch=<<Home branch|branches>>
                 AND i.location=<<Location|LOC>>
 ORDER BY onloan DESC, datelastseen DESC, i.itemcallnumber ASC

Items added by Collection

  • Developer: Katrin Fischer and Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Count of items added by collection in a specific date range
  • Status: Complete
SELECT count(ccode), ccode AS collection
FROM (
SELECT ccode, dateaccessioned FROM items
UNION ALL
SELECT ccode, dateaccessioned FROM deleteditems
)
AS itemsadded
WHERE date(dateaccessioned) BETWEEN
<<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY ccode

Damaged Items with Title

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Damaged Items with Title
  • Status: Complete
SELECT items.damaged, items.itemcallnumber, items.barcode, biblio.title, biblio.author 
FROM items 
INNER JOIN biblio ON items.biblionumber = biblio.biblionumber 
WHERE items.damaged = True ORDER BY biblio.title ASC

Action log entries of items damaged within the last day

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Catalog
  • Purpose: Items damaged within the last day
  • Status: Complete
SELECT
      b.title
    , b.author
    , i.itemnumber
    , i.barcode
    , i.timestamp
    , l.*
FROM items i
LEFT JOIN biblio b USING ( biblionumber )
LEFT JOIN action_logs l ON (l.timestamp >= timestamp( SUBDATE(CURDATE(), INTERVAL 1 DAY) ) AND l.object = i.itemnumber )
WHERE
    i.damaged = 1
    AND DATE(i.timestamp) >= SUBDATE(CURDATE(), INTERVAL 1 DAY)
    AND l.info LIKE '%damaged%'
ORDER BY i.timestamp ASC


Count by Call Number

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Count by Call Number
  • Status: Complete
SELECT count(items.itemcallnumber) AS 'Number of Items', items.itemcallnumber 
FROM items 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber ASC

Count by Call Number for items added last month

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Count by Call Number for items added last month
  • Status: Complete
SELECT count(items.itemcallnumber), items.itemcallnumber 
FROM items 
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber ASC

Items Added in Date Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Items added in a time period (will ask for date range twice)
  • Status: Complete
SELECT sum(count) AS added
FROM (
SELECT count(*) AS count FROM items WHERE date(dateaccessioned) BETWEEN <<Added
BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
UNION ALL
SELECT count(*) AS count FROM deleteditems WHERE date(dateaccessioned) BETWEEN
<<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
) AS items

Previous Month Items Created

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Created
  • Status: Complete
SELECT count(items.itemnumber) AS ItemsCreated 
FROM items 
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month)

Previous Month Items Deleted

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Deleted
  • Status: Complete
SELECT count(deleteditems.itemnumber) AS ItemsDeleted 
FROM deleteditems 
WHERE deleteditems.timestamp LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))

Items Deleted in Date Range at Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Items deleted at a branch in a time period
  • Status: Complete
SELECT count(*) AS "Items Deleted"
FROM deleteditems
WHERE timestamp BETWEEN <<Deleted BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND homebranch=<<Owning branch|branches>>

Previous Month Items Created--by item type

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Created--by item type (The total number of rows shown is misleading -- It matches the first item type total. An empty item type column means unknown item type. Add all the entries for the complete total.)
  • Status: Complete
SELECT items.itype AS ItemType, count(items.itemnumber) AS ItemsCreated 
FROM items 
WHERE (items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month))  
GROUP BY items.itype

Previous Month Items Deleted--by item type

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Previous Month Items Deleted--by item type (The total number of rows shown is misleading -- It matches the first item type total. An empty item type column means unknown item type. Add all the entries for the complete total.)
  • Status: Complete
SELECT deleteditems.itype AS ItemType, count(deleteditems.itemnumber) AS ItemsDeleted 
FROM deleteditems 
WHERE (deleteditems.timestamp LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))) 
GROUP BY deleteditems.itype

Withdrawn Items (w/ details)

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Withdrawn Items
  • Status: Complete
SELECT biblio.title,biblio.author,items.itemcallnumber,items.barcode,items.datelastborrowed, items.wthdrawn 
FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.wthdrawn != 0 
ORDER BY biblio.title ASC

Withdrawn Items 3.12- (barcodes only)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Barcodes of items marked as withdrawn (best used for batch deleting)
  • Status: Complete
  • Version: 3.12-
SELECT barcode
FROM items
WHERE wthdrawn != 0 
ORDER BY barcode ASC

Withdrawn Items 3.14+ (barcodes only)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Barcodes of items marked as withdrawn (best used for batch deleting)
  • Status: Complete
  • Version: 3.14+
SELECT barcode
FROM items
WHERE withdrawn != 0 
ORDER BY barcode ASC

List of URL's from 856

  • Developer: LibLime provided to David Schuster
  • Module: Catalog
  • Purpose: List of URL's from 856
  • Status: Complete
SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml, LOCATE('<subfield code="u">', 
       biblioitems.marcxml, LOCATE('<datafield tag="856"', biblioitems.marcxml)+19), 
       LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="u">', 
       biblioitems.marcxml, LOCATE('<datafield tag="856"', 
       biblioitems.marcxml)+19)) - LOCATE('<subfield code="u">', biblioitems.marcxml, 
       LOCATE('<datafield tag="856"', biblioitems.marcxml)+19)) AS url 
FROM biblioitems, biblio 
WHERE biblioitems.biblionumber = biblio.biblionumber AND url IS NOT NULL

Count of URL's from 856

  • Developer: From listserv provided to David Schuster
  • Module: Catalog
  • Purpose: count of URL's from 856
  • Status: Complete
SELECT count(*) FROM biblioitems WHERE biblioitems url != 'null';

Records without items

  • Developer: Magnus Enger
  • Module: Catalog
  • Purpose: Records without items, with links to OPAC and Intranet
  • Status: Complete
  • Note: Revised by Jared Camins-Esakov to provide correct link to OPAC based on OPACBaseURL
SELECT b.title AS Title, CONCAT('<a href=\"', IF(CHAR_LENGTH(systempreferences.value), CONCAT('http://', systempreferences.value), ''), '/cgi-bin/koha/opac-detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS OPAC,
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS Edit  
FROM systempreferences, biblio AS b 
  LEFT JOIN items AS i ON b.biblionumber = i.biblionumber 
WHERE i.itemnumber IS NULL AND systempreferences.variable='OPACBaseURL'

Call Number Shelflist

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: list in call number order
  • Status: Completed
SELECT items.itemcallnumber,items.datelastborrowed,biblio.title,biblioitems.publicationyear 
FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
ORDER BY items.cn_sort ASC

Duplicate titles

  • Developer: D Ruth Bavousett, ByWater Solutions
  • Module: Catalog
  • Purpose: Checks for exact duplicates on author/title combo; download for full list (doesn't paginate)
  • Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, title, author 
FROM biblio 
GROUP BY CONCAT(title,"/",author) HAVING COUNT(CONCAT(title,"/",author))>1

Duplicate titles (with same date)

  • Developer: Jared Camins-Esakov
  • Module: Catalog
  • Purpose: Based on druthb's report for duplicate titles, but considers date as well; download for full list (doesn't paginate)
  • Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, title, author,copyrightdate 
FROM biblio 
GROUP BY CONCAT(title,"/",author,"/",copyrightdate) HAVING COUNT(CONCAT(title,"/",author,"/",copyrightdate))>1

Duplicate ISBNs

  • Developer: Jared Camins-Esakov, ByWater Solutions
  • Module: Catalog
  • Purpose: Show records with duplicate ISBNs; download for full list (doesn't paginate)
  • Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, isbn 
FROM biblioitems 
GROUP BY isbn 
HAVING COUNT(isbn)>1


Duplicate ISBNs Alternative

  • Developer: Nick Clemens, VOKAL
  • Module: Catalog
  • Purpose: Building from the duplicate isbn report, but normalizing to 13-digits and adding a fast link to merge the highest and lowest bibnumbers. Long and maybe a bit clunky, but very effective.
  • Status: Completed
SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title, 
       b.author, IF(LEFT(REPLACE(TRIM(i.isbn),'-',''),3)<>'978',CONCAT('978',LEFT(REPLACE(TRIM(i.isbn),'-',''),9),(MOD(10-MOD((CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),1,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),3,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),5,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),7,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),9,1),UNSIGNED INTEGER))*3+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),2,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),4,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),6,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),8,1),UNSIGNED INTEGER)+38,10),10))),LEFT(REPLACE(TRIM(i.isbn),'-',''),13))AS NormISBN, CONCAT('<a href=\"http://staff.kohavt.org/cgi-bin/koha/cataloguing/merge.pl?biblionumber=',MIN(b.biblionumber),'&biblionumber=',MAX(b.biblionumber),'\">Merge</a>') AS FastMerge, GROUP_CONCAT(DISTINCT b.typelist SEPARATOR '::') AS TypeDiscrepCheck
FROM (SELECT b2.biblionumber, b2.title,b2.author, COUNT(i2.barcode) AS itemcount, GROUP_CONCAT(DISTINCT i2.itype) AS typelist FROM biblio b2 JOIN items i2 ON i2.biblionumber=b2.biblionumber GROUP BY b2.biblionumber HAVING itemcount>0) b 
LEFT JOIN biblioitems i ON (i.biblionumber=b.biblionumber)
WHERE i.isbn IS NOT NULL AND i.isbn<>''
GROUP BY CONCAT(substr(b.title,1,9),"/",NormISBN) 
HAVING COUNT(CONCAT(substr(b.title,1,9),"/",NormISBN))>1
ORDER BY COUNT(b.biblionumber) ASC


Duplicate Titles (using title and ISBN)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Show records with duplicate titles (using the first 9 characters) and duplicate ISBNs
  • Status: Completed
SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title, 
       b.author, GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns 
FROM biblio b 
LEFT JOIN biblioitems i 
ON (i.biblionumber=b.biblionumber)
GROUP BY CONCAT(substr(b.title,0,9),"/",i.isbn) 
HAVING COUNT(CONCAT(substr(b.title,0,9),"/",i.isbn))>1

Duplicate ISBNs with Links to Bib Records

  • Developer: Zachary Spalding, SENYLRC
  • Module: Catalog
  • Purpose: Show records with duplicate ISBNs; download for full list (doesn't paginate) and has links to bib records. Based on ISBN report written by Jared Camins-Esakov
  • Status: Completed
SELECT  GROUP_CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>') AS biblionumbers, 
        isbn 
FROM biblioitems 
GROUP BY isbn, itemtype 
HAVING COUNT(isbn)>1

Duplicate ISBNs in Time Frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Show records with duplicate ISBNs added within a specific time frame.
  • Status: Completed
SELECT GROUP_CONCAT(CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') 
       SEPARATOR ', ') AS biblionumbers, b.title, b.author 
FROM biblio b
LEFT JOIN biblioitems i USING (biblionumber)
WHERE b.datecreated BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY substring_index(i.isbn, ' ', 1) 
HAVING COUNT(substring_index(i.isbn, ' ', 1))>1

Duplicate bibs using the 001

  • Developer: Katrin Fischer and Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Show records with duplicate 001 fields
  • Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, 
       ExtractValue(marcxml,'//controlfield[@tag="001"]') AS id 
FROM biblioitems  
GROUP BY id  
HAVING count(id) > 1

Bibs with specific keyword in subjects

  • Developer: Chris Cormack & Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: This report shows all bib records with a subject that contains a specific keyword in the 650a
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber, lcsh 
FROM 
(SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')
AS lcsh FROM biblioitems) 
AS subjects 
WHERE lcsh 
LIKE <<Enter keyword surrounded BY %>>

Bibs without subjects

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows all bibs without subject headings
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber 
FROM 
(SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]') AS sub1, 
ExtractValue(marcxml,'//datafield[@tag="651"]/subfield[@code>="a"]') AS sub2, 
ExtractValue(marcxml,'//datafield[@tag="600"]/subfield[@code>="a"]') AS sub3, 
ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]') AS sub4, 
ExtractValue(marcxml,'//datafield[@tag="611"]/subfield[@code>="a"]') AS sub5, 
ExtractValue(marcxml,'//datafield[@tag="630"]/subfield[@code>="a"]') AS sub6, 
ExtractValue(marcxml,'//datafield[@tag="648"]/subfield[@code>="a"]') AS sub7, 
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code>="a"]') AS sub8, 
ExtractValue(marcxml,'//datafield[@tag="654"]/subfield[@code>="a"]') AS sub9, 
ExtractValue(marcxml,'//datafield[@tag="655"]/subfield[@code>="a"]') AS sub10, 
ExtractValue(marcxml,'//datafield[@tag="656"]/subfield[@code>="a"]') AS sub11, 
ExtractValue(marcxml,'//datafield[@tag="657"]/subfield[@code>="a"]') AS sub12, 
ExtractValue(marcxml,'//datafield[@tag="658"]/subfield[@code>="a"]') AS sub13, 
ExtractValue(marcxml,'//datafield[@tag="662"]/subfield[@code>="a"]') AS sub14 
FROM biblioitems) AS subjects 
WHERE sub1 = "" 
AND sub2 = "" 
AND sub3 = "" 
AND sub4 = "" 
AND sub5 = "" 
AND sub6 = "" 
AND sub7 = "" 
AND sub8 = "" 
AND sub9 ="" 
AND sub10 = "" 
AND sub11 = "" 
AND sub12 = "" 
AND sub13 = "" 
AND sub14 =""


Bibs without RDA specific fields

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A report to find bibs that have RDA fields (336-339)
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber
FROM 
(SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="336"]/subfield[@code>="a"]') AS rda1, 
ExtractValue(marcxml,'//datafield[@tag="337"]/subfield[@code>="a"]') AS rda2, 
ExtractValue(marcxml,'//datafield[@tag="338"]/subfield[@code>="a"]') AS rda3, 
ExtractValue(marcxml,'//datafield[@tag="339"]/subfield[@code>="a"]') AS rda4 
FROM biblioitems) AS rda 
WHERE rda1 != "" 
OR rda2 != "" 
OR rda3 != "" 
OR rda4 != ""

Items with notes

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records with either a public or nonpublic note
  • Status: Complete
SELECT b.title, b.author, i.itemcallnumber, i.barcode, 
       i.itemnotes AS 'public note',
       ExtractValue(i.more_subfields_xml,'//datafield[@tag="999"]/subfield[@code>="x"]')
       AS 'nonpublic note' 
FROM items i 
LEFT JOIN biblio b USING (biblionumber) 
WHERE i.itemnotes IS NOT NULL 
      OR i.more_subfields_xml IS NOT NULL

All bibs without items

  • Developer: Frédéric Demians
  • Module: Catalog
  • Purpose: Get biblionumber of biblio records without items and which itemtype doesn't belongs to a list
  • Status: Complete

Warning: Repeated report, see All bibs without items by Nicole Engards.

SELECT
 biblio.biblionumber
FROM
 biblio
RIGHT JOIN
 biblioitems
ON
 biblio.biblionumber = biblioitems.biblionumber
LEFT JOIN
 items
ON
 biblio.biblionumber = items.biblionumber
WHERE
 items.biblionumber IS NULL
 AND
 itype NOT IN ('AGH', 'PER');

Bibs Suppressed in OPAC

  • Developer: Chris Hobbs, New Haven Unified School District
  • Module: Catalog
  • Purpose: Finds all bibs that have been flagged as Suppressed in 942$n
  • Status: Completed
  SELECT concat( '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '">', biblio.title, '</a>' ) AS title, biblio.author
  FROM biblioitems
  JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
  WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="n"]' )
  IN ('Y', '1')

List of Items Marked Lost/Missing

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Finds all items that are marked as lost in some way.
  • Status: Completed
SELECT i.itemnumber, b.title, b.author, i.itemcallnumber, 
       i.barcode, v.lib 
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value) 
WHERE i.itemlost != 0 AND v.category='LOST'

List of Items Marked Lost/Missing w/ Hold Info

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Finds all items that are marked as lost in some way and shows if they're on hold.
  • 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 i.itemlost != 0 AND v.category='LOST'

Validate Codabar barcodes used by North American libraries

  • Developer: Jared Camins-Esakov
  • Module: Catalog
  • Purpose: Identifies barcodes that are invalid based on the rules at http://www.mecsw.com/specs/codabar.html
  • Status: Completed
  • Note: Change '8060' to the 4-digit code used by your library
SELECT biblionumber, barcode, CONCAT_WS('; ', lengthproblem, typeproblem, libraryproblem, checksumproblem) 
FROM (
    SELECT
        items.biblionumber AS biblionumber, items.barcode AS barcode, 
        IF(CHAR_LENGTH(TRIM(items.barcode)) <> 14, 'Barcode wrong length', NULL) AS lengthproblem,
        IF(SUBSTR(TRIM(items.barcode), 1, 1) <> '3', 'Not an item barcode', NULL) AS typeproblem,
        IF(SUBSTR(TRIM(items.barcode), 2, 4) <> '8060', 'Wrong library code', NULL) AS libraryproblem,
        IF(MOD(10 - MOD((IF(SUBSTR(TRIM(items.barcode), 1, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 1, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 1, 1) * 2)) + 
            (SUBSTR(TRIM(items.barcode), 2, 1)) + 
            (IF(SUBSTR(TRIM(items.barcode), 3, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 3, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 3, 1) * 2)) + 
            (SUBSTR(TRIM(items.barcode), 4, 1)) + 
            (IF(SUBSTR(TRIM(items.barcode), 5, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 5, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 5, 1) * 2)) + 
            (SUBSTR(TRIM(items.barcode), 6, 1)) + 
            (IF(SUBSTR(TRIM(items.barcode), 7, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 7, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 7, 1) * 2)) + 
            (SUBSTR(TRIM(items.barcode), 8, 1)) + 
            (IF(SUBSTR(TRIM(items.barcode), 9, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 9, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 9, 1) * 2)) + 
            (SUBSTR(TRIM(items.barcode), 10, 1)) + 
            (IF(SUBSTR(TRIM(items.barcode), 11, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 11, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 11, 1) * 2)) + 
            (SUBSTR(TRIM(items.barcode), 12, 1)) + 
            (IF(SUBSTR(TRIM(items.barcode), 13, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 13, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 13, 1) * 2)), 10), 10) <> SUBSTR(TRIM(items.barcode), 14, 1), 'Check digit bad', NULL) AS checksumproblem
    FROM items) AS quer 
WHERE lengthproblem IS NOT NULL OR libraryproblem IS NOT NULL OR checksumproblem IS NOT NULL

Find unused sequential barcode ranges

  • Developer: Jared Camins-Esakov
  • Module: Catalog
  • Purpose: Find ranges of unused barcodes.
  • Status: Completed
  • Note: This query takes a *long* time. Minutes, not seconds. This query will only work on non-checksummed, sequential numeric barcodes
SELECT Convert(l.barcode, UNSIGNED) + 1 AS start, MIN(Convert(fr.barcode, UNSIGNED)) - 1 AS stop
FROM items AS l
    LEFT OUTER JOIN items AS r ON Convert(l.barcode, UNSIGNED) = Convert(r.barcode, UNSIGNED) - 1
    LEFT OUTER JOIN items AS fr ON Convert(l.barcode, UNSIGNED) < Convert(fr.barcode, UNSIGNED)
WHERE r.barcode IS NULL AND fr.barcode IS NOT NULL
GROUP BY l.barcode, r.barcode
ORDER BY l.barcode


Title/Subtitle List

  • Developer: Katrin Fischer
  • Module: Catalog
  • Purpose: List of full titles (title and subtitle) with call numbers
  • Status: Completed
SELECT concat(b.title, ' ', ExtractValue((
    SELECT marcxml 
    FROM biblioitems b2
    WHERE b.biblionumber = b2.biblionumber),
      '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, 
    b.author, i.itemcallnumber FROM biblio b LEFT JOIN items i ON (i.biblionumber=b.biblionumber)

Records Cataloged with a Specific Framework

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides a list of titles cataloged with a specific framework, handy for finding items added using Fast Add.
  • Status: Completed
SELECT title, author 
FROM biblio 
WHERE frameworkcode=<<Enter Framework Code>>

Withdrawn Titles List to Send to OCLC

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides title, author and OCLC number of withdrawn titles for sending to OCLC to update your holdings in a batch.
  • Status: Completed
SELECT b.title, b.author, ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') AS 'OCLC Number' 
FROM biblio b 
LEFT JOIN items i USING (biblionumber) 
LEFT JOIN biblioitems m USING (biblionumber)
WHERE i.wthdrawn > 0

Deleted Titles List to Send to OCLC

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides title, author, isbn and OCLC number of deleted titles at a branch in a specific time period for sending to OCLC to update your holdings in a batch.
  • Status: Completed
SELECT b.title, b.author, m.isbn, 
       ExtractValue(m.marcxml, '//controlfield[@tag="001"]') AS 'OCLC Number 001'
FROM biblio b
LEFT JOIN deleteditems i USING (biblionumber)
LEFT JOIN biblioitems m USING (biblionumber)
WHERE b.biblionumber NOT IN (SELECT biblionumber FROM items) 
      AND i.homebranch=<<Branch|branches>> AND 
      i.timestamp BETWEEN <<Deleted BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>>

Collection Evaluation Report

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Collection Evaluation report asks for branch, shelving location, data acquired range and date last borrowed range and returns titles
  • Status: Completed
SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber 
FROM biblio b 
LEFT JOIN items i 
USING (biblionumber) 
WHERE i.homebranch=<<Branch|branches>> AND i.location=<<Shelving location|LOC>> 
          AND i.dateaccessioned BETWEEN <<Date acquired BETWEEN (yyyy-mm-dd)|date>> AND 
         <<and (yyyy-mm-dd)|date>> AND i.datelastborrowed BETWEEN 
         <<Date last checked out BETWEEN (yyyy-mm-dd)|date>> AND 
         <<and (yyyy-mm-dd)|date>>
ORDER BY i.itemcallnumber ASC

Collection Evaluation Report 2

  • Developer: Nicole C. Engard and Ian Walls, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows entire collection with publication info pulled from the 008 (Tip: would be wise to add a filter of some sort to this)
  • Status: Completed
SELECT b.title, b.author, i.dateaccessioned, i.location, i.itemcallnumber,
i.itype, i.datelastborrowed, i.issues, substring(ExtractValue((
    SELECT marcxml
    FROM biblioitems b2
    WHERE b.biblionumber = b2.biblionumber), 
'//controlfield[@tag="008"]'),8,4) AS 'pub date'
FROM biblio b LEFT JOIN items i USING (biblionumber)

Titles by General Materials Designation (MARC 245$h)

  • Developer: Ian Walls, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows each distinct GMD value in the catalog, with a count of titles for that value. Good for profiling materials, and spotting minor spelling errors
  • Status: Completed
SELECT ExtractValue(marcxml, '//datafield[@tag="245"]/subfield[@code="h"]') AS GMD, 
       count(*) AS COUNT
       FROM biblioitems 
       GROUP BY GMD ORDER BY COUNT DESC


Count of items added by cataloger

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Asks for librarian's borrower number and shows them with a count of items they've added. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT count(timestamp) AS 'items added' 
FROM action_logs 
WHERE module='CATALOGUING' AND user=<<Borrower number>> 
      AND info='item' AND action='ADD'


Count of items added by cataloger

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Counts the number of cataloging actions each cataloger performed in a date range. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT concat(p.firstname, ' ', p.surname) AS staff, concat(a.action, ' ', a.info) AS action, count(a.timestamp) AS count 
FROM action_logs a 
LEFT JOIN borrowers p ON (a.user=p.borrowernumber)
WHERE a.module='CATALOGUING' AND a.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND a.info IN ('item','biblio')
GROUP BY p.borrowernumber, concat(a.action, ' ', a.info)

Count of bibs modified by cataloger

  • Developer: Ramiro Uviña
  • Module: Catalog
  • Purpose: Asks for date range and shows you them with a count of bibs they've modified. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT user,count(user) AS 'bibs modified'
FROM action_logs WHERE module='CATALOGUING' AND info LIKE 'BEFORE%' AND action='MODIFY'
AND (timestamp BETWEEN <<Modified BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>)
GROUP BY user

Titles on a particular branch and shelving location

  • Developer: Nicole C. Engard, ByWater Solutions (Posted by Rachel)
  • Module: Catalog
  • Purpose: Creates a list of titles (245a and 245b), authors, and call numbers along with home-branch and library location.
  • Status: Completed
SELECT concat(b.title, ' ', 
       ExtractValue(m.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, i.itemcallnumber 
FROM biblio b 
LEFT JOIN items i USING (biblionumber)
LEFT JOIN biblioitems m USING (biblionumber)
WHERE i.homebranch=<<homebranch|branches>> AND i.location=<<Shelving Location|LOC>>

Language Material Bibs

  • Developer: Chris Cormack, Catalyst and Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List of biblionumbers where the leader says 'language material' and has a specific item type.
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
       AS biblionumber 
FROM biblioitems,
  (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,1) 
   AS leader6 FROM biblioitems) 
AS leaders 
WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND leaders.leader6 = 'a' 
      AND itemtype = <<Item Type|itemtypes>>


Materials based on biblio item type

  • Developer: Nicole C. Engard, ByWater Solutions and Melia Meggs, ByWater Solutions
  • Module: Catalog
  • Purpose: count of the collection by bibliographic item type as cataloged in the fixed fields.
  • Status: Completed
SELECT 
CASE SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,2) 
      WHEN 'am' THEN 'Book'
      WHEN 'as' THEN 'Serial'
      WHEN 'cm' THEN 'Score'
      WHEN 'em' THEN 'Map'
      WHEN 'gm' THEN 'Video recording and motion pictures'
      WHEN 'im' THEN 'Non-music sound recording'
      WHEN 'jm' THEN 'Music sound recording'
      WHEN 'mm' THEN 'Computer file'
      WHEN 'rm' THEN 'Three Dimensional item'
      WHEN 'tm' THEN 'Manuscript'
      ElSE 'unknown' END
   AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems
GROUP BY bibtype

Authors not in the Authorities

  • Developer: MJ Ray, software.coop
  • Module: Catalog
  • Purpose: List of author names found on biblio records but not authority records
  • Status: Production
SELECT DISTINCT(author) AS heading
FROM biblio
WHERE author NOT IN
   (SELECT ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code="a"]')
      AS heading
    FROM auth_header
    WHERE authtypecode='PERSO_NAME')
ORDER BY heading


Terms not in the Authorities

  • Developer: Adapted from a report by Bernardo Gonzalez Kriegel
  • Module: Catalog
  • Purpose: List of terms found on biblio records in 6XX fields that are not in Authorities, with associated biblio numbers.
  • Status: Completed
SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code="a"]') AS heading
FROM biblioitems
WHERE length(ExtractValue(marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(marcxml, '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
ORDER BY heading

Lost Items & Who Lost Them

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report should show all items that are marked lost and who lost them. It's not fool proof, but it's the closest I can get.
  • Status: Complete
SELECT i.itemnumber, i.ccode, b.title, b.author, i.itemcallnumber, 
       i.enumchron, i.itemnotes, i.barcode, v.lib AS 'lost', c.borrowernumber
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN ( SELECT itemnumber, borrowernumber, issuedate, timestamp, returndate
            FROM issues UNION SELECT itemnumber, borrowernumber, issuedate, timestamp,
            returndate FROM old_issues ) c 
     ON (c.itemnumber=i.itemnumber) 
LEFT JOIN statistics s ON (s.itemnumber=i.itemnumber)
WHERE i.itemlost != 0 AND v.category='LOST' AND
      date(s.datetime)=date(c.issuedate) AND s.type='issue'

Basic Item Information By Call Number Range

  • Developer: Jared Camins and Chris Nighswonger
  • Module: Catalog
  • Purpose: This report returns a set of items limited by a range of call numbers. The data included in the result set are: Call Number, Title, Author. A link is provided for easy viewing of the item details.
  • Status: Complete
SELECT CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">View Details</a>' ) AS 'View Details', items.itemcallnumber, biblio.title, biblio.author 
FROM items 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE items.itemcallnumber BETWEEN <<starting call number>> AND <<ending call number>>

Biblio Items without a Koha Item Type

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: Displays the biblionumber, title, and author of biblioitems that do not have an associated Koha Item Type.
  • Status: Complete
SELECT biblio.biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' ) = ""

Bib records added/deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the bibs added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging cataloging actions.
SELECT l.action, count(l.timestamp) AS 'bibs'
FROM action_logs l
LEFT JOIN borrowers p ON (p.borrowernumber=l.user)
WHERE module='CATALOGUING' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
      AND l.info!='item'
GROUP BY l.action


Item records added/deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the items added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging cataloging actions.
SELECT l.action, count(l.timestamp) AS 'items'
FROM action_logs l
LEFT JOIN borrowers p ON (p.borrowernumber=l.user)
WHERE module='CATALOGUING' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
      AND l.info='item'
GROUP BY l.action


Authorities records added/deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the authorities added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging authority actions (AuthoritiesLog system preference activated).
SELECT l.action, count(l.timestamp) AS 'authorities'
FROM action_logs l
LEFT JOIN borrowers p ON (p.borrowernumber=l.user)
WHERE module='AUTHORITIES' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY l.action

All titles with 008 for Continuing Resource

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report lists all titles that are coded as continuing resources in the 008
  • Status: Complete
SELECT b.title, m.issn
FROM biblioitems m 
LEFT JOIN biblio b USING (biblionumber) 
WHERE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),22,1) IN ('d','l','m','n','p','w')

Bibs with Different Item Types

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records that have multiple item types attached.
  • Status: Complete
SELECT b.title, b.biblionumber, count(DISTINCT itype) AS types 
FROM biblio b 
LEFT JOIN items i USING (biblionumber) 
GROUP BY i.biblionumber 
HAVING count(DISTINCT itype) > 1


Author List by Branch

  • Developer: Nick Clemens, VOKAL
  • Module: Catalog
  • Purpose: A list of authors that match search criteria
  • Status: Complete
SELECT  ' ' AS Checkbox, b.title, b.author, i.itemcallnumber, i.barcode
FROM items i
JOIN biblio b USING (biblionumber)
WHERE i.homebranch=<<Branch|branches>> AND 
      b.author LIKE CONCAT(<<Author: Last Name, First Name>>,'%')
ORDER BY b.title

Biblios with like data in a subfield of a field

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: This report displays the Biblionumber, Title, and Author of all Biblios with a specific value in a subfield of a field.
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE ExtractValue(marcxml, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

Mismatches between 2 fields and 2 subfields

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: This report displays the Biblionumber, Title, and Author of all Biblios that Do Not Match 2 sets of fields and subfields.
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 1 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 2 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
NOT LIKE ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 1(XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 2(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")

List all records with at least one subject

  • Developer: David Cook
  • Module: Catalog
  • Purpose: This report creates a list of all records with at least one subject tag. It also lists those subject tags and the biblionumber for the record.
  • Status: Complete
SELECT biblionumber,ExtractValue(marcxml,'//datafield[substring(@tag,1,1) = "6"]/@tag') AS 'Subject Tags' -- ,marcxml
FROM biblioitems
WHERE ExtractValue(marcxml,'//datafield[substring(@tag,1,1) = "6"]/@tag')
ORDER BY `Subject Tags`;


Bibs with diff item types attached

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records that have items with different item types attached.
  • Status: Complete
SELECT b.title, b.author, b.biblionumber, count(DISTINCT i.itype) AS 'item types', 
       count(i.itemnumber) AS items 
FROM biblio b 
LEFT JOIN items i USING (biblionumber) 
GROUP BY b.biblionumber 
HAVING count(DISTINCT i.itype) > 1

Bibs with Series info

  • Developer: Joy Nelson, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records with series info
  • Status: Complete
SELECT i.biblionumber, i.itemnumber, i.barcode, i.itemcallnumber, i.location, i.itype, 
       b.title, b.author, i.enumchron, b.seriestitle, 
       ExtractValue(bi.marcxml,'//datafield[@tag="830"]/subfield[@code="a"]') AS Series 
FROM items i 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN biblioitems bi ON (b.biblionumber=bi.biblionumber)


Percentage of collection by collection code

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Number of items per collection code with the percentage of the total collection
  • Status: Complete
SELECT
   x.ccode AS collection,
   x.allitems AS 'items',
   (x.allitems * 100)/(
     SELECT COUNT(itemnumber) AS 'total'
     FROM items
   ) AS 'Percentage of total collection'
FROM
   (SELECT
     i.ccode,
     COUNT(i.itemnumber) AS 'allitems'
   FROM items i
   GROUP BY i.ccode) x

List records with notes by note tag

  • Developer: David Cook, Prosentient Systems
  • Module: Catalog
  • Purpose: List bibliographic records that have notes fields, and list those note fields by tag number
  • Status: Complete
SELECT biblionumber,ExtractValue(marcxml,'//datafield/@tag/text()[substring(.,1,1) = "5"]') AS 'notes'
FROM biblioitems
HAVING notes <> ''

Accounting Reports (Fines/Credits/Etc)

Fines w/ Patron & Item Info

  • Developer: Kyle M Hall
  • Module: Accounting
  • Purpose: List of unpaid fines with patron and item information
  • Status: Complete
SELECT 
    b.surname, b.firstname, b.email, bib.title, i.barcode,
    a.amountoutstanding, ni.issuedate, ni.date_due, 
    IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate 
FROM accountlines a 
  LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber ) 
  LEFT JOIN items i ON ( a.itemnumber = i.itemnumber ) 
  LEFT JOIN biblio bib ON ( i.biblionumber = bib.biblionumber ) 
  LEFT JOIN ( SELECT * FROM issues UNION SELECT * FROM old_issues ) ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber ) 
WHERE 
    a.amountoutstanding > 0 
GROUP BY a.description
ORDER BY b.surname, b.firstname, ni.timestamp DESC

Patrons w/ Fines

  • Developer: Katrin Fischer
  • Module: Accounting
  • Purpose: List patrons with their fine amounts
  • Status: Complete
SELECT 
    (SELECT CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ', b.firstname,'</a>') 
    FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron, 
    format(sum(amountoutstanding),2) AS 'Outstanding',
    (SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'
FROM 
    accountlines a, borrowers b
WHERE 
    (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber)  > '0.00'
    AND a.borrowernumber = b.borrowernumber
GROUP BY 
    a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC


Patrons w/ Fines at Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: List patrons with their fine amounts limited by branch
  • Status: Complete
SELECT p.surname, p.firstname, p.borrowernumber, p.cardnumber,
format(sum(a.amountoutstanding),2) AS owes
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
WHERE a.amountoutstanding > 0 AND p.branchcode=<<Branch|branches>>
GROUP BY a.borrowernumber

Patrons w/ More Than an Amount in Fines

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: List patrons with who owe greater than or equal to an amount entered when the report is run.
  • Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, p.address, p.address2, p.city, p.state, p.phone, p.branchcode AS 'patron branch',
       p.debarred , p.debarredcomment, p.dateexpiry, format(sum(a.amountoutstanding),2) AS  'amount owed'
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
GROUP BY a.borrowernumber
HAVING sum(a.amountoutstanding) >= <<Owe more than>>
ORDER BY p.surname, p.firstname

Patrons w/ credits

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose:
  • Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, address, city, zipcode, round(Sum(accountlines.amountoutstanding),2) AS 'total owed' 
FROM accountlines LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) WHERE amountoutstanding != 0 
GROUP BY accountlines.borrowernumber HAVING sum(accountlines.amountoutstanding) < 0 
ORDER BY borrowers.surname, borrowers.firstname

Collections Report for Unique Management

  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS
  • Module: Accounting
  • Purpose: Outputs patrons with fines in certain categories, with more than $X in fines, and no fine payments in the last 60 days.
  • Status: Completed
  • Note: Updated by Jared Camins-Esakov, C & P Bibliography Services on 16 May 2012. If you were using a previous version of this report, please update the report and contact Unique immediately to tell them to disregard previous reports. The logic was reversed.
  • Note: This report, with the NOT IN line, eliminates any patron who has *ever* paid on their account - it is too broad and we've removed it from our reports we use at NEKLS now (see the next report for an updated specific version).
  SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, 
  borrowers.address, borrowers.city, borrowers.zipcode, borrowers.email, borrowers.phone,
  borrowers.dateofbirth, borrowers.debarred, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
  FROM borrowers, accountlines 
  WHERE borrowers.categorycode IN ('BONN-CITY', 'OTT-CITY') 
  AND borrowers.borrowernumber 
  NOT IN (SELECT DISTINCT borrowernumber FROM accountlines WHERE accountlines.date < DATE_SUB(CURDATE(),INTERVAL 60 DAY) AND (accountlines.accounttype IN ('PAY', 'C') ) )
  AND borrowers.borrowernumber = accountlines.borrowernumber 
  GROUP BY borrowers.borrowernumber 
  HAVING SUM(accountlines.amountoutstanding) >= 25.00 
  ORDER BY borrowers.surname ASC;
  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
  • Module: Accounting
  • Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
  • Status: Completed
  • Note: New Delinquent Report - first report sent to Unique, sent weekly after that
  • Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT borrowers.cardnumber, borrowers.borrowernumber, borrowers.surname, borrowers.firstname, borrowers.address, borrowers.city, borrowers.zipcode, borrowers.phone, borrowers.mobile, borrowers.phonepro AS "Alt Ph 1", borrowers.B_phone AS "Alt Ph 2", borrowers.branchcode, categories.category_type AS "Adult or Child", borrowers.dateofbirth, MAX(accountlines.date) AS "Most recent charge", FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
LEFT JOIN borrowers USING(borrowernumber) 
LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') 
AND ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL )
AND accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) 
AND accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day) 
GROUP BY borrowers.borrowernumber 
HAVING Due >=25.00 
ORDER BY borrowers.surname ASC


  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
  • Module: Accounting
  • Purpose: Produces report of patrons in particular branch codes with their sort1 field set to "yes" and fines of more than $25.
  • Status: Completed
  • Note: Update report - weekly report sent to Unique
SELECT borrowers.borrowernumber, borrowers.surname, borrowers.firstname, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
LEFT JOIN borrowers USING(borrowernumber) 
LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN (BRANCHCODES SEPARATED BY COMMAS) 
AND borrowers.sort1 = 'yes'
GROUP BY borrowers.borrowernumber 
ORDER BY borrowers.surname ASC
  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
  • Module: Accounting
  • Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
  • Status: Completed
  • Note: New Delinquent Report Linked - used to easily add the $10 fee and set the sort1 field to "yes" for new delinquent accounts
  • Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT CONCAT ('<a href=\"/cgi-bin/koha/members/maninvoice.pl?borrowernumber=',borrowers.borrowernumber,'\" target="_blank">',borrowers.cardnumber,'</a>') AS "Link to Fines",  borrowers.borrowernumber, borrowers.surname, borrowers.firstname, borrowers.address, borrowers.city, borrowers.zipcode, borrowers.phone, borrowers.mobile, borrowers.phonepro AS "Alt Ph 1", borrowers.B_phone AS "Alt Ph 2", borrowers.branchcode, categories.category_type AS "Adult or Child", borrowers.dateofbirth, MAX(accountlines.date) AS "Most recent charge", FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
LEFT JOIN borrowers USING(borrowernumber) 
LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') 
AND ( borrowers.sort1 != 'yes' OR borrowers.sort1 IS NULL )
AND accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) 
AND accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day) 
GROUP BY borrowers.borrowernumber 
HAVING Due >=25.00 
ORDER BY borrowers.surname ASC

Incremental Fines w/ Patron & Item Info

  • Developer: Ramprasad Joshi
  • Module: Accounting
  • Purpose: List of unpaid fines with patron and item information, with an incremental charge: $1 per day the first fortnight overdue, $2 for the next, $5 daily after that; it can be tailored by patron category.
  • Status: One Example
SELECT
    borrowers.cardnumber,borrowers.categorycode,borrowers.surname,issues.date_due,
    (TO_DAYS(curdate())-TO_DAYS( date_due)) AS daysoverdue,
    items.barcode AS 'Accession Number',
    biblio.title,biblio.author,
    IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=15,(TO_DAYS(curdate())-TO_DAYS( date_due)),
      IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=30,2*(TO_DAYS(curdate())-TO_DAYS( date_due))-15,5*(TO_DAYS(curdate())-TO_DAYS( date_due))-105))
     AS fine
  FROM borrowers
  LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
  LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  WHERE (borrowers.categorycode=<<Patron Category|categorycode>>) AND (TO_DAYS(curdate())-TO_DAYS(date_due)) > '0'
  ORDER BY borrowers.cardnumber ASC

Total Forgiven Fines Today

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: Total amount forgiven in fines today
  • Status: Complete


SELECT SUM(amount) 
  FROM accountlines 
  WHERE DATE(timestamp)=CURDATE() AND (accounttype='FOR' OR accounttype='W')

Total Fines Paid Today

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: Total amount paid in fines today
  • Status: Complete


SELECT SUM(amount) 
  FROM accountlines 
  WHERE DATE(timestamp)=CURDATE() AND (accounttype='PAY' OR accounttype='C')

Yesterday's Fines by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: Fines charged yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Fines

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: Fines charged yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND date = (now() - interval 1 day)

Yesterday's Lost Item Charges by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: lost items charged yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Lost Item Charges

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: lost items charged yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day)

Yesterday's Account Management Fees by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: acct mgt charged yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Account Management Fees

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: acct mgt fees charged yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day)

Yesterday's Forgiven Charges by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: forgiven charges yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Forgiven Charges (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: forgiven charges yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day)

Yesterday's Sundry Fees by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: sundry fees yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'M') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Sundry Fees (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: sundry fees charged yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines WHERE (accounttype = 'M') AND date = (now() - interval 1 day)

Yesterday's Credits by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: credits yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'C') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Credits (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: credits yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines WHERE (accounttype = 'C') AND date = (now() - interval 1 day)

Yesterday's New Card Fees by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: new card fees yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'N') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's New Card Fees (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: new card fees yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines WHERE (accounttype = 'N') AND date = (now() - interval 1 day)

Yesterday's Payments by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: payments yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'PAY') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Payments (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: payments yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines WHERE (accounttype = 'PAY') AND date = (now() - interval 1 day)

Year to Date Fines by branch

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: year to date fines charged for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' 
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND YEAR(date) = YEAR(NOW()) AND borrowers.branchcode = 'LIB'

Year to Date Fines (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: year to date fines charged (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' 
FROM accountlines 
WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND YEAR(date) = YEAR(NOW())

Total Fines Owed

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: total amount of fines owed (entire system)
  • Status: Complete


SELECT FORMAT(Sum(accountlines.amountoutstanding),2) FROM accountlines

Writeoff fine (Date Range wise)

  • Developer: Nikunj Tyagi, DPL
  • Module: Accounting
  • Purpose: writeoff Amount (Date range wise) with patron details (entire system)
  • Status: Complete


SELECT borrowers.borrowernumber, borrowers.cardnumber, accountlines.amount, accountlines.date
FROM accountlines, borrowers WHERE borrowers.borrowernumber = accountlines.borrowernumber AND accounttype = 'W' AND date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>

Payment (fine) detail (Date Range)

  • Developer: Nikunj Tyagi, DPL
  • Module: Accounting
  • Purpose: Payment (Date range wise) with patron details (entire system)
  • Status: Complete


SELECT borrowers.borrowernumber, borrowers.cardnumber, accountlines.amount, accountlines.date
FROM accountlines, borrowers WHERE borrowers.borrowernumber = accountlines.borrowernumber AND accounttype = 'pay' AND date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>

Yesterday's Amount Collected (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose: amount actually collected yesterday (entire system)
  • Status: Complete


SELECT round(Sum(accountlines.amount),2) AS 'Paid Yesterday' 
FROM accountlines
WHERE (accounttype = 'PAY' ) AND date = (now() - interval 1 day)

Amount Collected in specific Date Range (entire system)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: Asks you to enter the date range for which you would like to see all of the money collected at all branches.
  • Status: Complete
SELECT FORMAT(abs(sum(amount)),2) AS 'Total Collected' 
FROM accountlines 
WHERE (accounttype='C' OR accounttype='PAY') AND 
      timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>


Accounting for date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: List of all accounting details in date range
  • Status: Complete
SELECT 
CASE accounttype 
      WHEN 'A' THEN 'Account management fee'
      WHEN 'C' THEN 'Credit'
      WHEN 'F' THEN 'Overdue Fine'
      WHEN 'FOR' THEN 'Forgiven'
      WHEN 'FU' THEN 'Overdue Fine Still Accruing'
      WHEN 'L' THEN 'Lost Item'
      WHEN 'LR' THEN 'Lost and Returned'
      WHEN 'M' THEN 'Sundry'
      WHEN 'N' THEN 'New Card'
      WHEN 'PAY' THEN 'Payment'
      WHEN 'W' THEN 'Writeoff'
      ELSE accounttype END  
 AS transaction, SUM(amount)
 FROM accountlines
 WHERE DATE(timestamp) BETWEEN <<Collected BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>
 GROUP BY accounttype

Payments collected at a branch in a date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: Fines collected at a branch in a date range (uses the logged in user's branch)
  • Status: Complete
SELECT FORMAT(abs(sum(a.amount)),2) AS 'Total Collected' 
FROM accountlines a
LEFT JOIN borrowers p ON (a.manager_id=p.borrowernumber)
WHERE a.accounttype IN ('C','PAY') AND 
      a.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND
p.branchcode=<<Branch|branches>>

Statistical reports

Shows the total number of items circulated from a branch other than the owning branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: Shows the total number of items circulated from a branch other than the owning branch
  • Status: Complete
  SELECT count(*) AS total 
  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) 
  WHERE statistics.branch != items.homebranch AND statistics.datetime BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

Patrons with most checkouts in date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: This report will show the top 20 patrons who have checked out the most in a specific time period.
  • Status: Complete
SELECT concat(b.surname,', ',b.firstname) AS name, 
       count(s.borrowernumber) AS checkouts 
FROM statistics s 
LEFT JOIN borrowers b 
USING (borrowernumber) 
WHERE s.datetime BETWEEN <<Top checkouts BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY s.borrowernumber 
ORDER BY count(s.borrowernumber) DESC 
LIMIT 20

New materials added

  • Developer: Sharon Moreland
  • Module: Statistical (Circulation)
  • Purpose: New materials added
  • Status: Complete
  SELECT count(i.biblionumber) AS added, i.itype, i.homebranch, i.location FROM items i 
  WHERE YEAR(i.dateaccessioned) = <<Year accessioned (yyyy)>> AND MONTH(i.dateaccessioned) = <<Month accessioned (mm)>> 
  GROUP BY i.homebranch,i.itype,i.location 
  ORDER BY i.homebranch,i.itype,i.location ASC

Inactive Borrowers

  • Developer: Jonathan Field
  • Module: Statistical (Circulation, Reports)
  • Purpose: List of Borrowers who have not used the library within a given period
  • Status: Complete
SELECT DISTINCT borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.email 
FROM borrowers
WHERE NOT EXISTS (SELECT borrowernumber FROM statistics WHERE borrowers.borrowernumber = borrowernumber AND statistics.datetime >= 'YYYY-MM-DD')


Number of links clicked in the last month

  • Developer: Liz Rea, Catalyst IT for Albany Senior High School, New Zealand
  • Module: Statistical
  • Purpose: Count of links clicked in the last month
  • Status: Complete
SELECT count(*) FROM linktracker WHERE timeclicked > DATE_SUB(now(), INTERVAL 1 MONTH)

List of links clicked in the last month

  • Developer: Liz Rea, Catalyst IT for Albany Senior High School, New Zealand
  • Module: Statistical
  • Purpose: List of links clicked in the last month
  • Status: Complete
SELECT count(url) AS 'times', url FROM linktracker WHERE timeclicked > DATE_SUB(now(), INTERVAL 1 MONTH) GROUP BY url

Statistic for daily catalogers achievement in date range for bib records

  • Developer: Karam Qubsi
  • Module: Cataloging
  • Purpose: Statistic for daily catalogers achievement in date range for bib records (you can change the date range I make it for the whole 2014 year in this example )
  • Status: Complete
SELECT DATE_FORMAT(I.timestamp, '%d-%c-%Y') AS Date,
B.userid AS Staff,
count(I.timestamp) AS Count
FROM action_logs I
LEFT JOIN borrowers B
ON I.user=B.borrowernumber
WHERE I.module='CATALOGUING' AND I.action='ADD' AND date(I.timestamp) BETWEEN '2014-01-01' AND '2014-12-31' 
 AND I.info='biblio'
GROUP BY Date,B.userid
ORDER BY DATE(timestamp) DESC

Statistic for daily catalogers achievement in date range for Item records

  • Developer: Karam Qubsi
  • Module: Cataloging
  • Purpose: Statistic for daily catalogers achievement in date range for item records (you can change the date range I make it for the whole 2014 year in this example )
  • Status: Complete
SELECT DATE_FORMAT(I.timestamp, '%d-%c-%Y') AS Date,
B.userid AS Staff,
count(I.timestamp) AS Count
FROM action_logs I
LEFT JOIN borrowers B
ON I.user=B.borrowernumber
WHERE I.module='CATALOGUING' AND I.action='ADD' AND date(I.timestamp) BETWEEN '2014-01-01' AND '2014-12-31' 
 AND I.info='item'
GROUP BY Date,B.userid
ORDER BY DATE(timestamp) DESC

Notices Reports

Overdue Notices Sent

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Notices
  • Purpose: Count of overdue notices sent in a specific time frame (by type). Uses the following codes for overdue messages: ODUE, ODUE2, ODUE3. Edit notice names as necessary.
  • Status: Complete
SELECT monthname(message_queue.time_queued) AS month, year(message_queue.time_queued) AS year, 
       message_queue.letter_code AS notice, count(message_queue.borrowernumber) AS count 
FROM message_queue 
WHERE message_queue.time_queued BETWEEN <<Sent BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND (message_queue.letter_code = 'ODUE' OR message_queue.letter_code = 'ODUE2' 
      OR message_queue.letter_code = 'ODUE3') AND STATUS = 'sent' 
GROUP BY year(message_queue.time_queued), month(message_queue.time_queued), message_queue.letter_code


Acquisition Reports

Orders in Date Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show order information for a specific date period.
  • Status: Complete. Broken in Koha 3.16. aqorders.notes field was replaced with aqorders.order_internalnote and aqorders.order_vendornote
SELECT v.name AS vendor, b.title AS 'book title', 
       format(o.listprice,2) AS 'list price', 
       format(o.unitprice,2) AS 'actual price',
       ba.basketname, o.notes 
FROM aqorders o 
LEFT JOIN aqbasket ba USING (basketno) 
LEFT JOIN aqbooksellers v ON (v.id = ba.booksellerid) 
LEFT JOIN biblio b USING (biblionumber) 
WHERE o.entrydate BETWEEN <<Ordered BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

All Orders

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show the information for every order in the system.
  • Status: Complete
SELECT v.name AS Vendor, concat(c.basketname, ' (', c.basketno, ')') AS Basket,
       c.creationdate AS Ordered,  concat(p.firstname, ' ', 
       p.surname) AS 'Managed by', b.title, 
       ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS subtitle, 
       b.author, bi.isbn, bi.publishercode, 
       o.rrp AS RRP, o.ecost AS EST, o.quantity AS Qty,
       format(o.listprice*o.quantity,2) AS Total, f.budget_name AS Fund 
FROM aqorders o 
LEFT JOIN aqbudgets f USING (budget_id) 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN biblioitems bi USING (biblionumber) 
LEFT JOIN aqbasket c USING (basketno) 
LEFT JOIN aqbooksellers v ON (c.booksellerid=v.id) 
LEFT JOIN borrowers p ON (c.authorisedby=p.borrowernumber) 
ORDER BY v.name, c.basketno ASC


Ledger

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show's a ledger of all items ordered
  • Status: Complete. Works on Koha 3.16. Changed biblioitemnumber to biblionumber in the join on items.
SELECT b.name AS vendor, i.itype, p.budget_branchcode AS branch, k.basketno,  
       o.entrydate AS 'order date', format(o.listprice,2) AS 'list price', 
       format(o.unitprice,2) AS 'unit price', o.quantity, 
       format(o.totalamount,2) AS 'total amount', o.datereceived AS 'date received' 
FROM aqbasket k 
LEFT JOIN aqbooksellers b ON (k.booksellerid=b.id) 
LEFT JOIN aqorders o USING (basketno)
LEFT JOIN items i USING (biblionumber)
LEFT JOIN aqbudgets p USING (budget_id)

Titles ordered in a Fund

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Titles ordered in a specific fund (will search for any part of the fund code)
  • Status: Complete
SELECT b.title, b.author 
FROM biblio b 
LEFT JOIN aqorders a 
USING (biblionumber) 
LEFT JOIN aqbudgets aq 
USING (budget_id) 
WHERE a.datereceived BETWEEN <<Date received BETWEEN (yyyy-mm-dd)|date>> 
          AND <<and (yyyy-mm-dd)|date>> AND
          aq.budget_code LIKE concat(<<Budget code>>,'%')

Amount Encumbered

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Total encumbered against each budget
  • Status: Complete
SELECT b.budget_name, format(sum(b.budget_amount),2) AS 'amount budgeted',
          format(sum(o.listprice*o.quantity),2) AS 'amount encumbered' 
FROM aqorders o 
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NULL 
GROUP BY b.budget_name

Amount Spent

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Total spent against each budget
  • Status: Complete
SELECT b.budget_name, format(sum(b.budget_amount),2) AS 'amount budgeted',
          format(sum(o.listprice*o.quantity),2) AS 'amount spent' 
FROM aqorders o 
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NOT NULL 
GROUP BY b.budget_name

Tax Receipt

  • Developer: Paul A., Naval Marine Archive
  • Module: Acquisitions
  • Purpose: To allow a Charity to produce an "inventory" for Tax Receipting purposes; selection by Donor and Accession date.
  • Status: Complete
SELECT
items.barcode AS Barcode,items.dateaccessioned AS 'Acc Date', 
biblio.title AS Title, biblio.author AS Author,biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Year, 
biblioitems.editionstatement AS Edition,
items.price AS FMV,
ExtractValue(more_subfields_xml,
'/collection/record/datafield[\@tag=\"999\"]/subfield[\@code=\"x\"]') AS 'Condition'
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE
items.booksellerid = <<Pick your donor>>
AND DATE(items.dateaccessioned) BETWEEN <<Accessioned BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
 
ORDER BY biblio.author ASC

Orders with like data in a subfield of a field

  • Developer: Joseph Alway
  • Module: Acquisitions
  • Purpose: Displays the Biblionumber, Title, Author, Basket Name, List Price, Budget Code, and Vendor of All Orders with a like value in a subfield of a field.
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author, aqbasket.basketname AS PO, format(aqorders.listprice,2) AS 'List Price', aqbudgets.budget_code AS Fund, aqbooksellers.name AS Vendor
FROM biblioitems
JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
LEFT JOIN aqorders ON (biblioitems.biblionumber = aqorders.biblionumber)
LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id)
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
WHERE ExtractValue(marcxml, "//datafield[@tag=<<Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") 
LIKE <<Search Term (USE % AS wildcard)>> 
AND biblio.biblionumber = aqorders.biblionumber

Serial reports

Shows the total serial received during the month

  • Developer: Nikunj Tyagi, DPL
  • Module: Serial
  • Purpose: Shows the total serials received with Title, Frequency, latest issue detail
  • Status: Complete
  SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate,
IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19)))
AS FREQUENCY  FROM serial, biblio,biblioitems
WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND  MONTH(planneddate) = 03 AND YEAR(planneddate)= 2011 AND (STATUS)=2
ORDER BY serial.subscriptionid ASC

missing/late/claimed serial during the month

  • Developer: Nikunj Tyagi, DPL
  • Module: Serial
  • Purpose: Shows the total serials missing/late/claimed with Title, Frequency, latest issue detail status 3=late,4=missing,5=claimed
  • Status: Complete
  SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate, 
IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '', 
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19, 
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19))) 
AS FREQUENCY,serial.STATUS  FROM serial, biblio,biblioitems
WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND  MONTH(planneddate) = XX AND YEAR(planneddate)= XXXX AND (STATUS) BETWEEN '3' AND '5'
ORDER BY serial.subscriptionid ASC

Late Issues

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Serials
  • Purpose: A list of items that should have arrived by now
  • Status: Complete
SELECT b.title, s.serialseq, s.planneddate 
FROM serial s LEFT JOIN biblio b USING (biblionumber) 
WHERE s.planneddate < CURDATE()

Latest Issues

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Serials
  • Purpose: A list of the latest issue received for each subscription
  • Status: Complete
SELECT b.title, b.biblionumber, MAX(CONCAT(s.publisheddate, ' / ',s.serialseq)) AS 'date and enumeration' 
FROM serial s 
LEFT JOIN biblio b USING (biblionumber) 
WHERE s.STATUS=2 
GROUP BY b.biblionumber 
ORDER BY s.publisheddate DESC


Issues Received in a Range

  • Developer: Nicole C. Engard, ByWater Solutions with MJ Ray
  • Module: Serials
  • Purpose: A list of your serials and the year range you have received
  • Status: Complete
SELECT b.title, i.issn, 
      CONCAT(YEAR(MIN(s.publisheddate)), ' to ', YEAR(MAX(s.publisheddate))) AS 'range' 
FROM serial s
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN biblioitems i USING (biblionumber)
WHERE s.STATUS=2
GROUP BY b.biblionumber
ORDER BY b.title ASC


Year range of serials holdings

  • Developer: Paul Landers
  • Module: Serials
  • Purpose: Year range of all serial subscription titles by holding branch
  • Status: Complete
SELECT s.biblionumber,
c.issn AS ISSN,i.holdingbranch,b.title AS Title,
ExtractValue(c.marcxml,'//datafield[@tag="222"]/subfield[@code>="a"]') AS 'MARC
222 Title',
YEAR(MIN(s.publisheddate)) AS 'begin',
YEAR(MAX(s.publisheddate)) AS 'end' 
FROM serial s,items i,biblio b, biblioitems c
WHERE s.itemnumber=i.itemnumber  
AND i.biblionumber=b.biblionumber 
AND b.biblionumber=c.biblionumber 
AND i.holdingbranch = <<Branch|branches>>
GROUP BY b.biblionumber
ORDER BY b.title

Fix Subscriptions without Subscription Start Dates

  • Developer: Barton Chittenden
  • Module: Serials
  • Purpose: Fix serials that generate the following error message: Software error: Date::Calc::Add_Delta_YM(): not a valid date at /var/lib/koha/aarome/kohaclone/C4/Serials.pm line 2651.
  • Status: Complete
SELECT 
    CONCAT(
        '<a href="http://',
        (SELECT value 
         FROM systempreferences 
         WHERE variable='staffClientBaseURL'),
        '/cgi-bin/koha/serials/subscription-history.pl?subscriptionid=',
        subscriptionid,
        '">', title, '</a>'
    ) AS 'Edit Subscription for',
    subscriptionid,
    startdate,
    histstartdate
FROM
    subscription
    INNER JOIN biblio USING (biblionumber)
    LEFT JOIN subscriptionhistory USING (subscriptionid) 
    LEFT JOIN serial USING (subscriptionid) 
WHERE
    startdate IS NULL
    OR histstartdate IS NULL
GROUP BY subscriptionid

Misc Reports

List of Lists

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Lists
  • Purpose: Show all lists with their creator
  • Status: Complete
SELECT b.borrowernumber, b.surname, b.firstname, 
       s.shelfname 
FROM virtualshelves s 
LEFT JOIN borrowers b 
ON (b.borrowernumber=s.owner)

Backup/Share OPAC Layout from system preferences

  • Developer: Liz Rea, NEKLS
  • Module: Administration
  • Purpose: Dump the contents of all of the OPAC Interface user input customization preferences, for backup or sharing of layout/CSS
  • Status: Complete
SELECT variable, value 
FROM systempreferences 
WHERE variable IN ('OPACUserCSS', 'opacuserjs', 'OPACResultsSidebar', 'OPACNoResultsFound', 'OpacNav', 'opaccredits','opacheader', 'OpacMainUserBlock')

OCLC Number Lookup

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging
  • Purpose: Look up OCLC Number by barcode.
  • Status: Complete

This report assumes that OCLC Numbers are stored in MARC 001 field.

SELECT
    biblionumber
  , ExtractValue( marcxml, '//controlfield[@tag=\"001\"]' ) AS 'OCLC Number'
  , title 
FROM biblioitems 
INNER JOIN biblio USING (biblionumber) 
INNER JOIN items USING (biblionumber) 
WHERE barcode=<<barcode>>

Troubleshoot accidental checkout when action logs are disabled

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Notices
  • Purpose: Troubleshoot accidental checkouts
  • Status: Complete

This report takes a date range of notice queue times, and prints CHECKIN, CHECKOUT and RENEWAL notices ordered by borrowernumber and time queued. If you see cclusters of CHECKOUT, RENEWAL and CHECKIN notices within the same minute for the same borrower, this is an indication of accidental check-out.

Circulation action logs are a better way to monitor this, but they are often disabled for performance reasons.

SELECT 
   borrowernumber, 
   letter_code,
   time_queued,
   content 
FROM message_queue 
WHERE
date(time_queued) BETWEEN <<Notice Queued BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
AND letter_code IN ('CHECKIN', 'RENEWAL', 'CHECKOUT') 
ORDER BY borrowernumber, time_queued

Find all character encodings in MARCXML records

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging
  • Purpose: List all character encodings
  • Status: Complete
SELECT 
    count(*),
    CASE Mid( 
        ExtractValue(marcxml, '//leader'), 
        9, 
        1
    ) 
    WHEN ' ' THEN 'MARC8'
    WHEN 'a' THEN 'UTF8'
    ELSE CONCAT( 
        'Unknown encoding type: ', 
        Mid(
            ExtractValue(marcxml, '//leader'),
            9,
            1
        )
    )
    END AS encoding
FROM biblioitems 
WHERE Mid(
                ExtractValue(marcxml, '//leader'),
                9,
                1
        ) IS NOT NULL
GROUP BY Mid(
                ExtractValue(marcxml, '//leader'),
                9,
                1
        )

According to the MARC21 standard, the 'Leader' character position 9 determines character encoding: '#' for MARC8, 'a' for UTF8. Koha uses ' ' instead of '#' for MARC8.

WISHLIST

Requester Module Purpose of request SQL Request Notes
Sunil Acquisitions To Eliminate duplicate orders. I want to write a Sql Query to eliminate duplicate orders from selected vender. Please help me in this query.
Sunil Acquisitions List All Late Orders I want to write a Sql Query to List All Late Orders, vendor-wise. Please help me in this query.
Arron Birch Catalog To create a report that pulls individual fields of a MARC record I am trying to run reports of specific fields of a MARC record. Preferable I would like a general report that lets me change what field I would like to run a report for. For the current assignment I am wanting to run a report with the 300 field of the MARC record. (Have a look at the report "Biblios with like data in a subfield of a field". Is that what you want?)
Rachel Hollis Catalog Mismatches between biblioitem 942 and item 952 We think there is value in a report that identifies (by title, call number and biblio ID) records that have item mismatches, specific to our situation are 942 subfields 2 & c and 952 subfields 2 & y. Our Koha 3.01 biblio item loans are controlled by the 942. Additionally we have libraries that use Dewey, LC and locally developed classification schemes. Administration and System Preferences allow for static and variable data that can get mismatched.

Check the following report to see, if that is what you need. Mismatches between 2 fields and 2 subfields: This report displays the Biblionumber, Title, and Author of all Biblios that Do Not Match 2 sets of fields and subfields.

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 1 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 2 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
NOT LIKE ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 1(XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 2(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
Joe Tholen Circulation List items not circulated in last year, by shelf location, using old_issues and issues For migrated libraries to weed with.
Scotty Zollars Cataloging List all records with NULL in the source of acquistion field in the item record within a date range.
SELECT barcode, homebranch, title, dateaccessioned, booksellerid
FROM items, biblio
WHERE items.biblionumber = biblio.biblionumber
AND items.dateaccessioned
BETWEEN  <<Enter date from>>
AND  <<Enter date to>>
AND booksellerid IS NULL
ORDER BY barcode
Scotty Zollars Circulation List interlibrary loan materials check out to other libraries, by day. For ILL record keeping
Susan Bennett Catalog I need to eliminate materials that are on the holds shelf waiting for patron pick up from the following SQL. What is the flag in the record?
SELECT items.barcode, items.homebranch, items.itemcallnumber, items.holdingbranch, 
items.location, items.ccode, items.onloan, biblio.author, biblio.title 
FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.holdingbranch="GW" AND items.homebranch<>items.holdingbranch AND items.onloan IS NULL 
ORDER BY items.holdingbranch ASC
Scotty Zollars Circulation We are only one branch. Our interlibrary loan patrons are community patrons. They have the last name of ILL and the first name of the library, for example Erie Public Library. I need a list of interlibrary loan materials check out to other libraries, by month. i have the following donated so far.
SELECT  monthname(datesent) month,COUNT(*) 
FROM branchtransfers WHERE frombranch="MMM" AND YEAR(datesent)=YEAR(NOW())-1 
GROUP BY month 
ORDER BY month(datesent)
Rachel Hollis Circulation Compare number of items owned by library with number circulating Count of items currently checked out
SELECT COUNT( itemnumber ) 
FROM  issues
Personal tools