SQL Reports Circulation
Jump to navigation
Jump to search
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('<strong>',s.type,'</strong>') AS ' ',
CONCAT('<em>',b.branchname,'</em>') AS 'BRANCH',
SUM(IF(MONTHNAME(s.datetime) = 'January', 1, 0)) AS 'January',
SUM(IF(MONTHNAME(s.datetime) = 'February', 1, 0)) AS 'February',
SUM(IF(MONTHNAME(s.datetime) = 'March', 1, 0)) AS 'March',
SUM(IF(MONTHNAME(s.datetime) = 'April', 1, 0)) AS 'April',
SUM(IF(MONTHNAME(s.datetime) = 'May', 1, 0)) AS 'May',
SUM(IF(MONTHNAME(s.datetime) = 'June', 1, 0)) AS 'June',
SUM(IF(MONTHNAME(s.datetime) = 'July', 1, 0)) AS 'July',
SUM(IF(MONTHNAME(s.datetime) = 'August', 1, 0)) AS 'August',
SUM(IF(MONTHNAME(s.datetime) = 'September', 1, 0)) AS 'September',
SUM(IF(MONTHNAME(s.datetime) = 'October', 1, 0)) AS 'October',
SUM(IF(MONTHNAME(s.datetime) = 'November', 1, 0)) AS 'November',
SUM(IF(MONTHNAME(s.datetime) = 'December', 1, 0)) AS 'December',
COUNT(*) AS 'TOTAL',
CONCAT('<strong>',s.type,'</strong>') AS ' '
FROM statistics s, branches b
WHERE s.branch = b.branchcode
AND s.type IN ('renew', 'return', 'issue')
AND YEAR(s.datetime)=@CurrentYear:=<<year>>
GROUP BY b.branchname, s.type
UNION
SELECT
NULL AS ' ',
NULL AS 'BRANCH',
NULL AS 'January',
NULL AS 'February',
NULL AS 'March',
NULL AS 'April',
NULL AS 'May',
NULL AS 'June',
NULL AS 'July',
NULL AS 'August',
NULL AS 'September',
NULL AS 'October',
NULL AS 'November',
NULL AS 'December',
NULL AS 'TOTAL',
NULL AS ' '
UNION
SELECT DISTINCT
CONCAT('<strong>',c.type,'</strong>') AS ' ',
'<em>ALL BRANCHES</em>' AS 'BRANCH',
SUM(IF(MONTHNAME(c.datetime) = 'January', 1, 0)) AS 'January',
SUM(IF(MONTHNAME(c.datetime) = 'February', 1, 0)) AS 'February',
SUM(IF(MONTHNAME(c.datetime) = 'March', 1, 0)) AS 'March',
SUM(IF(MONTHNAME(c.datetime) = 'April', 1, 0)) AS 'April',
SUM(IF(MONTHNAME(c.datetime) = 'May', 1, 0)) AS 'May',
SUM(IF(MONTHNAME(c.datetime) = 'June', 1, 0)) AS 'June',
SUM(IF(MONTHNAME(c.datetime) = 'July', 1, 0)) AS 'July',
SUM(IF(MONTHNAME(c.datetime) = 'August', 1, 0)) AS 'August',
SUM(IF(MONTHNAME(c.datetime) = 'September', 1, 0)) AS 'September',
SUM(IF(MONTHNAME(c.datetime) = 'October', 1, 0)) AS 'October',
SUM(IF(MONTHNAME(c.datetime) = 'November', 1, 0)) AS 'November',
SUM(IF(MONTHNAME(c.datetime) = 'December', 1, 0)) AS 'December',
CONCAT('<strong>',COUNT(*),'</strong>') AS 'TOTAL',
CONCAT('<strong>',c.type,'</strong>') AS ' '
FROM statistics c
WHERE c.type IN ('renew', 'return', 'issue')
AND YEAR(c.datetime) = @CurrentYear
GROUP BY c.type
COUNT - Month wise circulation report for a specific year
- Developer: (1) Satisha MV - Govt. Engineering College Library, Hassan (2)Barton Chittenden (3) Mark Alexander
- Module: Circulation
- Purpose:
- Status: Complete
SELECT
monthname(datetime) AS month,
SUM( IF(type = 'issue', 1, 0 )) as Issues,
SUM( IF(type = 'renew', 1, 0 )) as Renewals,
SUM( IF(type = 'return', 1, 0 )) as Returns,
COUNT(statistics.type) AS 'Total Transactions'
FROM statistics
WHERE YEAR(datetime) = <<Enter Year YYYY>>
GROUP BY month
ORDER BY month(datetime) ASC
COUNT - Date wise daily circulation report for a specific month of an year
- Developer: (1) Satisha MV - Govt. Engineering College Library, Hassan (2)Barton Chittenden (3) Mark Alexander
- Module: Circulation
- Purpose:
- Status: Complete
SELECT
DATE(datetime) AS DATE,
SUM( IF(type = 'issue', 1, 0 )) as Issues,
SUM( IF(type = 'renew', 1, 0 )) as Renewals,
SUM( IF(type = 'return', 1, 0 )) as Returns,
COUNT(statistics.type) AS 'Total Transactions'
FROM statistics
WHERE YEAR(datetime) = <<Enter Year YYYY>> AND MONTH(datetime) = <<Enter Month MM>>
GROUP BY DATE(datetime)
Checkouts by Item Type (previous month)
- Developer: Galen Charlton, Equinox
- Module: Circ
- Purpose:
- Status: Complete
- Note: This can take a while to run because of the union of items and deleteditems, but has the advantage that items that get circulated, then deleted, during the previous month will get reported using their correct item type.
SELECT all_items.itype AS "Item Type" ,count(*) AS 'Checkouts'
FROM statistics
JOIN (
SELECT itemnumber, itype FROM deleteditems
UNION
SELECT itemnumber, itype FROM items
) AS all_items USING (itemnumber)
WHERE statistics.type = 'issue'
AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND statistics.datetime <= LAST_DAY(now() - interval 1 month)
GROUP BY all_items.itype
ORDER BY all_items.itype asc;
Monthly circ in a date range
- Developer: Kyle Hall
- Module: Circulation
- Purpose: Includes items that have since been deleted grouped by month
- Status: Complete
SELECT MONTHNAME(all_issues.issuedate) AS MONTH,
YEAR(all_issues.issuedate) AS YEAR,
count(*) AS checkouts,
SUM(all_issues.renewals) AS renewals
FROM (
(SELECT *
FROM issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
UNION
(SELECT *
FROM old_issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
) all_issues
LEFT JOIN items USING ( itemnumber )
LEFT JOIN deleteditems USING ( itemnumber )
GROUP BY
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)
ORDER BY
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)
Checked In
Date Wise List of Checked In Books
- Developer: Vimal Kumar V., Mahatma Gandhi University Library
- Module: Circulation
- Purpose: To generate date wise list of checked in books
- Example: List of checked in books between 2013-07-20 to 2013-07-25
- Status: Complete
SELECT old_issues.returndate,items.barcode,biblio.title,biblio.author,borrowers.firstname,borrowers.surname,borrowers.cardnumber,borrowers.categorycode
FROM old_issues
LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber
LEFT JOIN items ON old_issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE old_issues.returndate BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
ORDER BY old_issues.returndate DESC
Old circulation issues since the beginning
- Developer: Schnydszch
- Module: Statistical (Circulation)
- Purpose: Find all issues now closed from the start of your Koha installation. It extracts the more useful data and also the title (245$a) from biblioitems.marcxml
- Status: Complete
SELECT old_issues.*, borrowers.cardnumber, borrowers.surname,
borrowers.firstname, borrowers.sort1, borrowers.sort2, items.barcode,
items.datelastborrowed, items.datelastseen, items.itemcallnumber,
items.issues, items.holdingbranch, items.location, items.permanent_location,
items.onloan, items.ccode, items.itype, items.enumchron, items.copynumber,
items.location, b.totalissues, EXTRACTVALUE( b.marcxml, '//datafield[@tag="245"]/subfield[@code>="a"]' ) as TITLE
FROM old_issues
LEFT JOIN borrowers ON ( borrowers.borrowernumber = old_issues.borrowernumber )
LEFT JOIN items ON ( items.itemnumber = old_issues.itemnumber )
LEFT JOIN biblioitems AS b ON ( items.biblioitemnumber = b.biblioitemnumber)
Patron notes about item, shown at check in, within a date range
- Developer: Barton Chittenden, ByWater Solutions
- Module: Circulation
- Purpose: To view issue IDs, patron, items, the checkin notes they've written, and the note's date within a date range.
- Status: Complete
SELECT
coalesce( issues.issue_id, old_issues.issue_id) as issue_id,
CONCAT(borrowers.surname,', ',borrowers.firstname) AS Name,
items.itemnumber,
coalesce(issues.note, old_issues.note) AS Note,
coalesce(issues.notedate, old_issues.notedate) AS Date
FROM issues
LEFT JOIN old_issues USING(itemnumber)
LEFT JOIN items USING(itemnumber)
LEFT JOIN borrowers ON (borrowers.borrowernumber=coalesce(issues.borrowernumber, old_issues.borrowernumber))
WHERE date(coalesce(issues.notedate, old_issues.notedate)) BETWEEN <<note written on or after date|date>> AND <<and before|date>>
GROUP BY issue_id
Checked Out
All Checked Out Books
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A report to show you all items that are currently checked out and who they're checked out to
- Status: Complete
SELECT c.date_due, p.surname, p.firstname,
p.phone, p.email, b.title, b.author,
i.itemcallnumber, i.barcode, i.location
FROM issues c
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
ORDER BY c.date_due ASC
Date Wise List of Checked Out Books
- Developer: Arslan Farooq, SZABIST Islamabad Library
- Module: Circulation
- Purpose: To generate date wise list of checked out books (based on Nicole's "All Checked Out Books" report)
- Status: Complete
SELECT DATE_FORMAT(c.issuedate, "%d %b %Y %h:%i %p") AS Issue_Date, DATE_FORMAT(c.date_due, "%d %b %Y") AS Due_Date,
i.barcode AS Barcode,
b.title AS Title,
b.author AS Author,
p.cardnumber AS Card_No,
p.firstname AS First_Name,
p.surname AS Last_Name
FROM issues c
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE c.issuedate
BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> ORDER BY c.issuedate DESC
Patrons w/ Books Due Tomorrow
- Developer: Nicole C. Baratta, ByWater Solutions, Koha List
- Module: Circulation
- Purpose: List patrons with books due tommorrow
- Status: Complete
SELECT p.cardnumber, p.surname, p.branchcode, p.firstname,
co.date_due, i.barcode, b.title, b.author
FROM borrowers p
LEFT JOIN issues co ON (co.borrowernumber=p.borrowernumber)
LEFT JOIN items i ON (co.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber)
WHERE DATE(co.date_due) = DATE_ADD(curdate(), INTERVAL 1 DAY)
AND i.homebranch = <<Branch|branches>>
ORDER BY p.surname ASC
Patrons w/ Items Issued Today
- Developer: MJ Ray, software.coop
- Module: Circulation
- Purpose: List patrons and the items issued to them, to aid mailing items out
- Status: Development
SELECT
borrowers.surname,
borrowers.firstname,
borrowers.title,
borrowers.othernames,
borrowers.initials,
borrowers.streetnumber,
borrowers.streettype,
borrowers.address,
borrowers.address2,
borrowers.city,
borrowers.zipcode,
borrowers.country,
items.barcode,
items.itemcallnumber,
biblioitems.isbn,
biblioitems.issn
FROM borrowers LEFT JOIN
statistics on (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN
items on (items.itemnumber = statistics.itemnumber) LEFT JOIN
biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE
statistics.type='issue' and
statistics.datetime > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
ORDER BY items.barcode asc
Issues placed at a specific branch during a specified period
- Developer: Liz Rea, Catalyst IT (For South Taranaki District Council Libraries)
- Module: Circulation
- Purpose: lists borrowers card number, title, and barcode number of items that were issued, and the date they were issued. The person running the report is able to choose the branch and the date period when running the report.
- Example:
- Status: Complete
SELECT statistics.datetime AS 'Issue Date', borrowers.surname,borrowers.firstname, borrowers.cardnumber, items.barcode, biblio.title FROM borrowers
LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber)
LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber = items.biblionumber)
WHERE statistics.type='issue'
AND date(statistics.datetime)
BETWEEN <<Item issued BETWEEN (yyyy-mm-dd)|date>>
AND <<and (yyyy-mm-dd)|date>>
AND statistics.branch= <<Select library|branches>>
ORDER BY borrowers.surname ASC
List the items circulated from a branch
- Developer: Jameela P.
- Module: Circulation
- Purpose: Shows the total list of items circulated from a branch.
- Status: Completed
SELECT
borrowers.cardnumber AS "ID",
borrowers.title AS "RANK",
borrowers.surname AS "NAME",
borrowers.sort1 AS "TERM",
borrowers.othernames AS "PERSONAL NUMBER",
borrowers.city AS "RANK 1",
borrowers.sort2 AS "SQN/DEPT",
biblio.title AS "TITLE",
biblio.author AS "AUTHOR",
items.itemcallnumber AS "CALL NO",
items.barcode AS "BARCODE",
items.homebranch AS "HOME LIBRARY",
issues.issuedate AS "DATE OF ISSUE"
FROM
issues
LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber
LEFT JOIN items ON issues.itemnumber = items.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
WHERE
items.homebranch = <<Branch|branches>>
ORDER BY
issues.branchcode,
"TERM",
"NAME",
"DATE OF ISSUE",
"TITLE"
List of items checked out in a date range
- Developer: Nicole Engard and Christopher Brannon
- Module: Circulation
- Purpose: List of items checked out in a date range
- Status: Complete
SELECT b.title, b.author, i.itemcallnumber, i.barcode, c.issuedate
FROM (SELECT iss.issuedate, iss.itemnumber FROM issues iss,
(SELECT @StartDate:=<<Between (yyyy-mm-dd)|date>>,@EndDate:=<<and (yyyy-mm-dd)|date>>) as var
WHERE date(iss.issuedate) BETWEEN @StartDate AND @EndDate UNION ALL
SELECT oi.issuedate, oi.itemnumber FROM old_issues oi WHERE date(oi.issuedate)
BETWEEN @StartDate AND @EndDate) AS c
LEFT JOIN items i USING (itemnumber)
LEFT JOIN biblio b USING (biblionumber)
where i.homebranch=<<Item belongs to|branches>>
List of items issued on a date with optional limit by due date
- Developer: Sarah Cornell, Portsmouth Public Library
- Module: Circulation
- Purpose: Locates items with known checkout dates and/or due dates and links to patron for troubleshooting.
- Status: Completed
- Works with: 18.05
SELECT
issuedate,
date_due,
items.barcode,
items.itemcallnumber,
biblio.title,
biblio.author,
CONCAT(borrowers.firstname, ' ',borrowers.surname) AS 'patron',
borrowers.cardnumber,
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowers.borrowernumber,'\"target="_blank">', borrowers.borrowernumber, '</a>') AS borrowernumber
FROM issues
LEFT JOIN items ON issues.itemnumber = items.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
WHERE DATE(issuedate) = <<issue date|date>>
AND IF(<<Limit by due date?|YES_NO>>,DATE(date_due) = <<due date|date>>,1)
Items Checked out by Patron from Different Home Library
- Developer: Spencer Smith
- Module: Circulation
- Purpose: List of items currently checked out, limited by borrowers' home branch, items' home branch, and issuing branch.
- Status: Complete
SELECT issues, biblio.title, borrowers.branchcode as 'borrower home',items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate, issues.branchcode as 'check out branch', items.homebranch as 'item home location'
FROM issues
LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber
LEFT JOIN items ON issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE items.homebranch=<<item home branch|branches>>
AND borrowers.branchcode=<<borrower home branch|branches>>
AND issues.branchcode=<<checkout branch|branches>>
ORDER BY items.itemcallnumber, biblio.title
Circulation by Location
By Call Number
Checkouts by Call Number (previous month)
- Developer: Jane Wagner, PTFS
- Module: Circ
- Purpose:
- Status: Complete
SELECT count(statistics.type) AS 'Checkouts',items.itemcallnumber
FROM borrowers
LEFT JOIN statistics on (statistics.borrowernumber=borrowers.borrowernumber)
LEFT JOIN items on (items.itemnumber = statistics.itemnumber)
LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE statistics.type = 'issue'
AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND statistics.datetime <= LAST_DAY(now() - interval 1 month)
GROUP BY items.itemcallnumber
ORDER BY items.itemcallnumber asc
Renewals by Call Number (previous month)
- Developer: Jane Wagner, PTFS
- Module: Circ
- Purpose:
- Status: Complete
SELECT count(statistics.type) AS 'Renewals',items.itemcallnumber
FROM borrowers
LEFT JOIN statistics on (statistics.borrowernumber=borrowers.borrowernumber)
LEFT JOIN items on (items.itemnumber = statistics.itemnumber)
LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE statistics.type = 'renew'
AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND statistics.datetime <= LAST_DAY(now() - interval 1 month)
GROUP BY items.itemcallnumber
ORDER BY items.itemcallnumber asc
Checkouts & Renewal Counts by Call Number
- Developer: VOKAL
- Module: Circulation
- Purpose: A count of checkouts and renewals (and a total of both) in a specific month.
- Status: Complete
SELECT LEFT(i.itemcallnumber,3) AS "Call No.",
SUM(IF(s.type = 'issue', 1, 0)) AS Checkout,
SUM(IF(s.type = 'renew', 1, 0)) AS Renewal,
SUM(IF((s.type = 'renew' OR s.type='issue'), 1, 0)) AS TOTAL
FROM items i
LEFT JOIN statistics s
ON i.itemnumber=s.itemnumber
WHERE year(s.datetime)=<<Year (yyyy)>> AND month(s.datetime)=<<Month (mm)>>
AND i.homebranch=<<Branch|branches>>
GROUP BY LEFT(i.itemcallnumber,3)
WITH ROLLUP
Count of Circ by Alpha Call Number Prefix
- Developer: Jared Camins-Esakov and Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A statistical report showing how many items with a specific alphabetical prefix have circulated in a month
- Status: Complete
SELECT SUBSTRING_INDEX(itemcallnumber, ' ', 1) AS 'Call# range', count(*) AS count
FROM statistics
LEFT JOIN items USING (itemnumber)
WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = <<Year (yyyy)>> and
MONTH(datetime) = <<Month (mm)>> AND SUBSTRING_INDEX(itemcallnumber, ' ', 1) rlike '[a-z]'
GROUP BY SUBSTRING_INDEX(itemcallnumber, ' ', 1)
Circulation of Two Call Numbers
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: Circulation using two call numbers (or call number ranges) for a specific time frame
- Example: Using YA and J as the call numbers gets you all Juvenile materials if your library uses Dewey because the wildcard is being added in by default
- Status: Complete
SELECT s.branch, month(s.datetime) AS month,
year(s.datetime) AS year, count(s.datetime) AS count
FROM statistics s
LEFT JOIN items i using (itemnumber)
WHERE s.type LIKE 'issue'
AND (i.itemcallnumber LIKE concat(<<Call number like>>, '%') OR i.itemcallnumber LIKE concat(<<Second call number like>>, '%')
AND date(s.datetime) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY s.branch, year, month
ORDER BY year, month DESC, s.branch ASC
List that totals the circulation of each Dewey section, F, and periodicals, by month
- Developer: Joe Atzberger
- Module: Statistical (Circulation, Reports)
- Purpose: List that totals the circulation of each Dewey section, F, and periodicals, by month
- Status: Complete
SELECT DATE(datetime) AS date, substring(itemcallnumber,1,1) as 'Call# range', count(*) AS count
FROM statistics
LEFT JOIN items USING (itemnumber)
WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = <<Year (yyyy)>> and MONTH(datetime) = <<Month (mm)>>
GROUP BY DATE(datetime), substring(itemcallnumber,1,1)
List that totals the circulation of each Dewey section, F, and periodicals, by day
- Developer: Joe Atzberger, Scotty Zollars
- Module: Statistical (Circulation, Reports)
- Purpose: List that totals the circulation of each Dewey section, F, and periodicals, by day
- Status: Complete
SELECT DATE(datetime) AS date, substring(itemcallnumber,1,1) as 'Call# range', count(*) AS count
FROM statistics
LEFT JOIN items USING (itemnumber)
WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = <<Year (yyyy)>> and MONTH(datetime) = <<Month (mm)>> and DAY(datetime) = <<Day (dd)|date>>
GROUP BY DATE(datetime), substring(itemcallnumber,1,1)
Circulation grouped by Dewey decimal range, select date range
- Developer: Barton Chittenden
- Module: Circulation
- Purpose: Total circulation activities in a date range
- Status: Complete
select
CONCAT(
( LEFT(i.cn_sort, 3) DIV 100 ) * 100,
' - ',
( LEFT(i.cn_sort, 3) DIV 100 ) * 100 + 99
) as 'Callnuber range',
COUNT(checkouts.datetime) as checkouts,
COUNT(renewals.datetime) as renewals,
COUNT(localuse.datetime) as localuse,
COUNT(checkouts.datetime) + COUNT(renewals.datetime) as total
FROM
items i
LEFT JOIN statistics s using (itemnumber)
LEFT JOIN statistics checkouts on (checkouts.type = 'issue' and checkouts.itemnumber=i.itemnumber and s.datetime=checkouts.datetime )
LEFT JOIN statistics renewals on (renewals.type = 'renew' and renewals.itemnumber=i.itemnumber and s.datetime=renewals.datetime )
LEFT JOIN statistics localuse on (localuse.type = 'localuse' and localuse.itemnumber=i.itemnumber and s.datetime=localuse.datetime )
WHERE
i.cn_source = 'ddc'
AND date(s.datetime) between <<circulated between|date>> and <<and|date>>
GROUP BY ( LEFT(i.cn_sort, 3) DIV 100 ) * 100
ORDER BY ( LEFT(i.cn_sort, 3) DIV 100 ) * 100
By Shelving Location
Checkout by Shelving Location
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A count of checkouts by shelving location at a specific branch in a specific timeframe.
- Status: Complete
select i.location, count(s.datetime) as count
from items i
left join statistics s using (itemnumber)
where date(s.datetime) between <<Date between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
and s.type='issue' and s.branch=<<Pick your branch|branches>>
group by i.location
order by i.location asc
Checkins by Shelving Location
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A count of checkins by shelving location at a specific branch in a specific timeframe.
- Status: Complete
select i.location, count(s.datetime) as count
from items i
left join statistics s using (itemnumber)
where date(s.datetime) between <<Date between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
and s.type='return' and s.branch=<<Pick your branch|branches>>
group by i.location
order by i.location asc
Renewals by Shelving Location
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A count of renewals by shelving location at a specific branch in a specific timeframe.
- Status: Complete
select items.location, count(statistics.datetime) as count
from items left join statistics using (itemnumber)
where date(statistics.datetime) between <<Date between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
and statistics.type='renew' and statistics.branch=<<Pick your branch|branches>>
group by items.location
order by items.location asc
Circ in a date range by shelving location
- Developer: Kyle Hall
- Module: Circulation
- Purpose: Includes items that have since been deleted and will ask if you want to limit by location or not, grouped by month
- Status: Complete
SELECT COALESCE(items.location, deleteditems.location) AS LOCATION, MONTHNAME(all_issues.issuedate) AS MONTH,
YEAR(all_issues.issuedate) AS YEAR,
count(*) AS checkouts,
SUM(all_issues.renewals) AS renewals
FROM (
(SELECT *
FROM issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
UNION
(SELECT *
FROM old_issues
WHERE branchcode=<<Library|branches>>
AND issuedate BETWEEN <<Between this date (YYYY-MM-DD)|date>> AND <<and this date (YYYY-MM-DD)|date>>)
) all_issues
LEFT JOIN items USING ( itemnumber )
LEFT JOIN deleteditems USING ( itemnumber )
WHERE
IF( <<limit by location|YES_NO>>, COALESCE( items.location, deleteditems.location ) = <<location|LOC>>,1 )
GROUP BY
COALESCE( items.location, deleteditems.location),
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)
ORDER BY
COALESCE( items.location, deleteditems.location),
YEAR(all_issues.issuedate),
MONTH(all_issues.issuedate)
Circulation stats, choose date range, shelving location, itemtype
- Developer: Liz Rea, Catalyst IT, and David Cook, Prosentient, for Horowhenua Library Trust
- Module: Circulation
- Purpose: Generate a statistical report for all itemtypes, per branch
- Example:
- Status: Complete
SELECT
bib.title,
i.itype,
i.location,
SUM(IF(s.type = 'issue', 1, 0)) AS Checkout,
SUM(IF(s.type = 'renew', 1, 0)) AS Renewal,
SUM(IF((s.type = 'renew' OR s.type='issue'), 1, 0)) AS TOTAL
FROM
statistics s
LEFT JOIN items i using (itemnumber)
LEFT JOIN biblio bib using (biblionumber)
WHERE
date(s.datetime) between <<Between|date>> and <<and|date>>
AND i.itype = <<Item type|itemtypes>>
AND i.location = <<Location|loc>>
GROUP BY biblionumber
Local Use
Track In House Use
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: Find out local use circ numbers for a specific time frame
- Status: Complete
select count(*)
from statistics
where type='localuse'
and datetime between <<Local Use between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
Track In House Use for a Title
- Developer: Denise Hannibal
- Module: Circulation
- Purpose: Number of in house uses for a barcode (track Reference Collection use)
- Status: Complete
SELECT i.location, i.barcode, i.itemcallnumber, b.title, count(s.datetime) AS count
FROM items i LEFT JOIN statistics s USING (itemnumber) LEFT JOIN biblio b on (i.biblionumber=b.biblionumber)
WHERE s.type='localuse' AND s.branch=<<Enter the branch>> AND i.barcode=<<Enter the barcode to check in house use>>
GROUP BY i.itemnumber
Local Use by Shelving Location
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A count of in house use by shelving location at a specific branch in a specific timeframe.
- Status: Complete
SELECT i.location, count(s.datetime) AS count
FROM items i LEFT JOIN statistics s USING (itemnumber)
WHERE date(s.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
AND s.type='localuse' AND s.branch=<<Pick your branch|branches>>
GROUP BY i.location
ORDER BY i.location ASC
Naughty Overdues
Overdue materials
- Developer: Sharon Moreland
- Module: Circulation
- Purpose: Overdue materials
- Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.phone, borrowers.cardnumber,
borrowers.address, borrowers.city, borrowers.zipcode, issues.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) as 'days overdue', items.itype,
items.itemcallnumber, items.barcode, items.homebranch, biblio.title, biblio.author
FROM borrowers
LEFT JOIN issues on (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items on (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio on (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > '30' AND issues.branchcode = <<Issuing branch|branches>>
ORDER BY borrowers.surname asc, issues.date_due asc
Long Overdues
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: Items that are long overdue
- Status: Complete
SELECT p.surname, p.firstname, p.phone, p.cardnumber,
c.date_due, b.title, b.author, i.itemcallnumber, i.barcode,
i.homebranch, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue'
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > <<Due date more than (enter in days)>>
AND (TO_DAYS(curdate())-TO_DAYS(date_due)) < <<Due date less than (enter in days)>>
ORDER BY p.surname ASC, c.date_due ASC
Overdues w/ Contact Info
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A report that shows items overdue more than a specific number of days for contacting the patrons.
- Status: Complete
SELECT p.cardnumber, p.surname, p.firstname,
p.phone, p.email, c.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
b.title, b.author, i.itemcallnumber,
i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>>
ORDER BY p.surname ASC, p.firstname ASC, c.date_due ASC
Overdues Call List w/ Guarantor
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A report of patrons to call with overdues. Will show guarantor for the young patrons.
- Status: Complete
SELECT
p.surname,
p.firstname,
p.phone,
p.phonepro as 'second phone',
b.title,
b.author,
i.barcode,
c.date_due,
i.replacementprice,
p.categorycode,
IFNULL(concat(g.surname, ', ', g.firstname, ' (', g.cardnumber, ')'),'') AS guarantor,
ifnull(p.relationship,'') as relationship
FROM issues c left join
items i using (itemnumber) left join
biblio b using (biblionumber) LEFT JOIN
borrowers p using (borrowernumber) LEFT JOIN
borrowers g ON (p.guarantorid=g.borrowernumber)
WHERE c.date_due < NOW()
GROUP BY p.borrowernumber
ORDER BY p.cardnumber ASC
Overdues by Homeroom/Attribute
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A report that shows overdues alongside patron attributes (in this case GRADELVL and HOMEROOM)
- Status: Complete
SELECT p.cardnumber, p.surname, p.firstname,
c.date_due, g.attribute as grade, h.attribute as teacher,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
b.title, b.author, i.itemcallnumber,
i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
left join borrower_attributes g on (p.borrowernumber=g.borrowernumber)
left join borrower_attributes h on (p.borrowernumber=h.borrowernumber)
WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>> and g.code="GRADELVL" and h.code="HOMEROOM"
ORDER BY g.attribute, h.attribute, p.surname ASC
Overdues w/item info when patron has no email
- Developer: Doug Kingston
- Module: Circulation
- Purpose: A report that shows items overdue more than a specific number of days for patrons with no email address.
- Status: Complete
SELECT p.cardnumber, p.surname, p.firstname,
p.phone, p.email, c.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
b.title, b.author, i.itemcallnumber,
i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>>
AND p.email = ""
ORDER BY p.surname ASC, p.firstname ASC, c.date_due ASC
Overdues by Item Type
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: A report that shows overdue items based on item type.
- Status: Complete
SELECT p.cardnumber, p.surname, p.firstname,
p.phone, p.address, p.city, p.zipcode,
c.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
b.title, b.author, i.itemcallnumber,
i.barcode
FROM borrowers p
LEFT JOIN issues c using (borrowernumber)
LEFT JOIN items i using (itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE i.itype = <<Item Type|itemtypes>> and c.date_due < curdate()
ORDER BY p.surname ASC, c.date_due ASC
Detailed report of long-overdues charged-off in the last week
- Developer: D Ruth Bavousett, ByWater Solutions
- Module: Circulation
- Purpose: List all borrowers/items that have been marked as Lost--and remain unpaid--from the last seven days.
- Status: Completed
SELECT cardnumber AS "Borrower Barcode",
surname AS "Last Name",
firstname AS "First Name",
ROUND(amountoutstanding,2) AS "Amount Due",
biblio.title AS "Title",
author AS "Author",
barcode AS "Item Barcode"
FROM accountlines
JOIN borrowers USING (borrowernumber) LEFT JOIN items USING (itemnumber) JOIN biblio USING (biblionumber)
WHERE accounttype = "L" AND date > DATE_SUB(CURRENT_DATE(),INTERVAL 7 DAY)
List of current checked out books; branch wise
- Developer: Jameela P.; Panini Library
- Module: Circulation
- Purpose: To generate branch wise current checked out books list
- Status: Complete
SELECT
borrowers.cardnumber,
borrowers.title,
borrowers.surname,
borrowers.sort1,
borrowers.othernames,
biblio.title AS "TITLE",
author AS "AUTHOR",
items.itemcallnumber AS "CALL NO",
items.barcode AS "BARCODE",
items.homebranch AS "HOME LIBRARY",
issues.issuedate AS "DATE OF ISSUE"
FROM issues LEFT JOIN
borrowers ON borrowers.borrowernumber=issues.borrowernumber LEFT JOIN
items ON issues.itemnumber=items.itemnumber LEFT JOIN
biblio ON items.biblionumber=biblio.biblionumber
WHERE items.homebranch = <<Branch|branches>>
ORDER BY
issues.branchcode,
borrowers.sort1,
borrowers.surname,
issues.issuedate,
biblio.title
List of current checked out books; branch wise within a date range
- Developer: Jameela P.; Panini Library
- Module: Circulation
- Purpose: To generate branch wise current checked out books list within a date range.
- Status: Complete
SELECT
borrowers.cardnumber,
borrowers.title,
borrowers.surname,
borrowers.sort1,
borrowers.othernames,
biblio.title AS "TITLE",
author AS "AUTHOR",
items.itemcallnumber AS "CALL NO",
items.barcode AS "BARCODE",
items.homebranch AS "HOME LIBRARY",
issues.issuedate AS "DATE OF ISSUE"
FROM issues
LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber
LEFT JOIN items ON issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE
items.homebranch =<<Branch|branches>> AND
date(issuedate) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY
itemcallnumber,
barcode
List of total check-out books (which are not returned, sort datewise and branchwise)
- Developer: Nikunj Tyagi, Delhi Public Library
- Module: Circulation
- Purpose: To generate checked out books list which not recieved back, sort by days and Branch wise
- Example: List of not retured books from 10, 20, 30 days and in particular branch, type branch code
- Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber,
issues.date_due,(TO_DAYS(curdate())-TO_DAYS(date_due)) AS 'days overdue',items.itemcallnumber, items.barcode, biblio.title, biblio.author
FROM borrowers
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= <<Books not received (XX) Days>> AND homebranch =<<Type Homebranch code(XXXX)>>
ORDER BY issues.date_due asc, borrowers.surname ASC
Overdues at a Specific Branch
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: Patron and item info for books that are overdue from one specific branch.
- Status: Complete
SELECT p.cardnumber, p.surname, p.firstname,
p.phone, p.address, p.city,
p.zipcode, c.issuedate, c.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', b.title,
i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE c.branchcode = <<Branch|branches>> and
(TO_DAYS(curdate())-TO_DAYS( date_due)) > 0
ORDER BY p.surname ASC, c.date_due ASC
Weeding / Low Circulating Items
Not Circulating Items (Date Specific)
- Developer: Bev Church, Joe Tholen
- Module: Circulation
- Purpose: List items not circulated in specific date range, by shelf location (weeding tool)
- Status: Needs Work
SELECT barcode, homebranch as 'branch', itemcallnumber, title
FROM biblio, items
WHERE items.biblionumber = biblio.biblionumber AND homebranch = <<Home branch|branches>> AND location = <<Shelving location|LOC>> AND itemnumber NOT IN
(SELECT itemnumber FROM issues) UNION
(SELECT barcode, homebranch AS 'branch', itemcallnumber, title
FROM biblio, items
WHERE items.biblionumber = biblio.biblionumber AND homebranch = <<Home branch again|branches>> AND location = <<Shelving location again|LOC>> AND itemnumber NOT IN
(SELECT itemnumber FROM old_issues WHERE date(issuedate) BETWEEN <<Checked out between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>) )
ORDER BY itemcallnumber, barcode
Not Circulated items
- Developer: Abdullrahman Hegazy, BUE Library
- Module: Circulation
- Purpose: List of low or not circulated records, Considering the number of items in the records and the adding date of the items.
- Status: Ongoing
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',items.biblionumber,'\">',biblio.title,'</a>') AS Record, COUNT(items.biblionumber) AS "Num of items", items.itemcallnumber
FROM items
LEFT JOIN
(
SELECT DISTINCT issues.itemnumber
FROM issues
UNION
SELECT DISTINCT old_issues.itemnumber
FROM old_issues
UNION
SELECT DISTINCT statistics.itemnumber
FROM statistics
) AS inumber ON ( items.itemnumber = inumber.itemnumber)
LEFT JOIN biblio ON ( items.biblionumber = biblio.biblionumber)
WHERE inumber.itemnumber IS NULL
AND items.itype = <<Item type|itemtypes>>
AND (items.itemlost < 1 OR items.itemlost IS NULL)
AND dateaccessioned < <<Adding date Before (yyyy-mm-dd)|date>>
GROUP BY Record
ORDER BY COUNT(items.biblionumber) DESC, items.itemcallnumber ASC
Non Circulating Items
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: List items that have never circulated
- Status: Complete
SELECT b.title, b.author, i.barcode, i.itemcallnumber
from items i
left join biblio b using (biblionumber)
WHERE i.itemnumber NOT IN (SELECT itemnumber FROM issues where itemnumber is not null)
and i.itemnumber NOT IN (SELECT itemnumber FROM old_issues where itemnumber is not null)
Non Circulating Items in X Years
- Developer: Marion J. Makarewicz and Nicole C. Baratta, ByWater Solutions
- Module: Circulation
- Purpose: List items that have not circulated in X Years
- Status: Complete
SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber,
i.barcode, i.datelastborrowed, i.issues as totalcheckouts,
i.dateaccessioned
FROM items i
LEFT JOIN issues
USING (itemnumber)
LEFT JOIN biblio b
USING (biblionumber)
WHERE i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues)
AND YEAR(NOW())-YEAR(i.datelastborrowed) > <<Years not circulated>>
UNION
SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber,
i.barcode, i.datelastborrowed, i.issues as totalcheckouts,
i.dateaccessioned
FROM items i
LEFT JOIN issues
USING (itemnumber)
LEFT JOIN biblio b
USING (biblionumber)
WHERE i.itemnumber NOT IN (SELECT old_issues.itemnumber FROM old_issues)
AND YEAR(NOW())-YEAR(i.datelastborrowed) > <<Years not circulated (again)>>
Non Circulating Items in X Years for a specific branch also lists other items at other branches and their status
- Developer: Brenda Turnbull LiveWire CIC
- Module: Circulation
- Purpose: List items that have not circulated in X Years for a specifcic branch then lists all other copies held at other branches and their status. It selection is resctricted to certain Locations and item types (where AF and JF are Adult Fiction/Junior Fiction) - which you will need to alter to your values.
- Status: Complete **** WARNING ***** Run with care as it may slow down your database
SELECT b.title as Title,
b.author as Author,
b.copyrightdate AS CopyrightDate,
i.itemcallnumber AS CallNo,
i.itype AS ItemType,
i.homebranch As Branch,
i.location AS Loc,
i.biblionumber AS BibNo,
i.barcode AS BarcodeNo,
i.datelastborrowed AS lastBorrowed,
i.issues AS totalCheckouts,
i.dateaccessioned AS Accessioned,
(SELECT GROUP_CONCAT( ItemInnerDets.ItemDetails ORDER BY ItemInnerDets.ItemDetails DESC SEPARATOR '</br>')
FROM
(SELECT itemsInner.biblionumber, itemsInner.homebranch, itemsInner.datelastborrowed, itemsInner.barcode,
CONCAT(itemsInner.homebranch,' ',itemsInner.barcode,' ', itemsInner.location,
(CONCAT(
(IF (itemsInner.withdrawn > 0, ' withdrawn ',
(IF (itemsInner.itemlost > 0, ' missing ',
(IF (itemsInner.damaged > 0 , ' damaged ', ' ')))
)
)
),
(IF(itemsInner.onloan IS NULL,' Available ' , ' OnLoan ')),
(IF (itemsInner.notforloan > 0, ' REF item ',' '))
)),
' last borrowed: ',itemsInner.datelastborrowed,' No. issues: ', itemsInner.issues) AS ItemDetails
FROM items itemsInner ) ItemInnerDets
WHERE ItemInnerDets.biblionumber = i.biblionumber AND ItemInnerDets.barcode <> i.barcode ORDER BY ItemInnerDets.ItemDetails
) AS Also_Available_AT
FROM items i
LEFT JOIN biblio b USING (biblionumber)
WHERE YEAR(NOW())-YEAR(i.datelastborrowed) > <<Years NOT circulated>> AND i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues)
AND i.itype IN ('AF','JF')
AND i.location IN ('A', 'BEGIN-READ','F','JSPN','FC','G','SFG','W','H','X','LP','P','PROA','PRO-ANF','PROJ','R','SF','JSSC','Y','T','TC')
Non/Low Circulating New items Items for a selected Branch
- Developer: Brenda Turnbull LiveWire CIC
- Module: Circulation
- Purpose: Selects items that have been received into a branch in the last 12 months. It ignores items that are currently on loan or in transit. It shows items that have had only 1 issue or none at all.
- Status: Complete **** WARNING ***** Run with care as it may slow down your database
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS 'View Bib Item detail',
i.homebranch, b.title, b.author, i.barcode,i.itype, i.location, a.datereceived ,i.datelastseen,
( SELECT Count(statistics.itemnumber) from statistics
WHERE statistics.itemnumber = i.itemnumber AND statistics.type IN ('issue','renew') ) AS circs
FROM items i
LEFT JOIN biblioitems bi USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
LEFT JOIN aqorders a ON (i.biblionumber=a.biblionumber)
LEFT JOIN branchtransfers bt ON ( i.itemnumber = bt.itemnumber)
WHERE DATE( a.datereceived ) >= DATE_SUB(CURDATE(),INTERVAL 18 MONTH) AND DATE( a.datereceived ) <= DATE_SUB(CURDATE(),INTERVAL 6 MONTH)
AND NOT (DATE(bt.datesent) <= CURDATE() AND bt.datearrived IS NULL)
AND (SELECT Count(statistics.itemnumber) from statistics
WHERE statistics.itemnumber = i.itemnumber AND statistics.type IN ('issue','renew')) < 2
AND i.itype = <<Item type|itemtypes>> AND i.homebranch = <<Branch|branches>>
AND i.notforloan = 0
AND i.holdingbranch = i.homebranch
AND i.onloan IS NULL
GROUP BY b.title
Items with no Circs in a specific timeframe
- Developer: Nicole C. Baratta and Ian Walls, ByWater Solutions
- Module: Circulation
- Purpose: Titles that haven't checked out in a specific period of time
- Status: Complete
SELECT b.title, b.author, i.itemcallnumber, i.barcode
FROM biblio b
JOIN items i USING (biblionumber)
WHERE i.itype = <<Item type|itemtypes>> and itemnumber NOT IN
(SELECT DISTINCT itemnumber
FROM statistics
WHERE type = 'issue' AND
datetime BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>)
Items Added before a given date with either no circs or last circ before a given date, by collection code
- Developer: Andrew Fuerste-Henry, ByWater Solutions
- Module: Circulation
- Purpose: Titles never checked out or last checked out before a given date
- Status: Complete
SELECT itemnumber, items.barcode, items.itemcallnumber, biblio.title, biblio.author, items.issues, lu.localuse, items.datelastborrowed, items.ccode, items.damaged, items.itemlost, items.withdrawn
FROM items
LEFT JOIN biblio on (items.biblionumber=biblio.biblionumber) left join (SELECT itemnumber, count(datetime) as localuse from statistics where type='localuse' group by itemnumber) lu using (itemnumber)
WHERE items.ccode=<<Collection|CCODE>> AND items.dateaccessioned <= <<Item Acquired Before (yyyy-mm-dd)|date>> AND (items.datelastborrowed <= <<Last CKO before (yyyy-mm-dd)|date>> OR items.issues IS NULL)
ORDER BY items.itemcallnumber ASC
Claims Returned
List of Unresolved Claims
- Developer: Andrew Fuerste-Henry, ByWater Solutions
- Module: Circulation
- Purpose: List all unresolved Claims Returned claims
- Status: Complete
SELECT barcode, biblio.title, author, itemcallnumber, cardnumber, surname, firstname, created_on, return_claims.notes
FROM return_claims left join items using (itemnumber) left join biblio using (biblionumber) left join borrowers using (borrowernumber)
WHERE resolution is null
ORDER BY itemcallnumber
List of Unresolved Claims on Checked-In Items
- Developer: Andrew Fuerste-Henry, ByWater Solutions
- Module: Circulation
- Purpose: List all unresolved Claims Returned claims on items that are currently checked in
- Status: Complete
SELECT barcode, biblio.title, author, itemcallnumber, cardnumber, surname, firstname, created_on, return_claims.notes
FROM return_claims left join items using (itemnumber) left join biblio using (biblionumber) left join borrowers using (borrowernumber)
WHERE resolution is null and onloan is null
ORDER BY itemcallnumber
Others
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) 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