SQL Reports Circulation

From Koha Wiki

Jump to: navigation, search

This is the page that relates to circulation reports.

Contents

Circulation Reports

General Statistics

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

Circulation in Time

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

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

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

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)

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

Checked Out

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

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

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;


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


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


Naughty Overdues

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



Others

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 AND s.type IN ('issue','renew')
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
;

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


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

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

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)

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