SQL Reports Circulation

From Koha Test Wiki MediaWiki Postgres

This is the page that relates to circulation reports.

SQL Reports Holds | SQL Reports Patrons | SQL Reports Main Library

Circulation Reports

General Statistics

Circulation Counts by collection code (date range)

Developer: Tamara Biggio (posted by: Ed Veal)
Module: Circulation | Statistical
Purpose: Three columns of circ counts (checkouts/renewals) with All circs for multiple branches. Date range included.
Status: Completed
IMPORTANT Branch values are library specific.

SELECT av.lib AS 'Collection Code',  COUNT(s.itemnumber) AS 'ALL Circs',
    COUNT(CASE WHEN s.branch='JJGL' THEN 1 ELSE NULL END) AS JJGL, COUNT(CASE WHEN s.branch='RHHL' THEN 1 ELSE NULL END) AS HALL, COUNT(CASE WHEN s.branch='ORV' THEN 1 ELSE NULL END) AS Olive
FROM statistics s 
   LEFT JOIN authorised_values av ON (av.authorised_value=s.ccode) AND av.category =  'CCODE'
WHERE s.type IN ('issue', 'renew')
AND s.datetime BETWEEN <<between (date)|date>> AND <<and|date>>
GROUP BY av.lib
WITH ROLLUP

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

All Circ Transactions on Date with Patron & Item Details

Developer: Caroline Cyr La Rose, inLibro
Module: Circulation
Purpose: Gives you a list of all circulation transaction on a specific date, with patron information (cardnumber & name), transaction type and amount (if any), and item information (barcode, title & author)
Example:
Status: Complete

SELECT
datetime AS "Date",
cardnumber AS "Card number",
surname AS "Last name",
firstname AS "First name",
CASE type
WHEN 'issue' THEN "Check out"
WHEN 'localuse' THEN "In house use"
WHEN 'return' THEN "Check in"
WHEN 'renew' THEN "Renew"
WHEN 'writeoff' THEN "Amnesty"
WHEN 'payment' THEN "Payment"
ELSE "Other" END
AS "Transaction",
CASE value
WHEN '0' THEN "-"
ELSE value END
AS "Amount",
barcode AS "Barcode",
biblio.title AS "Title",
author AS "Author"
FROM statistics
JOIN borrowers ON statistics.borrowernumber=borrowers.borrowernumber
LEFT JOIN items ON statistics.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE DATE(statistics.datetime)=<<Date|date>>

All Circulation Transactions on Date Range with Patron & Item Details

Developer: Jameela P.
Module: Circulation
Purpose: Gives you a list of all circulation transaction on a specific date range, with patron information (cardnumber & name), transaction type and amount (if any), and item information (barcode, title & author)
Example:
Status: Complete
Reference: Caroline Cyr La Rose, inLibro's 'All Circ Transactions on Date with Patron & Item Details'

SELECT
datetime AS "Date",
cardnumber AS "Card number",
surname AS "Last name",
firstname AS "First name",
CASE type
WHEN 'issue' THEN "Check out"
WHEN 'localuse' THEN "In house use"
WHEN 'return' THEN "Check in"
WHEN 'renew' THEN "Renew"
WHEN 'writeoff' THEN "Amnesty"
WHEN 'payment' THEN "Payment"
ELSE "Other" END
AS "Transaction",
CASE value
WHEN '0' THEN "-"
ELSE value END
AS "Amount",
barcode AS "Barcode",
biblio.title AS "Title",
author AS "Author",
items.homebranch, 
items.holdingbranch
FROM statistics
JOIN borrowers ON statistics.borrowernumber=borrowers.borrowernumber
LEFT JOIN items ON statistics.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE DATE (statistics.datetime) BETWEEN <<From Date|date>> AND <<To Date|date>>

Circ Transaction Counts

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

All Circ Actions on Date

Developer: Nicole C. Baratta, 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 in Date Range

Developer: Caroline Cyr La Rose, inLibro
Module: Circ
Purpose: Based on Nicole's query, gives you stats for all circ actions during a date range
Status: Complete

SELECT 
CASE type 
      WHEN 'issue' THEN "Check outs"
      WHEN 'return' THEN "Check ins"
      WHEN 'renew' THEN "Renewals"
      WHEN 'writeoff' THEN "Amnesties"
      WHEN 'payment' THEN "Payments"
      ELSE "Others" END  
 AS "Transaction types", COUNT(datetime) AS "Quantity"
 FROM statistics
 WHERE DATE(datetime) BETWEEN <<Between|date>> AND <<and|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. Baratta, 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

Find bibs circulated, select date range

Developer: Nick Clemens, ByWater Solutions
Module: Circulation
Purpose: Finds all the circulated biblios for the given timeperiod. Also includes circulations for deleted items and biblios. Displays a list of columns for each biblio. Basically mocks above report but less intensive and appropriate to run within Koha.
Status: Complete

SELECT IFNULL(b.biblionumber,db.biblionumber),
       IFNULL(bi.isbn,dbi.isbn),
       IFNULL(bi.ean,dbi.ean) AS 'EAN',
       IFNULL(b.author,db.author) ,
       IFNULL(b.title,db.title) AS 'Title',
       IFNULL(bi.editionstatement,dbi.editionstatement) AS 'Edition',
       IFNULL(b.copyrightdate,db.copyrightdate) AS 'Copydate',
       ityp.description,
       IFNULL(i.itemcallnumber,di.itemcallnumber),       
       COUNT(s.itemnumber) AS 'Count of items'
FROM statistics s
LEFT JOIN items i USING (itemnumber)
LEFT JOIN deleteditems di USING (itemnumber)
LEFT JOIN biblio b ON IFNULL(i.biblionumber,di.biblionumber) = b.biblionumber
LEFT JOIN deletedbiblio db ON IFNULL(i.biblionumber,di.biblionumber) = db.biblionumber
LEFT JOIN biblioitems bi ON IFNULL(i.biblionumber,di.biblionumber) = bi.biblionumber
LEFT JOIN deletedbiblioitems dbi ON IFNULL(i.biblionumber,di.biblionumber) = dbi.biblionumber
LEFT JOIN itemtypes ityp ON ityp.itemtype = IFNULL(i.itype,di.itype)
WHERE  s.type IN ('issue', 'renew') AND
       s.datetime BETWEEN <<Start date|date>> AND <<End date|date>>
GROUP BY i.biblionumber

Circulation counts by biblio for a given duration, takes deleted items and biblios into account

Developer: Olli-Antti Kivilahti, Vaara-kirjastot
Module: Circulation
Purpose: Finds all the circulated biblios for the given timeperiod. Also includes circulations for deleted items and biblios. Displays a list of columns for each biblio, including extracted second value from a space-separated itemcallnumber acting as a triple. Since the report is rather intensive and cannot be ran from the reporting module, no Koha-placeholders are set.
Status: Complete

SELECT b.biblionumber AS 'Tietue id',
       bi.isbn AS 'ISBN',
       bi.ean AS 'EAN',
       b.author AS 'Tekijä',
       b.title AS 'Nimeke',
       bi.editionstatement AS 'Painos',
       b.copyrightdate AS 'Julkaistu',
       ityp.description,
       LEFT(
           SUBSTRING_INDEX(
               SUBSTRING_INDEX(i.itemcallnumber, ' ',  -2),
           ' ', 1),
           LOCATE('.',
               SUBSTRING_INDEX(
                   SUBSTRING_INDEX(i.itemcallnumber, ' ',  -2),
               ' ', 1))+2
       ) AS 'pääluokka',
       COUNT(s.itemnumber) AS 'Lainoja'
FROM   statistics s LEFT JOIN
       (SELECT itemnumber, biblionumber, itype, itemcallnumber
        FROM items
        UNION
        SELECT itemnumber, biblionumber, itype, itemcallnumber
        FROM deleteditems
       ) AS i ON s.itemnumber = i.itemnumber LEFT JOIN
       (SELECT biblionumber, title, author, copyrightdate
        FROM biblio
        UNION
        SELECT biblionumber, title, author, copyrightdate
        FROM deletedbiblio
       ) AS b ON i.biblionumber = b.biblionumber LEFT JOIN
       (SELECT biblionumber, isbn, ean, editionstatement
        FROM biblioitems
        UNION
        SELECT biblionumber, isbn, ean, editionstatement
        FROM deletedbiblioitems
       ) AS bi ON i.biblionumber = bi.biblionumber LEFT JOIN
       itemtypes ityp ON ityp.itemtype = i.itype
WHERE  s.type IN ('issue', 'renew') AND
       s.datetime >= '2015-01-01 00:00:00' AND s.datetime <= '2015-12-31 23:59:59'
GROUP BY i.biblionumber
INTO OUTFILE '/tmp/SanastoLainaustapahtumat.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Department wise transactions of Issued & Returned books with specific date range

Developer: Sher Afzal Khan (President Koha Pakistan Community (kohapaksitan@gmail.com)
Module: Circulation
Purpose: Finds the departments wise transactions of all patrons with specific date range
Status: Complete

SELECT
datetime AS "Date",
cardnumber AS "Card number",
surname AS "Last name",
firstname AS "First name",
CASE type
WHEN 'issue' THEN "Check out"
WHEN 'localuse' THEN "In house use"
WHEN 'return' THEN "Check in"
WHEN 'renew' THEN "Renew"
WHEN 'writeoff' THEN "Amnesty"
WHEN 'payment' THEN "Payment"
ELSE "Other" END
AS "Transaction",
CASE value
WHEN '0' THEN "-"
ELSE value END
AS "Amount",
barcode AS "Barcode",
sort1 AS "borrowers.sort1",
biblio.title AS "Title",
author AS "Author",
items.homebranch, 
items.holdingbranch
FROM statistics
JOIN borrowers ON statistics.borrowernumber=borrowers.borrowernumber
LEFT JOIN items ON statistics.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE DATE (statistics.datetime) BETWEEN <<From Date|date>> AND <<To Date|date>>

Circulation in Time

Hourly

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

Track In House Use Hourly

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

Daily

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

Weekly

Issues Details for a Selected Branch for the Week To Date

Developer: Chris Nighswonger (Foundations Bible College & Theological Seminary)
Module: Circulation
Purpose: lists all issues for the selected branch for the current week beginning on Sunday
Example:
Status: Complete

SELECT c.date_due, p.surname, p.firstname, b.title, b.author
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 >= curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
      AND i.homebranch = <<Branch|branches>>
ORDER BY c.date_due ASC

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

Monthly

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

Month-wise Circulation Report for a Given Year: By Branch And Cumulative

Developer: David Kane, Waterford Institute of Technology
Module: Circulation
Purpose: Generate a monthly statistical report for all itemtypes, per branch, with totals for any given year

SELECT DISTINCT 
  CONCAT('<strong>',s.type,'</strong>')       AS ' ', 
  CONCAT('<em>',b.branchname,'</em>')       AS 'BRANCH', 
  SUM(IF(MONTHNAME(s.datetime) = 'January',  1, 0)) AS 'January',
  SUM(IF(MONTHNAME(s.datetime) = 'February',   1, 0)) AS 'February',
  SUM(IF(MONTHNAME(s.datetime) = 'March',    1, 0)) AS 'March', 
  SUM(IF(MONTHNAME(s.datetime) = 'April',    1, 0)) AS 'April',
  SUM(IF(MONTHNAME(s.datetime) = 'May',    1, 0)) AS 'May',
  SUM(IF(MONTHNAME(s.datetime) = 'June',     1, 0)) AS 'June',
  SUM(IF(MONTHNAME(s.datetime) = 'July',     1, 0)) AS 'July',
  SUM(IF(MONTHNAME(s.datetime) = 'August',   1, 0)) AS 'August',
  SUM(IF(MONTHNAME(s.datetime) = 'September',  1, 0)) AS 'September',
  SUM(IF(MONTHNAME(s.datetime) = 'October',  1, 0)) AS 'October',
  SUM(IF(MONTHNAME(s.datetime) = 'November',   1, 0)) AS 'November',
  SUM(IF(MONTHNAME(s.datetime) = 'December',   1, 0)) AS 'December',
  COUNT(*)            AS 'TOTAL',
  CONCAT('<strong>',s.type,'</strong>')       AS ' '
FROM  statistics s, branches b
WHERE s.branch = b.branchcode
AND s.type IN ('renew', 'return', 'issue') 
AND YEAR(s.datetime)=@CurrentYear:=<<year>> 
GROUP BY  b.branchname, s.type 
UNION 
SELECT 
  NULL AS ' ', 
  NULL AS 'BRANCH', 
  NULL AS 'January',
  NULL AS 'February',
  NULL AS 'March', 
  NULL AS 'April',
  NULL AS 'May',
  NULL AS 'June',
  NULL AS 'July',
  NULL AS 'August',
  NULL AS 'September',
  NULL AS 'October',
  NULL AS 'November',
  NULL AS 'December',
  NULL AS 'TOTAL', 
  NULL AS ' '
UNION 
SELECT DISTINCT 
  CONCAT('<strong>',c.type,'</strong>')       AS ' ',  
  '<em>ALL BRANCHES</em>'         AS 'BRANCH', 
  SUM(IF(MONTHNAME(c.datetime) = 'January',  1, 0)) AS 'January',
  SUM(IF(MONTHNAME(c.datetime) = 'February',   1, 0)) AS 'February',
  SUM(IF(MONTHNAME(c.datetime) = 'March',    1, 0)) AS 'March', 
  SUM(IF(MONTHNAME(c.datetime) = 'April',    1, 0)) AS 'April',
  SUM(IF(MONTHNAME(c.datetime) = 'May',    1, 0)) AS 'May',
  SUM(IF(MONTHNAME(c.datetime) = 'June',     1, 0)) AS 'June',
  SUM(IF(MONTHNAME(c.datetime) = 'July',     1, 0)) AS 'July',
  SUM(IF(MONTHNAME(c.datetime) = 'August',   1, 0)) AS 'August',
  SUM(IF(MONTHNAME(c.datetime) = 'September',  1, 0)) AS 'September',
  SUM(IF(MONTHNAME(c.datetime) = 'October',  1, 0)) AS 'October',
  SUM(IF(MONTHNAME(c.datetime) = 'November',   1, 0)) AS 'November',
  SUM(IF(MONTHNAME(c.datetime) = 'December',   1, 0)) AS 'December',
  CONCAT('<strong>',COUNT(*),'</strong>')     AS 'TOTAL', 
  CONCAT('<strong>',c.type,'</strong>')       AS ' '
FROM  statistics c 
WHERE c.type IN ('renew', 'return', 'issue') 
AND YEAR(c.datetime) = @CurrentYear 
GROUP BY c.type

COUNT - Month wise circulation report for a specific year

Developer: (1) Satisha MV - Govt. Engineering College Library, Hassan (2)Barton Chittenden (3) Mark Alexander
Module: Circulation
Purpose:
Status: Complete

SELECT
      monthname(datetime) AS month, 
      SUM( IF(type = 'issue', 1, 0 )) AS Issues,
      SUM( IF(type = 'renew', 1, 0 )) AS Renewals,
      SUM( IF(type = 'return', 1, 0 )) AS Returns,
      COUNT(statistics.type) AS 'Total Transactions'
FROM  statistics
WHERE YEAR(datetime) = <<Enter Year YYYY>> 
GROUP BY month
ORDER BY month(datetime) ASC

COUNT - Date wise daily circulation report for a specific month of an year

Developer: (1) Satisha MV - Govt. Engineering College Library, Hassan (2)Barton Chittenden (3) Mark Alexander
Module: Circulation
Purpose:
Status: Complete

SELECT
      DATE(datetime) AS DATE, 
      SUM( IF(type = 'issue', 1, 0 )) AS Issues,
      SUM( IF(type = 'renew', 1, 0 )) AS Renewals,
      SUM( IF(type = 'return', 1, 0 )) AS Returns,
      COUNT(statistics.type) AS 'Total Transactions'
FROM  statistics
WHERE YEAR(datetime) = <<Enter Year YYYY>> AND  MONTH(datetime) = <<Enter Month MM>>
GROUP BY DATE(datetime)

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;

Monthly circ in a date range

Developer: Kyle Hall
Module: Circulation
Purpose: Includes items that have since been deleted grouped by month
Status: Complete

SELECT MONTHNAME(all_issues.issuedate) AS MONTH,
       YEAR(all_issues.issuedate) AS YEAR,
       count(*) AS checkouts,
       SUM(all_issues.renewals) AS renewals
FROM (
        (SELECT *
         FROM issues
         WHERE branchcode=<<Library|branches>>
           AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
      UNION
        (SELECT *
         FROM old_issues
         WHERE branchcode=<<Library|branches>>
           AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
    ) all_issues
LEFT JOIN items USING ( itemnumber )
LEFT JOIN deleteditems USING ( itemnumber )
GROUP BY
    YEAR(all_issues.issuedate),
    MONTH(all_issues.issuedate)
ORDER BY
    YEAR(all_issues.issuedate),
    MONTH(all_issues.issuedate)

Checked In

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

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)

Patron notes about item, shown at check in, within a date range

Developer: Barton Chittenden, ByWater Solutions
Module: Circulation
Purpose: To view issue IDs, patron, items, the checkin notes they've written, and the note's date within a date range.
Status: Complete

SELECT
coalesce( issues.issue_id, old_issues.issue_id) AS issue_id,
CONCAT(borrowers.surname,', ',borrowers.firstname) AS Name,
items.itemnumber,
coalesce(issues.note, old_issues.note) AS Note,
coalesce(issues.notedate, old_issues.notedate) AS Date
FROM issues
LEFT JOIN old_issues USING(itemnumber)
LEFT JOIN items USING(itemnumber)
LEFT JOIN borrowers ON (borrowers.borrowernumber=coalesce(issues.borrowernumber, old_issues.borrowernumber))
WHERE date(coalesce(issues.notedate, old_issues.notedate)) BETWEEN <<note written ON OR after date|date>> AND <<and before|date>>
GROUP BY issue_id

Checked Out

All Checked Out Books

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

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

Patrons w/ Books Due Tomorrow

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

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

List the items circulated from a branch

Developer: Jameela P.
Module: Circulation
Purpose: Shows the total list of items circulated from a branch.
Status: Completed

SELECT
  borrowers.cardnumber AS "ID",
  borrowers.title AS "RANK",
  borrowers.surname AS "NAME",
  borrowers.sort1 AS "TERM",
  borrowers.othernames AS "PERSONAL NUMBER",
  borrowers.city AS "RANK 1",
  borrowers.sort2 AS "SQN/DEPT",
  biblio.title AS "TITLE",
  biblio.author AS "AUTHOR",
  items.itemcallnumber AS "CALL NO",
  items.barcode AS "BARCODE",
  items.homebranch AS "HOME LIBRARY",
  issues.issuedate AS "DATE OF ISSUE"
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
  items.homebranch = <<Branch|branches>>
ORDER BY
  issues.branchcode,
  "TERM",
  "NAME",
  "DATE OF ISSUE",
  "TITLE"

List of items checked out in a date range

Developer: Nicole Engard and Christopher Brannon
Module: Circulation
Purpose: List of items checked out in a date range
Status: Complete

SELECT b.title, b.author, i.itemcallnumber, i.barcode, c.issuedate
FROM (SELECT iss.issuedate, iss.itemnumber FROM issues iss, 
(SELECT @StartDate:=<<Between (yyyy-mm-dd)|date>>,@EndDate:=<<and (yyyy-mm-dd)|date>>) AS var 
WHERE date(iss.issuedate) BETWEEN @StartDate AND @EndDate UNION ALL 
SELECT oi.issuedate, oi.itemnumber FROM old_issues oi WHERE date(oi.issuedate) 
BETWEEN @StartDate AND @EndDate) AS c
LEFT JOIN items i USING (itemnumber)
LEFT JOIN biblio b USING (biblionumber)
WHERE i.homebranch=<<Item belongs TO|branches>>

List of items issued on a date with optional limit by due date

Developer: Sarah Cornell, Portsmouth Public Library
Module: Circulation
Purpose: Locates items with known checkout dates and/or due dates and links to patron for troubleshooting.
Status: Completed
Works with: 18.05

SELECT 
issuedate, 
date_due, 
items.barcode, 
items.itemcallnumber, 
biblio.title, 
biblio.author, 
CONCAT(borrowers.firstname, ' ',borrowers.surname) AS 'patron', 
borrowers.cardnumber, 
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowers.borrowernumber,'\"target="_blank">', borrowers.borrowernumber, '</a>') AS borrowernumber
FROM issues
LEFT JOIN items ON issues.itemnumber = items.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
WHERE DATE(issuedate) = <<issue date|date>>
AND IF(<<Limit BY due date?|YES_NO>>,DATE(date_due) = <<due date|date>>,1)

Items Checked out by Patron from Different Home Library

Developer: Spencer Smith
Module: Circulation
Purpose: List of items currently checked out, limited by borrowers' home branch, items' home branch, and issuing branch.
Status: Complete

SELECT issues, biblio.title, borrowers.branchcode AS 'borrower home',items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate, issues.branchcode AS 'check out branch', items.homebranch AS 'item home location'
  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 items.homebranch=<<item home branch|branches>> 
  AND borrowers.branchcode=<<borrower home branch|branches>> 
  AND issues.branchcode=<<checkout branch|branches>> 
  ORDER BY items.itemcallnumber, biblio.title

Circulation by Location

By Call Number

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

Count of Circ by Alpha Call Number Prefix

Developer: Jared Camins-Esakov and Nicole C. Baratta, 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)

Circulation of Two Call Numbers

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

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)

Circulation grouped by Dewey decimal range, select date range

Developer: Barton Chittenden
Module: Circulation
Purpose: Total circulation activities in a date range
Status: Complete

SELECT
    CONCAT(
        ( LEFT(i.cn_sort, 3) DIV 100 ) * 100,
        ' - ',
        ( LEFT(i.cn_sort, 3) DIV 100 ) * 100 + 99
    ) AS 'Callnuber range',
    COUNT(checkouts.datetime) AS checkouts,
    COUNT(renewals.datetime) AS renewals,
    COUNT(localuse.datetime) AS localuse,
    COUNT(checkouts.datetime) + COUNT(renewals.datetime) AS total
FROM
    items i
    LEFT JOIN statistics s USING (itemnumber)
    LEFT JOIN statistics checkouts ON (checkouts.type = 'issue' AND checkouts.itemnumber=i.itemnumber AND s.datetime=checkouts.datetime )
    LEFT JOIN statistics renewals ON (renewals.type = 'renew' AND renewals.itemnumber=i.itemnumber AND s.datetime=renewals.datetime )
    LEFT JOIN statistics localuse ON (localuse.type = 'localuse' AND localuse.itemnumber=i.itemnumber AND s.datetime=localuse.datetime )
WHERE
    i.cn_source = 'ddc'
    AND date(s.datetime) BETWEEN <<circulated BETWEEN|date>> AND <<and|date>>
GROUP BY ( LEFT(i.cn_sort, 3) DIV 100 ) * 100
ORDER BY ( LEFT(i.cn_sort, 3) DIV 100 ) * 100

By Shelving Location

Checkout by Shelving Location

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

Circ in a date range by shelving location

Developer: Kyle Hall
Module: Circulation
Purpose: Includes items that have since been deleted and will ask if you want to limit by location or not, grouped by month
Status: Complete

SELECT COALESCE(items.location, deleteditems.location) AS LOCATION, MONTHNAME(all_issues.issuedate) AS MONTH,
       YEAR(all_issues.issuedate) AS YEAR,
       count(*) AS checkouts,
       SUM(all_issues.renewals) AS renewals
FROM (
        (SELECT *
         FROM issues
         WHERE branchcode=<<Library|branches>>
           AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
      UNION
        (SELECT *
         FROM old_issues
         WHERE branchcode=<<Library|branches>>
           AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
    ) all_issues
LEFT JOIN items USING ( itemnumber )
LEFT JOIN deleteditems USING ( itemnumber )
WHERE
    IF( <<limit BY location|YES_NO>>, COALESCE( items.location, deleteditems.location ) = <<location|LOC>>,1 )
GROUP BY
    COALESCE( items.location, deleteditems.location),
    YEAR(all_issues.issuedate),
    MONTH(all_issues.issuedate)
ORDER BY
    COALESCE( items.location, deleteditems.location),
    YEAR(all_issues.issuedate),
    MONTH(all_issues.issuedate)

Circulation stats, choose date range, shelving location, itemtype

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
        bib.title,
        i.itype,
        i.location,
        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
        statistics s
        LEFT JOIN items i USING (itemnumber)
        LEFT JOIN biblio bib USING (biblionumber)
WHERE
        date(s.datetime) BETWEEN <<Between|date>> AND <<and|date>>
        AND i.itype = <<Item type|itemtypes>>
        AND i.location = <<Location|loc>>
GROUP BY biblionumber

Local Use

Track In House Use

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

Local Use by Shelving Location

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

Naughty Overdues

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

Overdues w/ Contact Info

Developer: Nicole C. Baratta, 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. Baratta, 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. Baratta, 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. Baratta, 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>> AND c.date_due < curdate()
ORDER BY p.surname ASC, c.date_due ASC

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)

List of current checked out books; branch wise

Developer: Jameela P.; Panini Library
Module: Circulation
Purpose: To generate branch wise current checked out books list
Status: Complete

SELECT  borrowers.cardnumber, borrowers.title, borrowers.surname,  borrowers.sort1, borrowers.othernames, biblio.title AS "TITLE", author AS "AUTHOR", items.itemcallnumber AS "CALL NO", items.barcode AS "BARCODE", items.homebranch AS "HOME LIBRARY", issues.issuedate AS "DATE OF ISSUE"
 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 items.homebranch =<<Branch|branches>> 
  ORDER BY issues.branchcode, borrowers.sort1, borrowers.surname, issues.issuedate, biblio.title

List of current checked out books; branch wise within a date range

Developer: Jameela P.; Panini Library
Module: Circulation
Purpose: To generate branch wise current checked out books list within a date range.
Status: Complete

SELECT  borrowers.cardnumber, borrowers.title, borrowers.surname,  borrowers.sort1, borrowers.othernames, biblio.title AS "TITLE", author AS "AUTHOR", items.itemcallnumber AS "CALL NO", items.barcode AS "BARCODE", items.homebranch AS "HOME LIBRARY", issues.issuedate AS "DATE OF ISSUE"
 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 items.homebranch =<<Branch|branches>>  AND 
date(issuedate) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY itemcallnumber, barcode

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

Overdues at a Specific Branch

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

Weeding / Low Circulating Items

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

Not Circulated items

Developer: Abdullrahman Hegazy, BUE Library
Module: Circulation
Purpose: List of low or not circulated records, Considering the number of items in the records and the adding date of the items.
Status: Ongoing

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',items.biblionumber,'\">',biblio.title,'</a>') AS Record, COUNT(items.biblionumber) AS "Num of items", items.itemcallnumber
FROM items
LEFT JOIN
        (
        SELECT DISTINCT issues.itemnumber
        FROM issues
 
        UNION
 
        SELECT DISTINCT old_issues.itemnumber
        FROM old_issues
 
        UNION
 
        SELECT DISTINCT statistics.itemnumber
        FROM statistics
        ) AS inumber ON ( items.itemnumber = inumber.itemnumber)
LEFT JOIN biblio ON ( items.biblionumber = biblio.biblionumber)
WHERE inumber.itemnumber IS NULL
AND items.itype = <<Item type|itemtypes>>
AND (items.itemlost < 1 OR items.itemlost IS NULL)
AND dateaccessioned < <<Adding date Before (yyyy-mm-dd)|date>>
GROUP BY Record
ORDER BY COUNT(items.biblionumber) DESC, items.itemcallnumber ASC

Non Circulating Items

Developer: Nicole C. Baratta, 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. Baratta, 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)>>

Non Circulating Items in X Years for a specific branch also lists other items at other branches and their status

Developer: Brenda Turnbull LiveWire CIC
Module: Circulation
Purpose: List items that have not circulated in X Years for a specifcic branch then lists all other copies held at other branches and their status. It selection is resctricted to certain Locations and item types (where AF and JF are Adult Fiction/Junior Fiction) - which you will need to alter to your values.
Status: Complete **** WARNING ***** Run with care as it may slow down your database

SELECT b.title AS Title,
       b.author AS Author,
       b.copyrightdate AS CopyrightDate,
       i.itemcallnumber AS CallNo,
       i.itype AS ItemType,
       i.homebranch AS Branch,
       i.location AS Loc,
       i.biblionumber AS BibNo,
       i.barcode AS BarcodeNo,
       i.datelastborrowed AS lastBorrowed,
       i.issues AS totalCheckouts,
       i.dateaccessioned AS Accessioned,
       (SELECT GROUP_CONCAT( ItemInnerDets.ItemDetails ORDER BY ItemInnerDets.ItemDetails DESC SEPARATOR '</br>')
            FROM
           (SELECT itemsInner.biblionumber, itemsInner.homebranch, itemsInner.datelastborrowed, itemsInner.barcode,
                   CONCAT(itemsInner.homebranch,' ',itemsInner.barcode,' ', itemsInner.location,
                   (CONCAT( 
                       (IF (itemsInner.withdrawn > 0, ' withdrawn ', 
                           (IF (itemsInner.itemlost > 0, ' missing ', 
                           (IF (itemsInner.damaged > 0 , ' damaged ', ' ')))
                           )
                           )
                        ),
                       (IF(itemsInner.onloan IS NULL,' Available ' , ' OnLoan ')), 
                       (IF (itemsInner.notforloan > 0, ' REF item ',' '))
                       )),
                   ' last borrowed:  ',itemsInner.datelastborrowed,' No. issues: ',  itemsInner.issues) AS ItemDetails 
            FROM items itemsInner ) ItemInnerDets
         WHERE ItemInnerDets.biblionumber = i.biblionumber AND  ItemInnerDets.barcode <> i.barcode  ORDER BY ItemInnerDets.ItemDetails  
         )  AS Also_Available_AT
FROM items i
LEFT JOIN biblio b USING (biblionumber)                 
 WHERE YEAR(NOW())-YEAR(i.datelastborrowed) > <<Years NOT circulated>>  AND i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues)
 AND i.itype IN ('AF','JF')
 AND i.location IN ('A', 'BEGIN-READ','F','JSPN','FC','G','SFG','W','H','X','LP','P','PROA','PRO-ANF','PROJ','R','SF','JSSC','Y','T','TC')

Non/Low Circulating New items Items for a selected Branch

Developer: Brenda Turnbull LiveWire CIC
Module: Circulation
Purpose: Selects items that have been received into a branch in the last 12 months. It ignores items that are currently on loan or in transit. It shows items that have had only 1 issue or none at all.
Status: Complete **** WARNING ***** Run with care as it may slow down your database

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS 'View Bib Item detail',
     i.homebranch, b.title, b.author, i.barcode,i.itype, i.location, a.datereceived ,i.datelastseen, 
   ( SELECT Count(statistics.itemnumber) FROM statistics 
          WHERE statistics.itemnumber = i.itemnumber AND statistics.type IN ('issue','renew') ) AS circs
  FROM items i
  LEFT JOIN biblioitems bi USING (biblioitemnumber)
  LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
  LEFT JOIN aqorders a ON (i.biblionumber=a.biblionumber)
  LEFT JOIN branchtransfers bt ON ( i.itemnumber = bt.itemnumber)
  WHERE  DATE( a.datereceived  ) >= DATE_SUB(CURDATE(),INTERVAL 18 MONTH) AND  DATE( a.datereceived  )  <= DATE_SUB(CURDATE(),INTERVAL 6 MONTH)       
   AND NOT (DATE(bt.datesent) <= CURDATE() AND bt.datearrived IS NULL)
   AND   (SELECT Count(statistics.itemnumber) FROM statistics 
          WHERE  statistics.itemnumber = i.itemnumber AND statistics.type IN ('issue','renew'))  < 2
        AND i.itype =  <<Item type|itemtypes>>  AND  i.homebranch = <<Branch|branches>>
        AND i.notforloan = 0
        AND i.holdingbranch = i.homebranch
         AND i.onloan IS NULL
GROUP BY b.title

Items with no Circs in a specific timeframe

Developer: Nicole C. Baratta 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>>)

Items Added before a given date with either no circs or last circ before a given date, by collection code

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Circulation
Purpose: Titles never checked out or last checked out before a given date
Status: Complete

SELECT itemnumber, items.barcode, items.itemcallnumber, biblio.title, biblio.author, items.issues, lu.localuse, items.datelastborrowed, items.ccode, items.damaged, items.itemlost, items.withdrawn
FROM items 
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) LEFT JOIN (SELECT itemnumber, count(datetime) AS localuse FROM statistics WHERE type='localuse' GROUP BY itemnumber) lu USING (itemnumber)
WHERE items.ccode=<<Collection|CCODE>> AND items.dateaccessioned <= <<Item Acquired Before (yyyy-mm-dd)|date>> AND (items.datelastborrowed <= <<Last CKO before (yyyy-mm-dd)|date>> OR items.issues IS NULL)
ORDER BY items.itemcallnumber ASC

Claims Returned

List of Unresolved Claims

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Circulation
Purpose: List all unresolved Claims Returned claims
Status: Complete

SELECT barcode, biblio.title, author, itemcallnumber, cardnumber, surname, firstname, created_on, return_claims.notes
FROM return_claims LEFT JOIN items USING (itemnumber) LEFT JOIN biblio USING (biblionumber) LEFT JOIN borrowers USING (borrowernumber)
WHERE resolution IS NULL
ORDER BY itemcallnumber

List of Unresolved Claims on Checked-In Items

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Circulation
Purpose: List all unresolved Claims Returned claims on items that are currently checked in
Status: Complete

SELECT barcode, biblio.title, author, itemcallnumber, cardnumber, surname, firstname, created_on, return_claims.notes
FROM return_claims LEFT JOIN items USING (itemnumber) LEFT JOIN biblio USING (biblionumber) LEFT JOIN borrowers USING (borrowernumber)
WHERE resolution IS NULL AND onloan IS NULL
ORDER BY itemcallnumber

Others

Number of Checkouts by Branch

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

Items filtered by branch, collection code and last checkout date

Developer: Barton Chittenden, ByWater Solutions
Module: Circulation
Purpose: Based on the built in 'Home › Reports › Items with no checkouts'. Items last checked out before a given date, filtered by Collection Code and Item home branch.
Status: Complete

SELECT *
FROM (
    SELECT
    @homebranch := <<Home branch|branches>>  COLLATE utf8_unicode_ci   AS barcode,
    @ccode  := <<Collection Code|CCODE>> COLLATE utf8_unicode_ci  AS branch,
    @lastcheckout  := <<Last checked out before|date>> COLLATE utf8_unicode_ci  AS itemcallnumber,
    0  AS title,
    0  AS biblionumber,  
    0  AS author
    ) AS `set variables`
WHERE 0 = 1
UNION
SELECT
    items.barcode        AS barcode,
    items.homebranch     AS branch,
    items.itemcallnumber AS itemcallnumber,
    biblio.title         AS title,
    biblio.biblionumber  AS biblionumber,
    biblio.author        AS author
FROM
    items
    LEFT JOIN biblio     USING (biblionumber)
    LEFT JOIN (
        SELECT
            itemnumber,
            max( issuedate ) AS issuedate
        FROM
            ( SELECT * FROM issues
                UNION
              SELECT * FROM old_issues ) AS all_issues
        GROUP BY itemnumber
    ) AS last_checkout USING (itemnumber)
WHERE       
        items.homebranch = @homebranch
    AND items.ccode = @ccode
    AND date( last_checkout.issuedate ) < @lastcheckout

Circ by Literary Form

Developer: Nicole C. Baratta, 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. Baratta, 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 AND s.type IN ('issue','renew')
GROUP BY b.biblionumber 
ORDER BY circs DESC 
LIMIT 10

Top 10 Circulating Books in Date Range (by Collection Code and Item Type)

Developer: Alex Chen, Butte County Library
Module: Circulation
Purpose: Top 10 circulated books in a given date range, with Collection Code and Item Type selections.
Status: Complete

/* Count checkout & renewal, filter by collection code. */
 
SELECT
	biblio.title,
	biblio.author,
	items.ccode,
	items.location,
	items.itype,
	COUNT(statistics.itemnumber) AS "checkout&renewal"
FROM statistics
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
WHERE
	statistics.datetime BETWEEN <<Transaction happened BETWEEN |date>> AND <<and |date>>
	AND statistics.type IN ('issue', 'renew')
	AND items.ccode = <<Collection Code |ccode>>
GROUP BY biblio.title
ORDER BY COUNT(statistics.itemnumber) DESC
LIMIT 10
 
/* Count checkout & renewal, filter by item type. */
 
SELECT
	biblio.title,
	biblio.author,
	items.ccode,
	items.location,
	items.itype,
	COUNT(statistics.itemnumber) AS "checkout&renewal"
FROM statistics
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
WHERE
	statistics.datetime BETWEEN <<Transaction happened BETWEEN |date>> AND <<and |date>>
	AND statistics.type IN ('issue', 'renew')
	AND items.itype = <<Item Type |itemtypes>>
GROUP BY biblio.title
ORDER BY COUNT(statistics.itemnumber) DESC
LIMIT 10
 
/*With slight modification, report can be changed to only count checkout or renewal numbers*/

Average Checkouts

Developer: Katrin Fischer and Nicole C. Baratta, 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. Baratta, 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

Percentage of circs by collection code

Developer: Nicole C. Baratta, 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
;

Checkouts by Item Type (date range)

Developer: Georgia Katsarou
Module: Circ
Purpose: It will show checkouts per item type in that period of time.
Status: Complete

SELECT  COALESCE(i.itype,di.itype) AS "Item Type" ,count(*) AS 'Checkouts' 
FROM statistics 
LEFT JOIN deleteditems di USING (itemnumber)
LEFT JOIN items i USING (itemnumber)
WHERE statistics.type = 'issue' 
AND DATE(datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND type IN ('issue','renew') 
GROUP BY  COALESCE(i.itype,di.itype)

Checkouts by Item Type (date range) - Grouped by Branch

Developer: Alex Chen, Butte County Library
Module: Circulation
Purpose: Report shows number of checkouts by by item type in a date range, grouped by branch, and the number of items held by the branches at the time.
Status: Complete

/*Code can be simplified, but readability and details were prioritized over code simplicity and performance.*/
 
SELECT
CONCAT('') AS 'Branch',
CONCAT('') AS 'Data'
UNION
SELECT
CONCAT('BRANCH'),
CONCAT('NO. OF ISSUES (CURRENT ITEMS)')
UNION
SELECT
	items.homebranch,
	COUNT(*)
FROM statistics
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber)
WHERE
	items.itype = (@ItemTypeVar:= <<Item Type |itemtypes>>) COLLATE utf8mb4_unicode_ci
	AND statistics.datetime BETWEEN (@StartDateVar:=<<Checkout date BETWEEN |date>>) AND (@EndDateVar:=<<and |date>>)
	AND statistics.type = 'issue'
GROUP BY items.homebranch ASC
UNION
SELECT
CONCAT('BRANCH'),
CONCAT('NO. OF CURRENT ITEMS')
UNION
SELECT
	items.homebranch,
	COUNT(DISTINCT items.itemnumber)
FROM items
WHERE
	items.itype = @ItemTypeVar COLLATE utf8mb4_unicode_ci
	AND items.dateaccessioned <= @EndDateVar
GROUP BY items.homebranch ASC
UNION
SELECT
CONCAT('BRANCH'),
CONCAT('NO. OF ISSUES (DELETED ITEMS)')
UNION
SELECT
	deleteditems.homebranch,
	COUNT(*)
FROM statistics
LEFT JOIN deleteditems ON (statistics.itemnumber = deleteditems.itemnumber)
WHERE
	deleteditems.itype = @ItemTypeVar COLLATE utf8mb4_unicode_ci
	AND statistics.datetime BETWEEN @StartDateVar AND @EndDateVar
	AND statistics.type = 'issue'
GROUP BY deleteditems.homebranch ASC
UNION
SELECT
CONCAT('BRANCH'),
CONCAT('NO. OF DELETED ITEMS')
UNION
SELECT
	deleteditems.homebranch,
	COUNT(DISTINCT deleteditems.itemnumber)
FROM deleteditems
WHERE
	deleteditems.itype = @ItemTypeVar COLLATE utf8mb4_unicode_ci
	AND deleteditems.dateaccessioned <= @EndDateVar
GROUP BY deleteditems.homebranch ASC

Circ in a date range by item type

Developer: Kyle Hall
Module: Circulation
Purpose: Includes items that have since been deleted and will ask if you want to limit by item type or not, grouped by month
Status: Complete

SELECT COALESCE(items.itype, deleteditems.itype) AS ITEMTYPE, MONTHNAME(all_issues.issuedate) AS MONTH,
YEAR(all_issues.issuedate) AS YEAR,
count(*) AS checkouts,
SUM(all_issues.renewals) AS renewals
FROM (
(SELECT *
FROM issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
UNION
(SELECT *
FROM old_issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
) all_issues
LEFT JOIN items USING ( itemnumber )
LEFT JOIN deleteditems USING ( itemnumber )
WHERE
IF( <<limit BY itemtype|YES_NO>>, COALESCE( items.itype, deleteditems.itype ) = <<itemtype|itemtypes>>,1 )
GROUP BY
COALESCE( items.itype, deleteditems.itype ),
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)
ORDER BY
COALESCE( items.itype, deleteditems.itype ),
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)

Circ in a date range by patron category

Developer: Nicole Engard, ByWater Solutions
Module: Circulation
Purpose: Includes patrons that have since been deleted and will ask if you want to limit by patron category or not
Status: Complete

SELECT COALESCE(borrowers.categorycode, deletedborrowers.categorycode) AS Category, MONTHNAME(all_issues.issuedate) AS MONTH,
YEAR(all_issues.issuedate) AS YEAR,
count(*) AS checkouts,
SUM(all_issues.renewals) AS renewals
FROM (
(SELECT *
FROM issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
UNION
(SELECT *
FROM old_issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
) all_issues
LEFT JOIN borrowers USING ( borrowernumber )
LEFT JOIN deletedborrowers USING ( borrowernumber )
WHERE
IF( <<limit BY category|YES_NO>>, COALESCE( borrowers.categorycode, deletedborrowers.categorycode ) = <<category|categorycode>>,1 )
GROUP BY
COALESCE( borrowers.categorycode, deletedborrowers.categorycode ),
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)
ORDER BY
COALESCE( borrowers.categorycode, deletedborrowers.categorycode ),
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)

Patron Circulation History

Developer: Caroline Cyr La Rose, inLibro
Module: Circulation, Patrons
Purpose: Same as Circulation History table in Patron file, but you can export or print this one
Status: Complete

SELECT
  all_issues.timestamp AS 'Date',
  biblio.title AS 'Title',
  biblio.author AS 'Author',
  items.itemcallnumber AS 'Call no',
  items.barcode AS 'Barcode',
  all_issues.renewals AS 'Nunmber of Renewals',
  all_issues.issuedate AS 'Checked out on',
  branches.branchname AS 'Checked out from',
  all_issues.date_due AS "Date due",
  (CASE WHEN all_issues.returndate IS NULL THEN 'Prêté' ELSE all_issues.returndate END) AS 'Return date'
FROM (SELECT * FROM issues UNION SELECT * FROM old_issues) all_issues
LEFT JOIN items ON items.itemnumber = all_issues.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
LEFT JOIN borrowers ON all_issues.borrowernumber = borrowers.borrowernumber
LEFT JOIN branches ON all_issues.branchcode = branches.branchcode
WHERE borrowers.cardnumber = <<Patron barcode>>

Patron Circulation History on date & patron range

Developer: Jameela P.; Panini Library
Module: Circulation, Patrons
Purpose: Same as Circulation History on a range of patrons and date
Status: Complete

SELECT
  borrowers.cardnumber, borrowers.title, borrowers.surname, borrowers.sort1, borrowers.state, borrowers.othernames, borrowers.streetnumber, borrowers.sort2, borrowers.city, all_issues.timestamp AS 'XXX',
  biblio.title AS 'Title',
  biblio.author AS 'Author',
  items.itemcallnumber AS 'Call no',
  items.barcode AS 'Barcode',
 items.homebranch AS 'Home Branch', items.holdingbranch AS 'Current Location',
  all_issues.renewals AS 'Nunmber of Renewals',
  all_issues.issuedate AS 'Checked out on',
  branches.branchname AS 'Checked out from',
  all_issues.date_due AS "Date due",
  (CASE WHEN all_issues.returndate IS NULL THEN 'Prêté' ELSE all_issues.returndate END) AS 'Return date'
FROM (SELECT * FROM issues UNION SELECT * FROM old_issues) all_issues
LEFT JOIN items ON items.itemnumber = all_issues.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
LEFT JOIN borrowers ON all_issues.borrowernumber = borrowers.borrowernumber
LEFT JOIN branches ON all_issues.branchcode = branches.branchcode
WHERE borrowers.cardnumber BETWEEN <<From Mem. ID>> AND <<Mem. ID)>>  AND all_issues.issuedate BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

Circulation rules across all branches

Developer: Barton Chittenden, ByWater Solutions
Module: Circulation, Patrons
Purpose: Show circulation rules across all branches
Status: Complete

SELECT 
    issuingrules.branchcode,
    issuingrules.*,
    itemtypes.description AS humanitemtype,
    categories.description AS humancategorycode,
    COALESCE( localization.translation, itemtypes.description ) AS translated_description
FROM issuingrules
    LEFT JOIN itemtypes
        ON (itemtypes.itemtype = issuingrules.itemtype)
    LEFT JOIN categories
        ON (categories.categorycode = issuingrules.categorycode)
    LEFT JOIN localization ON issuingrules.itemtype = localization.code
        AND localization.entity = 'itemtypes'
        AND localization.lang = 'EN'
ORDER BY issuingrules.branchcode

Circulation rule helper

Developer: Barton Chittenden, ByWater Solutions
Module: Circulation, Patrons
Purpose: Look up information needed to determine which circ rule applies
Status: Complete

SELECT
    'patron' AS 'type',
    CONCAT( firstname, ' ', surname ) AS name,
    cardnumber AS barcode,
    categorycode AS `category or itype`
FROM borrowers 
WHERE cardnumber = <<Patron cardnumber>>
UNION
SELECT
    'item' AS 'type',
    biblio.title AS 'name',
    barcode AS barcode,
    itype AS  `category or itype`
FROM
    borrowers
    INNER JOIN issues USING (borrowernumber)
    INNER JOIN items USING (itemnumber)
    LEFT JOIN biblio USING (biblionumber)
WHERE cardnumber = <<Patron cardnumber>>
UNION
SELECT
    'syspref' AS 'type',
    variable AS name,
    value AS barcode,
    '' AS `category or itype`
FROM systempreferences
WHERE
    variable IN ( 'CircControl', 'ReservesControlBranch', 'HomeOrHoldingBranch' )

Find popular items without replacement price

Developer: Barton Chittenden, BWS
Module: Circulation
Purpose: Finds all items circulated in the last year which have no replacement price set. Report shows the most circulated items at the top. This allows replacement prices to be added before items are marked lost.
Status: Complete

SELECT
    count(*),
    itemcallnumber,
    title,
    author,
    barcode
FROM
    statistics
    INNER JOIN items USING (itemnumber)
    INNER JOIN biblio USING (biblionumber)
WHERE
    date(datetime) >= DATE_SUB(CURRENT_DATE, interval 1 year)
    AND type='issue'
    AND items.replacementprice IS NULL
GROUP BY
    itemnumber
ORDER BY
    count(*) DESC

Yesterday's Hotspot Usage

Developer: Barton Chittenden, ByWater Solutions
Module: Circulation
Purpose: Show Usage of Hot Spots
Status: Complete

SELECT i.itemcallnumber AS "PC_Id",
    p.cardnumber AS "Patron_Id",
    o.issuedate AS "Start_Time",
    o.returndate AS "Stop_Time",
    o.branchcode AS "Branch"
    (HOUR(o.returndate) * 60) + MINUTE(o.returndate) 
    - ( (HOUR(o.issuedate) * 60) +  MINUTE(o.issuedate) ) AS "Session_Length"
FROM old_issues o
LEFT JOIN items i USING (itemnumber)
LEFT JOIN borrowers p USING (borrowernumber)
WHERE
      i.itype=<<Item type of hotspot|itemtypes>>
      AND date(o.returndate) = DATE_SUB(CURRENT_DATE, interval 1 day)
ORDER BY o.branchcode

Sum of Replacement Costs for Issued Items In Date Range

Developer: Christofer Zorn
Module: Circulation / Marketing
Purpose: Lists the sum totals of the replacement costs for all items issued within a date range and groups them by borrower number. Can be used for Marketing purposes. Shows the cost savings of using the Library by user.
Status: Complete

SELECT 
  statistics.borrowernumber AS BorrowerNumber,
  sum(items.replacementprice) AS AmountSavedUsingLibrary
FROM
  statistics
INNER JOIN items USING (itemnumber)
WHERE
  date(statistics.datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
  AND statistics.type = 'issue'
GROUP BY statistics.borrowernumber
ORDER BY AmountSavedUsingLibrary DESC

Checkouts per Unique User During a Time Frame

Developer: Spencer Smith
Module: Circulation / Patrons
Purpose: Returns the total number of unique users who checked out an item in a given time frame, the total number of items checked out in that same time frame, and the average checked out per unique user- grouped by checkout branch.
Status: Complete

SELECT  
     Count(DISTINCT s.borrowernumber) AS 'Borrowers',
    COUNT(s.itemnumber) AS 'Total Checkouts', s.branch, ((COUNT(s.itemnumber))/(Count(DISTINCT s.borrowernumber))) AS 'Average Checkouts'
 
FROM statistics s 
LEFT JOIN borrowers p ON (p.borrowernumber=s.borrowernumber)
LEFT JOIN authorised_values av ON (av.authorised_value=s.ccode)
WHERE s.type IN ('issue') 
AND s.datetime BETWEEN <<Start date|date>> AND <<End date|date>>
 
GROUP BY s.branch

Article requests created in a timeframe grouped by status

Developer: Lucy Vaux-Harvey
Module: Circulation
Purpose: gives counts of article requests created in a partcular timeframe grouped by request status.
Status: Complete

SELECT STATUS, count(*) AS Total 
FROM article_requests
WHERE date(created_on) BETWEEN <<Request created date BETWEEN |date>> AND <<and |date>>
GROUP BY STATUS

Checkouts in date range of items with "Something" in the subject headings, by homebranch

Developer: Caroline Cyr La Rose
Module: Circulation / statistics
Purpose: to know the usage of a particular subject (650$a), in each branch. Switch out "Something" for the subject heading you're looking for
Status: Complete

SELECT
    items.homebranch AS "Item homebranch",
    count(*) AS "Checkouts"
FROM
    statistics
    LEFT JOIN items USING (itemnumber)
WHERE 
    DATE(datetime) BETWEEN <<Between |date>> AND <<and |date>>
    AND type IN ('issue', 'renew')
    AND biblionumber IN (
                          SELECT 
                              biblionumber
                          FROM
                              biblio_metadata
                          WHERE 
                              extractvalue(metadata, '//datafield[@tag="650"]/subfield[@code="a"]') LIKE "%Something%"
                          )
GROUP BY items.homebranch

Transfers

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. Baratta, 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)

Items to be moved, select date range and other branch circulation threshold

Developer: Barton Chittenden (Bywater Solutions)
Module: Circulation
Purpose: Find books checked out more often at remote branches.
Status: Complete

SELECT
    itemcallnumber,
    title,
    barcode,
    homebranch,
    count(*) - count(rco.datetime) AS homebranch_circs,
    count(rco.datetime) AS other_branch_circs,
    count(rco.datetime) - (count(*) - count(rco.datetime)) AS 'circ_difference'
FROM
    statistics co
    LEFT JOIN items USING (itemnumber)
    LEFT JOIN biblio USING (biblionumber)
    LEFT JOIN statistics rco ON
        co.itemnumber = rco.itemnumber
        AND co.borrowernumber = rco.borrowernumber
        AND co.datetime = rco.datetime
        AND rco.branch != homebranch
WHERE
    items. ccode= <<Item collection code|CCODE>> 
    AND date( co.datetime ) BETWEEN  <<between (date)|date>> AND <<and|date>>
    AND co.type = 'issue'
GROUP BY
    items.itemnumber
HAVING 
    other_branch_circs > homebranch_circs+<<With more than this many circs at other branches>>

SQL Reports Circulation

From Koha Test Wiki MediaWiki Postgres

Contents

Circulation Reports

General Statistics

Circulation Counts by collection code (date range)

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

All Circ Transactions on Date with Patron & Item Details

All Circulation Transactions on Date Range with Patron & Item Details

Circ Transaction Counts

All Circ Actions on Date

All Circ Actions in Date Range

All Circ Actions on Patron categories and Date

Checkouts & Renewals in Date Range

Find bibs circulated, select date range

Circulation counts by biblio for a given duration, takes deleted items and biblios into account

Department wise transactions of Issued & Returned books with specific date range

Circulation in Time

Hourly

Checkouts by Hour in selected date range

Track In House Use Hourly

Track In House Use in Hourly Range

Daily

Previous Day's Circ Stats

Weekly

Issues Details for a Selected Branch for the Week To Date

Weekly Checked Out by Branch

Monthly

Previous Month's Circ Stats

Previous Month's Checkouts/Renewals by Collection Code

Previous Month Checkouts/Renews by Patron Category

Month-wise Circulation Report for a Given Year: By Branch And Cumulative

COUNT - Month wise circulation report for a specific year

COUNT - Date wise daily circulation report for a specific month of an year

Checkouts by Item Type (previous month)

Monthly circ in a date range

Checked In

Date Wise List of Checked In Books

Old circulation issues since the beginning

Patron notes about item, shown at check in, within a date range

Checked Out

All Checked Out Books

Date Wise List of Checked Out Books

Patrons w/ Books Due Tomorrow

Patrons w/ Items Issued Today

Issues placed at a specific branch during a specified period

List the items circulated from a branch

List of items checked out in a date range

List of items issued on a date with optional limit by due date

Items Checked out by Patron from Different Home Library

Circulation by Location

By Call Number

Checkouts by Call Number (previous month)

Renewals by Call Number (previous month)

Checkouts & Renewal Counts by Call Number

Count of Circ by Alpha Call Number Prefix

Circulation of Two Call Numbers

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

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

Circulation grouped by Dewey decimal range, select date range

By Shelving Location

Checkout by Shelving Location

Checkins by Shelving Location

Renewals by Shelving Location

Circ in a date range by shelving location

Circulation stats, choose date range, shelving location, itemtype

Local Use

Track In House Use

Track In House Use for a Title

Local Use by Shelving Location

Naughty Overdues

Overdue materials

Long Overdues

Overdues w/ Contact Info

Overdues Call List w/ Guarantor

Overdues by Homeroom/Attribute

Overdues w/item info when patron has no email

Overdues by Item Type

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

List of current checked out books; branch wise