SQL Reports Circulation

This is the page that relates to circulation reports.
SQL Reports Holds | SQL Reports Patrons | SQL Reports Main Library
== Circulation Reports ==
=== General Statistics ===
==== Department wise "sort1" (issue+renew) statistics in date range ====
* Developer: Vinod Kumar Mishra (http://itshelp.co.in/)
* Module: Circulation
* Purpose: This report generates department wise statistics of (issue+renew) books in a date range. It includes % of issues out of all issued during the period AND % of issues by active Users in Koha.A visual chart/graph can be also automatically generated for reference. 
* Status: Completed and tested on Version 20.05
* IMPORTANT The departments are defined under Bsort1 authorized value. 
SELECT
sort1 'Department Code',
authorised_values.lib AS "Department",
count(sort1) AS "Total issued",
count(sort1) / SUM(count(sort1)) OVER()*100 AS "% of issue out of all issued during the period",
count(sort1)/(SELECT count(borrowers.sort1) from borrowers where NOT borrowers.dateexpiry <= NOW())*100 AS "% of issue by active Users in Software"
FROM statistics
LEFT JOIN borrowers ON statistics.borrowernumber=borrowers.borrowernumber
LEFT JOIN items ON statistics.itemnumber=items.itemnumber
LEFT JOIN authorised_values ON borrowers.sort1=authorised_values.authorised_value 
WHERE DATE(statistics.datetime) BETWEEN <<Transaction BETWEEN (dd-mm-yyyy)|date>> AND <<AND (dd-mm-yyyy)|date>> AND statistics.type in ('issue','renew') AND NOT borrowers.dateexpiry <= NOW() AND authorised_values.category = 'Bsort1'
Group by sort1
ORDER BY sort1 ASC
==== 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 action by staff (date range) ====
* Developer: Vinod Kumar Mishra (http://itshelp.co.in/)
* Module: Circulation
* Purpose: This report provides detail about all the circulation actions taken by various library staff in a date range. Help to track who has done what and other reporting purposes.
* Status: Completed and tested on Version 20.05
* IMPORTANT Log under system preferences should be active (i.e. Log for various options). 
SELECT concat(p.firstname, ' ', p.surname) 'Staff Name',p.cardnumber 'Staff Card Number',a.action'Action',a.timestamp'Time',c.barcode 'Acc. Number',d.title'Title', concat(k.firstname, ' ', k.surname) 'User Name', k.cardnumber 'User ID'
FROM action_logs a
LEFT JOIN borrowers p ON (a.user=p.borrowernumber)
LEFT JOIN items c ON (a.info=c.itemnumber)
LEFT JOIN biblio d ON (c.biblionumber=d.biblionumber)
LEFT JOIN borrowers k ON (a.object=k.borrowernumber)
where a.timestamp BETWEEN <<Circulation action Between (dd/mm/yyyy)|date>> AND <<and (dd/mm/yyyy)|date>> AND a.module='CIRCULATION'
==== 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('',s.type,'') AS ' ', 
 CONCAT('',b.branchname,'') 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('',s.type,'') 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('',c.type,'') AS ' ', 
 'ALL BRANCHES' 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('',COUNT(*),'') AS 'TOTAL', 
 CONCAT('',c.type,'') 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 '')
 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
==== Circ stats for all items with a specified item type ====
* Developer: George Williams, Northeast Kansas Library System
* Module: Circulation
* Purpose: Lists all items in an item type with issue counts, renewal counts, dates and counts of issues and renewals in year and month specified
* Status: Complete
* Notes: Run for item types with a small item type count
Select 
 items_subquery.title, 
 items_subquery.barcode, 
 items_subquery.dateaccessioned, 
 items_subquery.datelastborrowed, 
 items_subquery.issues, 
 items_subquery.renewals, 
 items_subquery.onloan, 
 items_subquery.biblionumber, 
 items_subquery.itemnumber, 
 items_subquery.description, 
 annual_statistics.Group_Concat_datetime, 
 Coalesce(annual_statistics.COUNT, 0) as COUNT 
From 
 ( 
 Select 
 biblio.title, 
 items.barcode, 
 items.dateaccessioned, 
 items.datelastborrowed, 
 items.issues, 
 items.renewals, 
 items.onloan, 
 itemtypes.description, 
 items.biblionumber, 
 items.itemnumber 
 From 
 biblio Join 
 items On items.biblionumber = biblio.biblionumber Inner Join 
 itemtypes On itemtypes.itemtype = items.itype 
 Where 
 items.itype LIKE <<Enter item type|itemtypes>> 
 Order By 
 biblio.title 
 ) items_subquery 
 Left Join 
 ( 
 Select 
 statistics.itemnumber, 
 Group_Concat( 
 statistics.datetime 
 ORDER BY statistics.datetime 
 DESC Separator ' // ' 
 ) As Group_Concat_datetime, 
 Count(statistics.datetime) As COUNT 
 From 
 statistics 
 Where 
 (statistics.type = 'issue' Or 
 statistics.type = 'renew') And 
 Year(statistics.datetime) 
 Like <<Enter 4 digit year>> And 
 Month(statistics.datetime) 
 Like Concat('%', <<Enter number of month (or a percent symbol for all months)>> , '%') 
 Group By 
 statistics.itemnumber 
 ) annual_statistics 
 On annual_statistics.itemnumber = items_subquery.itemnumber 
Group By 
 items_subquery.itemnumber, 
 annual_statistics.Group_Concat_datetime, 
 annual_statistics.COUNT 
=== 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 ===
==== List of books issued in between two date ranges and accession number range ====
* Developer: Vinod Kumar Mishra 
* Module: Circulation 
* Purpose: List of books issued in between two date ranges and accession number range. It has been designed to satisfy any query related to RTI/Audit etc.
* Status: Complete and tested on Version 20.05
 SELECT b.title 'Title', b.author 'Author', i.itemcallnumber 'Call Number', i.barcode 'Acc. Number', c.issuedate 'Issue Date'
FROM (SELECT iss.issuedate, iss.itemnumber FROM issues iss, 
(SELECT @StartDate:=<<Between (dd/mm/yyyy)|date>>,@EndDate:=<<and (dd/mm/yyyy)|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 cast(i.barcode AS UNSIGNED) BETWEEN <<Accession Number>> AND <<To Accession Number>>
==== 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 utf8mb4_unicode_ci AS barcode,
 @ccode := <<Collection Code|CCODE>> COLLATE utf8mb4_unicode_ci AS branch,
 @lastcheckout := <<Last checked out before|date>> COLLATE utf8mb4_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_count) 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_count) 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' ) 
==== Circulation and fine rules by branch ====
* Developer: Jason Robb, SEKLS
* Module: Circulation/Admin
* Purpose: Pulls the circulation and fine rules for a selected branch
* Version: 21.05
SELECT COALESCE(cr.branchcode, 'all') AS branch, COALESCE(cr.categorycode, 'all') AS cat, COALESCE(cr.itemtype, 'all') AS type,
 MAX(CASE WHEN cr.rule_name = 'note' THEN cr.rule_value ELSE NULL END) AS Note,
 MAX(CASE WHEN cr.rule_name = 'maxissueqty' THEN cr.rule_value ELSE NULL END) AS CheckoutsAllowed,
 MAX(CASE WHEN cr.rule_name = 'maxonsiteissueqty' THEN cr.rule_value ELSE NULL END) AS OnsiteCheckoutsAllowed,
 MAX(CASE WHEN cr.rule_name = 'issuelength' THEN cr.rule_value ELSE NULL END) AS LoanPeriod,
 MAX(CASE WHEN cr.rule_name = 'daysmode' THEN cr.rule_value ELSE NULL END) AS DaysMode,
 MAX(CASE WHEN cr.rule_name = 'lengthunit' THEN cr.rule_value ELSE NULL END) AS Unit,
 MAX(CASE WHEN cr.rule_name = 'hardduedate' THEN cr.rule_value ELSE NULL END) AS HardDueDate,
 MAX(CASE WHEN cr.rule_name = 'decreaseloanholds' THEN cr.rule_value ELSE NULL END) AS DecreaseLoanPeriodForHighHolds,
 MAX(CASE WHEN cr.rule_name = 'fine' THEN cr.rule_value ELSE NULL END) AS FineAmount,
 MAX(CASE WHEN cr.rule_name = 'chargeperiod' THEN cr.rule_value ELSE NULL END) AS FineChargingInterval,
 MAX(CASE WHEN cr.rule_name = 'chargeperiod_charge_at' THEN cr.rule_value ELSE NULL END) AS WhenToCharge,
 MAX(CASE WHEN cr.rule_name = 'firstremind' THEN cr.rule_value ELSE NULL END) AS FineGracePeriod,
 MAX(CASE WHEN cr.rule_name = 'overduefinescap' THEN cr.rule_value ELSE NULL END) AS OverdueFineCap,
 MAX(CASE WHEN cr.rule_name = 'cap_fine_to_replacement_price' THEN cr.rule_value ELSE NULL END) AS CapFineAtReplacementPrice,
 MAX(CASE WHEN cr.rule_name = 'finedays' THEN cr.rule_value ELSE NULL END) AS SuspensionInDays,
 MAX(CASE WHEN cr.rule_name = 'maxsuspensiondays' THEN cr.rule_value ELSE NULL END) AS MaxSuspensionDuration,
 MAX(CASE WHEN cr.rule_name = 'suspension_chargeperiod' THEN cr.rule_value ELSE NULL END) AS SuspensionChargeInterval,
 MAX(CASE WHEN cr.rule_name = 'renewalsallowed' THEN cr.rule_value ELSE NULL END) AS RenewalsAllowed,
 MAX(CASE WHEN cr.rule_name = 'renewalperiod' THEN cr.rule_value ELSE NULL END) AS RenewLoanLength,
 MAX(CASE WHEN cr.rule_name = 'norenewalbefore' THEN cr.rule_value ELSE NULL END) AS NoRenewBefore,
 MAX(CASE WHEN cr.rule_name = 'auto_renew' THEN cr.rule_value ELSE NULL END) AS AutoRenew,
 MAX(CASE WHEN cr.rule_name = 'no_auto_renewal_after' THEN cr.rule_value ELSE NULL END) AS NoAutoRenewAfter,
 MAX(CASE WHEN cr.rule_name = 'no_auto_renewal_after_hard_limit' THEN cr.rule_value ELSE NULL END) AS NoAutoRenewAfterHardDate,
 MAX(CASE WHEN cr.rule_name = 'reservesallowed' THEN cr.rule_value ELSE NULL END) AS HoldsAllowed,
 MAX(CASE WHEN cr.rule_name = 'holds_per_day' THEN cr.rule_value ELSE NULL END) AS HoldsPerDay,
 MAX(CASE WHEN cr.rule_name = 'holds_per_record' THEN cr.rule_value ELSE NULL END) AS HoldsPerRecord,
 MAX(CASE WHEN cr.rule_name = 'onshelfholds' THEN cr.rule_value ELSE NULL END) AS OnShelfHolds,
 MAX(CASE WHEN cr.rule_name = 'opacitemholds' THEN cr.rule_value ELSE NULL END) AS OPACilvlHolds,
 MAX(CASE WHEN cr.rule_name = 'article_requests' THEN cr.rule_value ELSE NULL END) AS ArticleRequests,
 MAX(CASE WHEN cr.rule_name = 'rentaldiscount' THEN cr.rule_value ELSE NULL END) AS RentalDiscount
FROM circulation_rules cr
WHERE branchcode LIKE <<Choose Library|branches:all>>
 AND cr.rule_name not IN ('unseen_renewals_allowed', 'restrictedtype', 'patron_maxissueqty', 'patron_maxonsiteissueqty', 'max_holds', 'lostreturn', 'hold_fulfillment_policy', 'holdallowed', 'returnbranch', 'hardduedatecompare', 'accountsent')
GROUP BY branch, cat, type
==== Circulation rules - 3 reports ====
* Developer: George H. Williams (Next Search Catalog / Northeast Kansas Library System)
* Module: 3 reports for circulation rules
* Purpose: Allows staff that don't have "manage_circ_rules" or "manage_circ_rules_from_any_libraries" the ability to view the rules for their library
* Version: Tested and working on Koha 21.05
===== Circulation rules 1 - circulation and renewal =====
 SELECT
 branchess.branchcode,
 categorytypes.BORROWER_CATEGORY,
 categorytypes.ITEM_TYPE,
 notes.rule_value AS NOTE,
 If(
 maxissueqty.rule_value = "", "Unlimited", maxissueqty.rule_value
 ) AS CKOS_ALLOWED,
 If(
 maxonsiteissueqty.rule_value = "", "Unlimited", maxissueqty.rule_value
 ) AS ONSITE_ALLOWED,
 issuelength.rule_value AS LOAN_PERIOD,
 lengthunit.rule_value AS LOAN_UNIT,
 If(
 hardduedate.rule_value = "",
 "",
 If(
 hardduedatecompare.rule_value = "-1",
 "Before",
 If(
 hardduedatecompare.rule_value = "0",
 "Exactly on",
 If(
 hardduedatecompare.rule_value = "1",
 "After",
 hardduedatecompare.rule_value
 )
 )
 )
 ) AS HDD_ITEMS_DUE,
 hardduedate.rule_value AS HARD_DATE,
 renewalsallowed.rule_value AS RENEWAL_ALLOWED,
 renewalperiod.rule_value AS RENEWAL_PERIOD,
 norenewalbefore.rule_value AS NO_RENEWAL_BEFORE,
 If(
 auto_renew.rule_value = 0,
 "Disabled",
 If(
 auto_renew.rule_value = 1,
 "Enabled",
 auto_renew.rule_value
 )
 ) AS AUTO_RENEW_ENABLED,
 no_auto_renewal_after.rule_value AS NO_AUTO_RENEW_AFTER,
 no_auto_renewal_after_hard_limit.rule_value AS AUTO_RENEW_HDD
 FROM
 (
 SELECT
 branches.branchcode,
 branches.branchname
 FROM
 branches
 UNION
 SELECT
 Concat('ALL') AS branchcode,
 Concat('All libraries') AS branchname
 ) branchess JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(categories.description IS NULL, "All borrowers", categories.description) AS BORROWER_CATEGORY,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 If(itemtypes.description IS NULL, "All item types", itemtypes.description) AS ITEM_TYPE
 FROM
 circulation_rules LEFT JOIN
 categories ON circulation_rules.categorycode = categories.categorycode
 LEFT JOIN
 itemtypes ON circulation_rules.itemtype = itemtypes.itemtype
 WHERE
 circulation_rules.rule_name <> "accountsent" AND 
 circulation_rules.rule_name <> "hold_fulfillment_policy" AND 
 circulation_rules.rule_name <> "holdallowed" AND 
 circulation_rules.rule_name <> "max_holds" AND 
 circulation_rules.rule_name <> "patron_maxissueqty" AND 
 circulation_rules.rule_name <> "patron_maxonsiteissueqty" AND 
 circulation_rules.rule_name <> "refund" AND 
 circulation_rules.rule_name <> "restrictedtype" AND 
 circulation_rules.rule_name <> "returnbranch" 
 GROUP BY
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ),
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ),
 If(categories.description IS NULL, "All borrowers", categories.description),
 circulation_rules.itemtype,
 itemtypes.description
 ) categorytypes
 ON categorytypes.branchcode = branchess.branchcode LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'note'
 ) notes
 ON notes.branchcode = categorytypes.branchcode AND
 notes.categorycode = categorytypes.categorycode AND
 notes.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'maxissueqty'
 ) maxissueqty
 ON maxissueqty.branchcode = categorytypes.branchcode AND
 maxissueqty.categorycode = categorytypes.categorycode AND
 maxissueqty.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'maxonsiteissueqty'
 ) maxonsiteissueqty
 ON maxonsiteissueqty.branchcode = categorytypes.branchcode AND
 maxonsiteissueqty.categorycode = categorytypes.categorycode AND
 maxonsiteissueqty.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'issuelength'
 ) issuelength
 ON issuelength.branchcode = categorytypes.branchcode AND
 issuelength.categorycode = categorytypes.categorycode AND
 issuelength.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'lengthunit'
 ) lengthunit
 ON lengthunit.branchcode = categorytypes.branchcode AND
 lengthunit.categorycode = categorytypes.categorycode AND
 lengthunit.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'hardduedatecompare'
 ) hardduedatecompare
 ON hardduedatecompare.branchcode = categorytypes.branchcode AND
 hardduedatecompare.categorycode = categorytypes.categorycode AND
 hardduedatecompare.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'hardduedate'
 ) hardduedate
 ON hardduedate.branchcode = categorytypes.branchcode AND
 hardduedate.categorycode = categorytypes.categorycode AND
 hardduedate.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'renewalsallowed'
 ) renewalsallowed
 ON renewalsallowed.branchcode = categorytypes.branchcode AND
 renewalsallowed.categorycode = categorytypes.categorycode AND
 renewalsallowed.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'renewalperiod'
 ) renewalperiod
 ON renewalperiod.branchcode = categorytypes.branchcode AND
 renewalperiod.categorycode = categorytypes.categorycode AND
 renewalperiod.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'norenewalbefore'
 ) norenewalbefore
 ON norenewalbefore.branchcode = categorytypes.branchcode AND
 norenewalbefore.categorycode = categorytypes.categorycode AND
 norenewalbefore.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'auto_renew'
 ) auto_renew
 ON auto_renew.branchcode = categorytypes.branchcode AND
 auto_renew.categorycode = categorytypes.categorycode AND
 auto_renew.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'no_auto_renewal_after'
 ) no_auto_renewal_after
 ON no_auto_renewal_after.branchcode = categorytypes.branchcode AND
 no_auto_renewal_after.categorycode = categorytypes.categorycode AND
 no_auto_renewal_after.itemtype = categorytypes.itemtype LEFT JOIN
 (
 SELECT
 If(
 circulation_rules.branchcode IS NULL,
 "ALL",
 circulation_rules.branchcode
 ) AS branchcode,
 If(
 circulation_rules.categorycode IS NULL,
 "ALL",
 circulation_rules.categorycode
 ) AS categorycode,
 If(
 circulation_rules.itemtype IS NULL,
 "ALL",
 circulation_rules.itemtype
 ) AS itemtype,
 circulation_rules.rule_name,
 circulation_rules.rule_value
 FROM
 circulation_rules
 WHERE
 circulation_rules.rule_name = 'no_auto_renewal_after_hard_limit'
 ) no_auto_renewal_after_hard_limit
 ON no_auto_renewal_after_hard_limit.branchcode = categorytypes.branchcode AND
 no_auto_renewal_after_hard_limit.categorycode = categorytypes.categorycode AND
 no_auto_renewal_after_hard_limit.itemtype = categorytypes.itemtype
 WHERE
 (categorytypes.branchcode LIKE <<Choose your library|branches:all>> OR
 categorytypes.branchcode LIKE "ALL")
 GROUP BY
 branchess.branchcode,
 categorytypes.BORROWER_CATEGORY,
 categorytypes.ITEM_TYPE
 ORDER BY
 If(
 branchess.branchcode = "ALL",
 "ZZZZZ",
 branchess.branchcode
 ),
 If(
 categorytypes.BORROWER_CATEGORY = "All borrowers",
 "ZZZZZ",
 categorytypes.BORROWER_CATEGORY
 ),
 If(
 categorytypes.ITEM_TYPE = "All item types",
 "ZZZZZ",
 categorytypes.ITEM_TYPE
 )
===== Circulation rules 2 - late fees and suspensions =====
 SELECT 
 branchess.branchcode, 
 categorytypes.BORROWER_CATEGORY, 
 categorytypes.ITEM_TYPE, 
 Format(fine.rule_value, 2) AS LATE_FEE, 
 chargeperiod.rule_value AS FEE_PERIOD, 
 lengthunit.rule_value AS FEE_INTERVAL, 
 chargeperiod_charge_at.rule_value AS CHARGED_AT, 
 firstremind.rule_value AS GRACE_PERIOD, 
 lengthunit.rule_value AS GRACE_INTERVAL, 
 Concat( 
 If(overduefinescap.rule_value = 0, "", 
 Format(overduefinescap.rule_value, 2)), 
 cap_fine_to_replacement_price.rule_value 
 ) AS LATE_FEE_CAP, 
 finedays.rule_value AS SUSPENSION_DAYS, 
 maxsuspensiondays.rule_value AS MAX_SUSPENSION, 
 If(finedays.rule_value = "", "", suspension_chargeperiod.rule_value) AS SUSPENSION_INTERVAL 
 FROM 
 ( 
 SELECT 
 branches.branchcode, 
 branches.branchname 
 FROM 
 branches 
 UNION 
 SELECT 
 Concat('ALL') AS branchcode, 
 Concat('All libraries') AS branchname 
 ) branchess JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If(categories.description IS NULL, "All borrowers", categories.description) AS BORROWER_CATEGORY, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 If(itemtypes.description IS NULL, "All item types", itemtypes.description) AS ITEM_TYPE 
 FROM 
 circulation_rules LEFT JOIN 
 categories ON circulation_rules.categorycode = categories.categorycode 
 LEFT JOIN 
 itemtypes ON circulation_rules.itemtype = itemtypes.itemtype 
 WHERE 
 circulation_rules.rule_name <> "accountsent" AND 
 circulation_rules.rule_name <> "hold_fulfillment_policy" AND 
 circulation_rules.rule_name <> "holdallowed" AND 
 circulation_rules.rule_name <> "max_holds" AND 
 circulation_rules.rule_name <> "patron_maxissueqty" AND 
 circulation_rules.rule_name <> "patron_maxonsiteissueqty" AND 
 circulation_rules.rule_name <> "refund" AND 
 circulation_rules.rule_name <> "restrictedtype" AND 
 circulation_rules.rule_name <> "returnbranch" 
 GROUP BY 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ), 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ), 
 If(categories.description IS NULL, "All borrowers", categories.description), 
 circulation_rules.itemtype, 
 itemtypes.description 
 ) categorytypes 
 ON categorytypes.branchcode = branchess.branchcode LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'fine' 
 ) fine 
 ON fine.branchcode = categorytypes.branchcode AND 
 fine.categorycode = categorytypes.categorycode AND 
 fine.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'lengthunit' 
 ) lengthunit 
 ON lengthunit.branchcode = categorytypes.branchcode AND 
 lengthunit.categorycode = categorytypes.categorycode AND 
 lengthunit.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'chargeperiod' 
 ) chargeperiod 
 ON chargeperiod.branchcode = categorytypes.branchcode AND 
 chargeperiod.categorycode = categorytypes.categorycode AND 
 chargeperiod.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 If(circulation_rules.rule_value = 0, "End of interval", 
 If(circulation_rules.rule_value = 1, "Start of interval", 
 circulation_rules.rule_value 
 ) 
 ) AS rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'chargeperiod_charge_at' 
 ) chargeperiod_charge_at 
 ON chargeperiod_charge_at.branchcode = categorytypes.branchcode AND 
 chargeperiod_charge_at.categorycode = categorytypes.categorycode AND 
 chargeperiod_charge_at.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'firstremind' 
 ) firstremind 
 ON firstremind.branchcode = categorytypes.branchcode AND 
 firstremind.categorycode = categorytypes.categorycode AND 
 firstremind.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'overduefinescap' 
 ) overduefinescap 
 ON overduefinescap.branchcode = categorytypes.branchcode AND 
 overduefinescap.categorycode = categorytypes.categorycode AND 
 overduefinescap.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 If(circulation_rules.rule_value = 0, "", 
 If(circulation_rules.rule_value = 1, "Item replacement cost", 
 circulation_rules.rule_value 
 ) 
 ) AS rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'cap_fine_to_replacement_price' 
 ) cap_fine_to_replacement_price 
 ON cap_fine_to_replacement_price.branchcode = categorytypes.branchcode AND 
 cap_fine_to_replacement_price.categorycode = categorytypes.categorycode AND 
 cap_fine_to_replacement_price.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'finedays' 
 ) finedays 
 ON finedays.branchcode = categorytypes.branchcode AND 
 finedays.categorycode = categorytypes.categorycode AND 
 finedays.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'maxsuspensiondays' 
 ) maxsuspensiondays 
 ON maxsuspensiondays.branchcode = categorytypes.branchcode AND 
 maxsuspensiondays.categorycode = categorytypes.categorycode AND 
 maxsuspensiondays.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'suspension_chargeperiod' 
 ) suspension_chargeperiod 
 ON suspension_chargeperiod.branchcode = categorytypes.branchcode AND 
 suspension_chargeperiod.categorycode = categorytypes.categorycode AND 
 suspension_chargeperiod.itemtype = categorytypes.itemtype 
 WHERE 
 (categorytypes.branchcode LIKE <<Choose your library|branches:all>> OR 
 categorytypes.branchcode LIKE "ALL") 
 GROUP BY 
 branchess.branchcode, 
 categorytypes.BORROWER_CATEGORY, 
 categorytypes.ITEM_TYPE 
 ORDER BY 
 If( 
 branchess.branchcode = "ALL", 
 "ZZZZZ", 
 branchess.branchcode 
 ), 
 If( 
 categorytypes.BORROWER_CATEGORY = "All borrowers", 
 "ZZZZZ", 
 categorytypes.BORROWER_CATEGORY 
 ), 
 If( 
 categorytypes.ITEM_TYPE = "All item types", 
 "ZZZZZ", 
 categorytypes.ITEM_TYPE 
 ) 
===== Circulation rules 3 - requests and rental discount =====
 SELECT 
 branchess.branchcode, 
 categorytypes.BORROWER_CATEGORY, 
 categorytypes.ITEM_TYPE, 
 reservesallowed.rule_value AS REQUESTS_ALLOWED, 
 holds_per_day.rule_value AS REQUESTS_PER_DAY, 
 holds_per_record.rule_value AS REQUESTS_PER_BIBLIO, 
 If(onshelfholds.rule_value = 1, "Yes", 
 If(onshelfholds.rule_value = 0, "If any unavailable", 
 If(onshelfholds.rule_value = 2, "If all unavailable", 
 onshelfholds.rule_value 
 ) 
 ) 
 ) AS ON_SHELF_REQUESTS, 
 If(opacitemholds.rule_value = "N", "Don't allow", 
 If(opacitemholds.rule_value = "Y", "Allowed", 
 If(opacitemholds.rule_value = "F", "Required", 
 opacitemholds.rule_value 
 ) 
 ) 
 ) AS OPAC_ITEM_LEVEL_REQUESTS, 
 article_requests.rule_value AS ARTICLE_REQUESTS, 
 rentaldiscount.rule_value AS RENTAL_DISCOUNT 
 FROM 
 ( 
 SELECT 
 branches.branchcode, 
 branches.branchname 
 FROM 
 branches 
 UNION 
 SELECT 
 Concat('ALL') AS branchcode, 
 Concat('All libraries') AS branchname 
 ) branchess JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 categories.description IS NULL, 
 "All borrowers", 
 categories.description 
 ) AS BORROWER_CATEGORY, 
 If(circulation_rules.itemtype IS NULL, "ALL", circulation_rules.itemtype) AS itemtype, 
 If(itemtypes.description IS NULL, "All item types", itemtypes.description) AS ITEM_TYPE 
 FROM 
 circulation_rules LEFT JOIN 
 categories ON circulation_rules.categorycode = categories.categorycode LEFT JOIN 
 itemtypes ON circulation_rules.itemtype = itemtypes.itemtype 
 WHERE 
 circulation_rules.rule_name <> "accountsent" AND 
 circulation_rules.rule_name <> "hold_fulfillment_policy" AND 
 circulation_rules.rule_name <> "holdallowed" AND 
 circulation_rules.rule_name <> "max_holds" AND 
 circulation_rules.rule_name <> "patron_maxissueqty" AND 
 circulation_rules.rule_name <> "patron_maxonsiteissueqty" AND 
 circulation_rules.rule_name <> "refund" AND 
 circulation_rules.rule_name <> "restrictedtype" AND 
 circulation_rules.rule_name <> "returnbranch" 
 GROUP BY 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ), 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ), 
 If( 
 categories.description IS NULL, 
 "All borrowers", 
 categories.description 
 ), 
 circulation_rules.itemtype, 
 itemtypes.description 
 ) categorytypes 
 ON categorytypes.branchcode = branchess.branchcode LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'reservesallowed' 
 ) reservesallowed 
 ON reservesallowed.branchcode = categorytypes.branchcode AND 
 reservesallowed.categorycode = categorytypes.categorycode AND 
 reservesallowed.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'holds_per_day' 
 ) holds_per_day 
 ON holds_per_day.branchcode = categorytypes.branchcode AND 
 holds_per_day.categorycode = categorytypes.categorycode AND 
 holds_per_day.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'holds_per_record' 
 ) holds_per_record 
 ON holds_per_record.branchcode = categorytypes.branchcode AND 
 holds_per_record.categorycode = categorytypes.categorycode AND 
 holds_per_record.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'onshelfholds' 
 ) onshelfholds 
 ON onshelfholds.branchcode = categorytypes.branchcode AND 
 onshelfholds.categorycode = categorytypes.categorycode AND 
 onshelfholds.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'opacitemholds' 
 ) opacitemholds 
 ON opacitemholds.branchcode = categorytypes.branchcode AND 
 opacitemholds.categorycode = categorytypes.categorycode AND 
 opacitemholds.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'article_requests' 
 ) article_requests 
 ON article_requests.branchcode = categorytypes.branchcode AND 
 article_requests.categorycode = categorytypes.categorycode AND 
 article_requests.itemtype = categorytypes.itemtype LEFT JOIN 
 ( 
 SELECT 
 If( 
 circulation_rules.branchcode IS NULL, 
 "ALL", 
 circulation_rules.branchcode 
 ) AS branchcode, 
 If( 
 circulation_rules.categorycode IS NULL, 
 "ALL", 
 circulation_rules.categorycode 
 ) AS categorycode, 
 If( 
 circulation_rules.itemtype IS NULL, 
 "ALL", 
 circulation_rules.itemtype 
 ) AS itemtype, 
 circulation_rules.rule_name, 
 circulation_rules.rule_value 
 FROM 
 circulation_rules 
 WHERE 
 circulation_rules.rule_name = 'rentaldiscount' 
 ) rentaldiscount 
 ON rentaldiscount.branchcode = categorytypes.branchcode AND 
 rentaldiscount.categorycode = categorytypes.categorycode AND 
 rentaldiscount.itemtype = categorytypes.itemtype 
 WHERE 
 (categorytypes.branchcode LIKE <<Choose your library|branches:all>> OR 
 categorytypes.branchcode LIKE "ALL") 
 GROUP BY 
 branchess.branchcode, 
 categorytypes.BORROWER_CATEGORY, 
 categorytypes.ITEM_TYPE 
 ORDER BY 
 If( 
 branchess.branchcode = "ALL", 
 "ZZZZZ", 
 branchess.branchcode 
 ), 
 If( 
 categorytypes.BORROWER_CATEGORY = "All borrowers", 
 "ZZZZZ", 
 categorytypes.BORROWER_CATEGORY 
 ), 
 If( 
 categorytypes.ITEM_TYPE = "All item types", 
 "ZZZZZ", 
 categorytypes.ITEM_TYPE 
 ) 
==== 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
==== Percent of collection circulated per month since acquisition ====
*Developer: Andrew Fuerste-Henry
*Module: Circulation / statistics
*Purpose: For items added within a date range, calculate the percentage of of those items checked out per month relative to the date on which they were accessioned
*Status: Complete
SELECT 
	round(sum(if(1_mo_cko > 0,1,0))/sum(if(months_in_collection>=1,1,0))*100,2) as 1_month,
 round(sum(if(2_mo_cko > 0,1,0))/sum(if(months_in_collection>=2,1,0))*100,2) as 2_month,
 round(sum(if(3_mo_cko > 0,1,0))/sum(if(months_in_collection>=3,1,0))*100,2) as 3_month,
 round(sum(if(4_mo_cko > 0,1,0))/sum(if(months_in_collection>=4,1,0))*100,2) as 4_month,
 round(sum(if(5_mo_cko > 0,1,0))/sum(if(months_in_collection>=5,1,0))*100,2) as 5_month,
 round(sum(if(6_mo_cko > 0,1,0))/sum(if(months_in_collection>=6,1,0))*100,2) as 6_month,
 round(sum(if(7_mo_cko > 0,1,0))/sum(if(months_in_collection>=7,1,0))*100,2) as 7_month,
 round(sum(if(8_mo_cko > 0,1,0))/sum(if(months_in_collection>=8,1,0))*100,2) as 8_month,
 round(sum(if(9_mo_cko > 0,1,0))/sum(if(months_in_collection>=9,1,0))*100,2) as 9_month,
 round(sum(if(10_mo_cko > 0,1,0))/sum(if(months_in_collection>=10,1,0))*100,2) as 10_month,
 round(sum(if(11_mo_cko > 0,1,0))/sum(if(months_in_collection>=11,1,0))*100,2) as 11_month,
 round(sum(if(12_mo_cko > 0,1,0))/sum(if(months_in_collection>=12,1,0))*100,2) as 12_month,
	round(sum(if(13_mo_cko > 0,1,0))/sum(if(months_in_collection>=13,1,0))*100,2) as 13_month,
 round(sum(if(14_mo_cko > 0,1,0))/sum(if(months_in_collection>=14,1,0))*100,2) as 14_month,
 round(sum(if(15_mo_cko > 0,1,0))/sum(if(months_in_collection>=15,1,0))*100,2) as 15_month,
 round(sum(if(16_mo_cko > 0,1,0))/sum(if(months_in_collection>=16,1,0))*100,2) as 16_month,
 round(sum(if(17_mo_cko > 0,1,0))/sum(if(months_in_collection>=17,1,0))*100,2) as 17_month,
 round(sum(if(18_mo_cko > 0,1,0))/sum(if(months_in_collection>=18,1,0))*100,2) as 18_month,
 round(sum(if(19_mo_cko > 0,1,0))/sum(if(months_in_collection>=19,1,0))*100,2) as 19_month,
 round(sum(if(20_mo_cko > 0,1,0))/sum(if(months_in_collection>=20,1,0))*100,2) as 20_month,
 round(sum(if(21_mo_cko > 0,1,0))/sum(if(months_in_collection>=21,1,0))*100,2) as 21_month,
 round(sum(if(22_mo_cko > 0,1,0))/sum(if(months_in_collection>=22,1,0))*100,2) as 22_month,
 round(sum(if(23_mo_cko > 0,1,0))/sum(if(months_in_collection>=23,1,0))*100,2) as 23_month,
 round(sum(if(24_mo_cko > 0,1,0))/sum(if(months_in_collection>=24,1,0))*100,2) as 24_month
FROM
(SELECT i.itemnumber,
 timestampdiff(month,dateaccessioned,curdate()) as months_in_collection,
 sum(if(date(s.datetime) BETWEEN dateaccessioned and date_add(dateaccessioned, interval 24 month) AND s.type='issue',1,0)) as total_circ_24_months,
sum(if(date(s.datetime) BETWEEN dateaccessioned and date_add(dateaccessioned, interval 1 month) AND s.type='issue',1,0)) as 1_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 1 month) and date_add(dateaccessioned, interval 2 month) AND s.type='issue',1,0)) as 2_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 2 month) and date_add(dateaccessioned, interval 3 month) AND s.type='issue',1,0)) as 3_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 3 month) and date_add(dateaccessioned, interval 4 month) AND s.type='issue',1,0)) as 4_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 4 month) and date_add(dateaccessioned, interval 5 month) AND s.type='issue',1,0)) as 5_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 5 month) and date_add(dateaccessioned, interval 6 month) AND s.type='issue',1,0)) as 6_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 6 month) and date_add(dateaccessioned, interval 7 month) AND s.type='issue',1,0)) as 7_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 7 month) and date_add(dateaccessioned, interval 8 month) AND s.type='issue',1,0)) as 8_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 8 month) and date_add(dateaccessioned, interval 9 month) AND s.type='issue',1,0)) as 9_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 9 month) and date_add(dateaccessioned, interval 10 month) AND s.type='issue',1,0)) as 10_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 10 month) and date_add(dateaccessioned, interval 11 month) AND s.type='issue',1,0)) as 11_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 11 month) and date_add(dateaccessioned, interval 12 month) AND s.type='issue',1,0)) as 12_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 12 month) and date_add(dateaccessioned, interval 13 month) AND s.type='issue',1,0)) as 13_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 13 month) and date_add(dateaccessioned, interval 14 month) AND s.type='issue',1,0)) as 14_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 14 month) and date_add(dateaccessioned, interval 15 month) AND s.type='issue',1,0)) as 15_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 15 month) and date_add(dateaccessioned, interval 16 month) AND s.type='issue',1,0)) as 16_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 16 month) and date_add(dateaccessioned, interval 17 month) AND s.type='issue',1,0)) as 17_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 17 month) and date_add(dateaccessioned, interval 18 month) AND s.type='issue',1,0)) as 18_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 18 month) and date_add(dateaccessioned, interval 19 month) AND s.type='issue',1,0)) as 19_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 19 month) and date_add(dateaccessioned, interval 20 month) AND s.type='issue',1,0)) as 20_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 20 month) and date_add(dateaccessioned, interval 21 month) AND s.type='issue',1,0)) as 21_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 21 month) and date_add(dateaccessioned, interval 22 month) AND s.type='issue',1,0)) as 22_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 22 month) and date_add(dateaccessioned, interval 23 month) AND s.type='issue',1,0)) as 23_mo_cko,
sum(if(date(s.datetime) BETWEEN date_add(dateaccessioned, interval 23 month) and date_add(dateaccessioned, interval 24 month) AND s.type='issue',1,0)) as 24_mo_cko
FROM items i
 LEFT JOIN statistics s USING (itemnumber)
WHERE date(dateaccessioned) BETWEEN '2021-07-01' and '2023-06-30' and barcode is not null
GROUP BY itemnumber
HAVING months_in_collection > 0) boop
=== 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>>
==== Missing in transit ====
* Developer: George H. Williams (Next Search Catalog / Northeast Kansas Library System)
* Module: Circulation > Transfers
* Purpose: Generates list of items that have been in transit for more than 7 days and creates a mailto link that allows you to quickly inform the owning, shipping, and recieving libraries that the item is missing
* Version: Tested and working on Koha 23.05
SELECT 
 Concat( 
 '<a class="btn btn-default btn-xs noprint"', 
 'href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
 item_info.biblionumber, 
 '\" target="_blank">BIBLIO</a>' 
 ) AS 'LINK', 
 item_info.HOME_LIBRARY, 
 item_info.branchname AS CURRENT_LIBRARY, 
 item_info.LOCATION, 
 item_info.ITYPE, 
 item_info.CCODE, 
 item_info.CALL_NUMBER, 
 item_info.author, 
 item_info.TITLE, 
 item_info.datelastseen, 
 item_info.barcode1, 
 item_info.homebranch AS OWNED_BY, 
 frombranches.branchname AS SENT_FROM, 
 branchtransfers.datesent AS SENT_DATE, 
 tobranches.branchname AS SENT_TO, 
 branchtransfers.reason AS TRANSFER_REASON, 
 Concat_WS(, 
 '<a class="btn btn-default btn-xs noprint" ', 
 'href="mailto:', 
 item_info.branchemail, 
 '?subject=Missing in transit ', 
 branchtransfers.branchtransfer_id, 
 '&cc=', 
 frombranches.branchemail, 
 ';', 
 tobranches.branchemail, 
 '&body=An%20item%20owned%20by%20**', 
 item_info.HOME_LIBRARY, 
 '**%20was%20shipped%20from%20**', 
 frombranches.branchname, 
 '**%20to%20**', 
 tobranches.branchname, 
 '**%20on%20', 
 branchtransfers.datesent, 
 '%20and%20the%20item%20has%20not%20yet%20arrived.', 
 '%0D%0A%0D%0A', 
 'The%20details%20are%20as%20follow%3A', 
 '%0D%0A%0D%0A', 
 'Branch%20transfer%20ID%3A%20', 
 branchtransfers.branchtransfer_id, 
 '%0D%0A%0D%0A', 
 'Home%20library%3A%20', 
 item_info.HOME_LIBRARY, 
 '%0D%0ALocation%3A%20', 
 item_info.LOCATION, 
 '%0D%0AItem%20type%3A%20', 
 item_info.ITYPE, 
 '%0D%0ACollection%20code%3A%20', 
 item_info.CCODE, 
 '%0D%0ACall%20number%3A%20', 
 item_info.CALL_NUMBER, 
 '%0D%0AAuthor%3A%20', 
 item_info.author, 
 '%0D%0ATitle%3A%20', 
 UPPER(REPLACE(item_info.TITLE, '&', 'and')), 
 '%0D%0ABarcode%20number%3A%20', 
 item_info.barcode, 
 '%0D%0A%0D%0A', 
 'Sent%20from%3A%20', 
 frombranches.branchname, 
 '%0D%0ASent%20to%3A%20', 
 tobranches.branchname, 
 '%0D%0ASent%20on%3A%20', 
 branchtransfers.datesent, 
 '%0D%0A%0D%0A', 
 'Transfer%20reason%3A%20', 
 branchtransfers.reason, 
 '%20%0D%0A%0D%0A', 
 'Could%20you%20please%20check%20the%20shelves%20at%20your%20library%20', 
 '(including%20the%20hold%20shelf)%20', 
 'and%20check%20this%20item%20in%20if%20you%20find%20it?', 
 '%0D%0A%0D%0A', 
 'Thank you,', 
 '%20%0D%0A%0D%0A', 
 '">Send e-mail</a>' 
 ) AS MAILTO_LINK 
FROM 
 branchtransfers JOIN 
 branches frombranches ON branchtransfers.frombranch = frombranches.branchcode JOIN 
 branches tobranches ON branchtransfers.tobranch = tobranches.branchcode JOIN 
 (SELECT 
 items.biblionumber, 
 items.itemnumber, 
 items.barcode, 
 home.branchname AS HOME_LIBRARY, 
 holding.branchname, 
 perm_locs.lib AS PERM_LOCATION, 
 Concat_Ws(, 
 perm_locs.lib, 
 If(locs.lib = 'Recently returned', ' (Recently returned)', ) 
 ) AS LOCATION, 
 itemtypes.description AS ITYPE, 
 ccodes.lib AS CCODE, 
 items.itemcallnumber, 
 Concat_Ws(, 
 items.itemcallnumber, 
 If(items.copynumber IS NULL, , Concat(' (Copy number: ', items.copynumber, ')')) 
 ) AS CALL_NUMBER, 
 biblio.author, 
 Concat_Ws(' ', biblio.title, biblio.medium, biblio.subtitle, 
 biblioitems.number, biblio.part_name) AS TITLE, 
 items.datelastseen, 
 items.copynumber, 
 Concat('-', items.barcode, '-') AS barcode1, 
 items.homebranch, 
 home.branchemail 
 FROM 
 items JOIN 
 biblio ON items.biblionumber = biblio.biblionumber JOIN 
 biblioitems ON biblioitems.biblionumber = biblio.biblionumber LEFT JOIN 
 (SELECT 
 authorised_values.category, 
 authorised_values.authorised_value, 
 authorised_values.lib, 
 authorised_values.lib_opac 
 FROM 
 authorised_values 
 WHERE 
 authorised_values.category = 'LOC' 
 ) 
 perm_locs ON perm_locs.authorised_value = items.permanent_location LEFT JOIN 
 (SELECT 
 authorised_values.category, 
 authorised_values.authorised_value, 
 authorised_values.lib, 
 authorised_values.lib_opac 
 FROM 
 authorised_values 
 WHERE 
 authorised_values.category = 'LOC' 
 ) locs ON locs.authorised_value = items.location LEFT JOIN 
 itemtypes ON itemtypes.itemtype = items.itype LEFT JOIN 
 (SELECT 
 authorised_values.category, 
 authorised_values.authorised_value, 
 authorised_values.lib, 
 authorised_values.lib_opac 
 FROM 
 authorised_values 
 WHERE 
 authorised_values.category = 'CCODE' 
 ) 
 ccodes ON 
 ccodes.authorised_value = items.ccode LEFT JOIN 
 branches home ON home.branchcode = items.homebranch LEFT JOIN 
 branches holding ON holding.branchcode = items.holdingbranch 
 GROUP BY 
 items.biblionumber, 
 items.itemnumber 
 ) 
 item_info ON item_info.itemnumber = branchtransfers.itemnumber 
WHERE 
 branchtransfers.datearrived IS NULL AND 
 branchtransfers.datecancelled IS NULL AND 
 Concat_Ws(' | ', 
 item_info.homebranch, 
 branchtransfers.frombranch, 
 branchtransfers.tobranch 
 ) LIKE Concat('%', <<Choose your library|branches>>, '%') AND 
 branchtransfers.datesent < CurDate() - INTERVAL 7 DAY 
GROUP BY 
 item_info.biblionumber, 
 item_info.itemnumber 
ORDER BY 
 item_info.PERM_LOCATION, 
 item_info.ITYPE, 
 item_info.CCODE, 
 item_info.itemcallnumber, 
 item_info.author, 
 item_info.TITLE, 
 item_info.copynumber 
==== Requests loaned and borrowed last month ====
* Developer: George Williams (NEKLS/Next Search Catalog)
* Module: Circulation > Transfers
* Purpose: Counts the items loaned to and borrowed from other libraries 
* Creation Date: 2024-09-11
* Status: Complete
* Version: Developed on Koha 16.05 - Tested and working on Koha 23.05
SELECT 
 branches.branchname, 
 Coalesce( REQUESTS_LOANED.count, 0) AS REQUESTS_LOANED_LM, 
 Coalesce(REQUESTS_BORROWED.count, 0) AS REQUESTS_BORROWED_LM 
FROM 
 branches 
 LEFT JOIN 
 ( 
 SELECT 
 items.homebranch, 
 COUNT(*) AS count 
 FROM 
 branchtransfers LEFT JOIN items 
 ON branchtransfers.itemnumber = items.itemnumber 
 WHERE 
 items.homebranch <> branchtransfers.tobranch AND 
 Year(branchtransfers.datesent) = Year(Now() - INTERVAL 1 MONTH) AND 
 Month(branchtransfers.datesent) = Month(Now() - INTERVAL 1 MONTH) AND 
 branchtransfers.tobranch <> branchtransfers.frombranch AND 
 branchtransfers.comments IS NULL AND 
 branchtransfers.reason = 'reserve' 
 GROUP BY 
 items.homebranch 
 ) REQUESTS_LOANED 
 ON branches.branchcode = REQUESTS_LOANED.homebranch 
 LEFT JOIN 
 ( 
 SELECT 
 branchtransfers.tobranch, 
 COUNT(*) AS count 
 FROM 
 branchtransfers LEFT JOIN items 
 ON branchtransfers.itemnumber = items.itemnumber 
 WHERE 
 branchtransfers.tobranch <> items.homebranch AND 
 Year(branchtransfers.datearrived) = Year(Now() - INTERVAL 1 MONTH) AND 
 Month(branchtransfers.datearrived) = Month(Now() - INTERVAL 1 MONTH) AND 
 branchtransfers.frombranch <> branchtransfers.tobranch AND 
 branchtransfers.comments IS NULL AND 
 branchtransfers.reason = 'reserve' 
 GROUP BY 
 branchtransfers.tobranch 
 ) REQUESTS_BORROWED 
 ON branches.branchcode = REQUESTS_BORROWED.tobranch 
GROUP BY 
 branches.branchname 
ORDER BY 
 branches.branchname