3.2 SQL Reports Library

The following SQL statements have been written by Koha users world-wide. They are duplicates of the reports in the regular SQL reports library, but updated to remove unnecessary left joins, and insert parameter statements where those would be useful. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system. In addition, if you find a mistake, please feel free to fix it!
== SQL Reports ==
=== Tips ===
Use the parameters function when you can, to reduce the number of saved reports (one report instead of, say, one report per library)
==== Links ====
If you want to put links to your report, you can use the SQL's CONCAT keyword in your SELECT clause.
for example, the following SQL Report will list all your biblio with a link to each of them.
SELECT
 biblionumber, 
 CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',title,'</a>') AS Title 
FROM biblio 
ORDER BY biblionumber
==== Query MARC ====
MySQL has some XML handling functions (since MySQL 5.1.5): http://dev.mysql.com/doc/refman/5.4/en/xml-functions.html
For example:
 
SELECT 
 ExtractValue((
 SELECT marcxml
 FROM biblioitems
 WHERE
 biblionumber=14),
 '//datafield[@tag="952"]/subfield[@code>="a"]') AS ITEM;
 
Returns the entire 952 data for all 952 fields for biblionumber 14 (without delimiting):
 
SELECT 
 ExtractValue((
 SELECT marcxml 
 FROM biblioitems 
 WHERE biblionumber=14),
 '//datafield[@tag="260"]/subfield[@code="b"]') AS PUBLISHER; 
Returns the 260$b data for biblionumber 14
=== SQL Report Summary Template ===
==== Catalog without Withdrawn ====
* Developer: Asif Nawab
* Module: Catalog
* Purpose: Complete list of catalog without withdrawn items.
* Status: Completed
SELECT items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate,items.itype,items.ccode,items.price,items.withdrawn
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.withdrawn != 1
ORDER BY items.itype ASC
=== Holds ===
==== List of Patrons with Holds Awaiting Pickup ====
* Developer: Bev Church
* Module: Holds
* Purpose: List of all patrons at branch with holds awaiting pickup. So list can be exported from system and merged with a word processing notification document 
* Status: Complete
SELECT
 borrowers.surname,
 borrowers.firstname, 
 borrowers.address, 
 borrowers.city, 
 borrowers.zipcode, 
 reserves.waitingdate AS 'hold date',
 items.barcode,
 biblio.title
FROM
 reserves,
 borrowers,
 items,
 biblio 
WHERE
 reserves.borrowernumber = borrowers.borrowernumber
AND
 reserves.itemnumber = items.itemnumber
AND
 items.biblionumber = biblio.biblionumber
AND
 priority = 0
AND
 waitingdate IS NOT NULL
AND
 reserves.branchcode = <<Select your library|branches>>
==== Count of hold filled by another branch ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Holds
* Purpose: Holds filled by a branch other than the items homebranch for a year broken down by month 
* Status: Complete
select items.homebranch, 
old_reserves.branchcode, 
monthname(old_reserves.reservedate) as month, 
year(old_reserves.reservedate) as year, 
count(*) 
from items 
join old_reserves 
USING (itemnumber) 
where old_reserves.branchcode != items.homebranch and year(old_reserves.reservedate) = <<Enter Year>>
group by month(old_reserves.reservedate), old_reserves.branchcode
==== List of all Patrons from a Single Branch with open Hold Requests ====
* Developer: Jane Wagner, PTFS 
* Module: Holds
* Purpose: Monthly holds placed by branch (counts holds placed in that month that have not been filled) 
* Status: Complete
select borrowers.surname, borrowers.firstname, borrowers.cardnumber, reserves.reservedate 
as 'date reserved', reserves.priority, biblio.title, 
IF( LOCATE('<datafield tag="020"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="020"', biblioitems.marcxml)), , 
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="020"', biblioitems.marcxml)) + 19, 
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="020"', biblioitems.marcxml)) + 19))) 
AS ISBN from reserves, borrowers, biblio, biblioitems where reserves.borrowernumber = borrowers.borrowernumber 
and reserves.biblionumber = biblio.biblionumber and reserves.biblionumber = biblioitems.biblionumber 
and reserves.branchcode = <<Select your library|branches>> and reserves.priority = 0
==== List of all items currently on loan to another library ====
* Developer: Nora Blake and Bev Church
* Module: Holds
* Purpose: List of all items currently on loan to another library (includes title and call #) 
* Status: Complete
SELECT
 biblio.title,
 items.itemcallnumber, 
 items.holdingbranch,
 items.homebranch, 
 items.barcode, 
 issues.issuedate 
FROM items 
JOIN issues USING(itemnumber) 
JOIN biblio USING(biblionumber)
WHERE
 issues.branchcode !=<<Issuing Library|branches>>
 AND
 items.homebranch = <<Your Library|branches>>
ORDER BY
 items.homebranch, issues.issuedate, biblio.title
==== List of all items currently borrowed from another library ====
* Developer: Nora Blake and Bev Church
* Module: Holds
* Purpose: List of all items currently borrowed from another library (includes title and call #) 
* Status: Complete
SELECT
 biblio.title,
 items.itemcallnumber, 
 items.holdingbranch,
 items.homebranch, 
 items.barcode, 
 issues.issuedate 
FROM items 
JOIN issues USING(itemnumber)
JOIN biblio USING(biblionumber)
WHERE 
 issues.branchcode=<<Issuing Library|branches>>
 AND 
 items.holdingbranch != items.homebranch 
ORDER BY
 items.homebranch, issues.issuedate, biblio.title
==== Monthly holds placed by branch ====
* Developer: Jane Wagner, PTFS
* Module: Holds
* Purpose: Monthly holds placed by branch (counts holds placed in that month that have not been filled) 
* Status: Complete
SELECT count(*),branchcode
FROM reserves
WHERE
 reservedate >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND
 reservedate <= LAST_DAY(now() - interval 1 month)
GROUP BY branchcode
==== Monthly holds placed and filled by branch ====
* Developer: Jane Wagner, PTFS
* Module: Holds
* Purpose: Monthly holds placed and filled by branch (counts holds both placed and filled in that month) 
* Status: Complete
SELECT count(*), branchcode
FROM old_reserves
WHERE
 (timestamp like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%')))
AND
 (reservedate >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND
 reservedate <= LAST_DAY(now() - interval 1 month))
AND Found = 'F'
GROUP BY branchcode 
==== Monthly holds filled by branch ====
* Developer: Jane Wagner, PTFS
* Module: Holds
* Purpose: Monthly holds filled by branch (counts all holds filled in that month regardless of when placed)
* Status: Complete
SELECT count(*),branchcode
FROM old_reserves 
WHERE
 (timestamp like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%')))
AND
 Found = 'F'
GROUP BY branchcode 
==== Overdues With Holds Waiting ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Holds/Circulation
* Purpose: A list of items that are overdue that have holds on them. A report to help you know who to call with overdues to tell them others are waiting for their items
* Status: Complete
SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, 
 borrowers.phone, borrowers.address, borrowers.city, borrowers.zipcode, 
 issues.date_due, 
 (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', 
 items.itype, biblio.title, biblio.author, items.itemcallnumber, 
 items.barcode, COUNT(reserves.biblionumber) AS 'holds' 
FROM borrowers 
JOIN issues USING(borrowernumber)
JOIN items USING(itemnumber) 
JOIN biblio USING(biblionumber)
JOIN reserves USING(biblionumber)
WHERE issues.branchcode = <<Select your library|branches>>
GROUP BY reserves.biblionumber 
HAVING COUNT(reserves.biblionumber) > 0 
ORDER BY borrowers.surname ASC, issues.date_due ASC 
==== Top 10 Titles Placed on Hold in the Last 6 Months ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Holds/Circulation
* Purpose: Top 10 titles placed on hold in the last 6 months showing titles, authors and ccode.
* Status: Complete
SELECT count(*) as holds, title, author, ccode 
FROM (
SELECT biblio.title, biblio.author, items.ccode, biblio.biblionumber
FROM biblio 
JOIN reserves USING(biblionumber) 
JOIN items USING(biblionumber)
WHERE DATE(reserves.timestamp) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH) 
 AND DATE(reserves.timestamp) <=CURRENT_DATE()
UNION ALL
SELECT biblio.title, biblio.author, items.ccode, biblio.biblionumber
FROM biblio
JOIN old_reserves USING(biblionumber)
JOIN items USING(biblionumber)
WHERE DATE(old_reserves.timestamp) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH) 
 AND DATE(old_reserves.timestamp) <=CURRENT_DATE()
 ) AS myholds 
GROUP BY biblionumber 
ORDER BY holds DESC 
LIMIT 10
=== Patron Reports ===
==== New Patron List (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: 
* Status: Complete 
SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.dateenrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
ORDER BY borrowers.surname asc
==== Superlibrarians ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Patrons
* Purpose: List the superlibrarians
* Status: Complete
select firstname, surname, borrowernumber 
from borrowers 
where flags='1'
==== Patrons w/ Fines ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Patrons
* Purpose: List patrons with their fine amounts
* Status: Complete
SELECT
 borrowers.cardnumber,
 borrowers.surname, 
 borrowers.firstname, 
 FORMAT(SUM(accountlines.amountoutstanding),2) AS due 
FROM
 borrowers
JOIN
 accountlines
USING(borrowernumber)
WHERE
 accountlines.amountoutstanding > 0 
GROUP BY borrowers.cardnumber 
ORDER BY borrowers.surname ASC
==== Patrons w/ credits ====
* Developer: Jane Wagner, PTFS 
* Module: circ/accountlines 
* Purpose: 
* Status: Complete 
SELECT 
 borrowers.surname, 
 borrowers.firstname, 
 borrowers.cardnumber, 
 borrowers.address, 
 borrowers.city, 
 borrowers.zipcode, 
 round(Sum(accountlines.amountoutstanding),2) AS 'total owed' 
FROM borrowers 
JOIN accountlines USING(borrowernumber)
WHERE amountoutstanding != 0 
GROUP BY accountlines.borrowernumber having sum(accountlines.amountoutstanding) < 0 
ORDER BY borrowers.surname, borrowers.firstname
==== New Patrons ====
* Developer: Sharon Moreland 
* Module: Circulation 
* Purpose: New patrons added 
* Status: Complete 
 SELECT branchcode,categorycode,COUNT(*)
 FROM borrowers 
WHERE MONTH(dateenrolled) = <<Numerical Month (i.e. April = 04)>> 
AND YEAR(dateenrolled)= <<Enter Year>>
 GROUP BY branchcode,categorycode 
 ORDER BY branchcode
==== Expired Patrons w/out Checkouts ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Patrons 
* Purpose: List patrons expired in a specific year who do not currently have any checkouts
* Status: Complete 
 SELECT borrowers.surname, borrowers.firstname, borrowers.borrowernumber 
 FROM borrowers 
 WHERE borrowernumber 
 NOT IN (SELECT borrowernumber FROM issues) 
 AND YEAR(borrowers.dateexpiry) = <<Enter Year>>
==== Missing Emails ====
* Developer: Sharon Moreland 
* Module: Patrons 
* Purpose: Missing e-mails 
* Status: Complete 
SELECT 
 cardnumber, 
 surname,
 firstname,
 branchcode,
 debarred,
 dateexpiry 
 FROM borrowers 
 WHERE ' ' IN (email)
==== Patrons w/ Checked Out Items ====
* Developer: Nora Blake 
* Module: Circulation 
* Purpose: List of items checked out to patrons according to data contained in Sort field 
* Status: Complete 
SELECT issues, biblio.title, biblio.author, borrowers.surname, borrowers.firstname, borrowers.sort1, 
 items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate 
FROM borrowers 
JOIN issues USING(borrowernumber)
JOIN items USING(itemnumber)
JOIN biblio USING(biblionumber)
 WHERE issues.branchcode=<<Choose your branch|branches>> AND sort1=<<Enter sort value to match on>>
 ORDER BY issues.branchcode, borrowers.sort1, borrowers.surname, issues.issuedate, biblio.title
==== New Patron Count (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: Patron 
* Purpose: 
* Status: Complete 
SELECT COUNT(*) as 'New Patrons Last Month' 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month)
==== New Patron Count (by Branch/Category) (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: Patron 
* Purpose: Count of new patrons enrolled in the previous month, by branch and category code 
* Status: Complete 
SELECT branchcode, categorycode, COUNT(branchcode) as NumberEnrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
GROUP BY branchcode, categorycode
==== New Patrons by Branch (year to date) ====
* Developer: Jane Wagner, PTFS 
* Module: Patron 
* Purpose: 
* Status: Complete 
SELECT branchcode, categorycode, COUNT(branchcode) as NumberEnrolled 
FROM borrowers 
WHERE YEAR(borrowers.dateenrolled) = YEAR(NOW()) 
GROUP BY branchcode, categorycode
==== Count of Expired Patrons ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Patrons
* Purpose: Count of patrons who's cards have expired before today
* Status: Complete
SELECT COUNT(borrowers.cardnumber) as count
FROM borrowers 
WHERE borrowers.dateexpiry > 2010-08-01
==== Patrons with All Attribute Values====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Patrons
* Purpose: Patron list with the value of all of their custom patron attributes
* Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrower_attributes.code, borrower_attributes.attribute 
FROM borrowers 
JOIN borrower_attributes USING(borrowernumber) 
JOIN borrower_attribute_types USING(code)
group by borrower_attributes.attribute 
order by borrowers.surname, borrowers.firstname asc
==== Patrons with a Specific Attribute Value ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Patrons
* Purpose: Patron list with the value of one of their custom patron attributes (student id)
* Status: Complete
SELECT borrowers.surname, borrowers.firstname, 
 borrowers.cardnumber, borrower_attributes.attribute as 'Student ID' 
FROM borrowers 
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) 
LEFT JOIN borrower_attribute_types ON (borrower_attribute_types.code=borrower_attributes.code) 
WHERE borrower_attributes.code = 'STUID'
==== Duplicate Patrons ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Patrons
* Purpose: List of patrons who are potentially duplicates
* Status: Complete
select surname, firstname, GROUP_CONCAT(cardnumber SEPARATOR ', ') as barcodes, 
 GROUP_CONCAT(borrowernumber SEPARATOR ', ') as borrowers 
from borrowers 
group by CONCAT(surname,"/",firstname,"/") 
HAVING COUNT(CONCAT(surname,"/",firstname,"/"))>1
=== Circulation Reports ===
==== Circulation of Juvenile Materials ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Circulation
* Purpose: Circulation for juvenile materials (using call number) for a specific time frame
* Status: Complete
SELECT statistics.branch, month(statistics.datetime) AS month, 
 year(statistics.datetime) AS year, count(statistics.datetime) AS count 
FROM statistics 
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) 
WHERE statistics.type LIKE 'issue' 
 AND (items.itemcallnumber LIKE 'YA%' OR items.itemcallnumber LIKE 'J%') 
 AND date(statistics.datetime) BETWEEN '2009-12-01' AND '2010-06-30' 
GROUP BY statistics.branch, year, month 
ORDER BY year, month DESC, statistics.branch ASC
==== Checkouts by Call Number (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: 
* Status: Complete 
SELECT count(statistics.type) AS 'Checkouts',items.itemcallnumber 
FROM borrowers 
 LEFT JOIN statistics on (statistics.borrowernumber=borrowers.borrowernumber) 
 LEFT JOIN items on (items.itemnumber = statistics.itemnumber) 
 LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber) 
WHERE statistics.type = 'issue' 
 AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') 
 AND statistics.datetime <= LAST_DAY(now() - interval 1 month) 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber asc
==== Renewals by Call Number (previous month) ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: 
* Status: Complete 
SELECT count(statistics.type) AS 'Renewals',items.itemcallnumber 
FROM borrowers 
 LEFT JOIN statistics on (statistics.borrowernumber=borrowers.borrowernumber) 
 LEFT JOIN items on (items.itemnumber = statistics.itemnumber) 
 LEFT JOIN biblioitems on (biblioitems.biblioitemnumber = items.biblioitemnumber) 
WHERE statistics.type = 'renew'
 AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
 AND statistics.datetime <= LAST_DAY(now() - interval 1 month) 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber asc
==== Checkouts by Item Type (previous month) ====
* Developer: Galen Charlton, Equinox
* Module: Circ 
* Purpose: 
* Status: Complete 
* Note: This can take a while to run because of the union of items and deleteditems, but has the advantage that items that get circulated, then deleted, during the previous month will get reported using their correct item type.
SELECT all_items.itype AS "Item Type" ,count(*) AS 'Checkouts' 
FROM statistics 
JOIN (
 SELECT itemnumber, itype FROM deleteditems
 UNION
 SELECT itemnumber, itype FROM items 
) AS all_items USING (itemnumber)
WHERE statistics.type = 'issue' 
AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') 
AND statistics.datetime <= LAST_DAY(now() - interval 1 month)
GROUP BY all_items.itype 
ORDER BY all_items.itype asc;
==== Previous Day's Circ Stats ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: 
* Status: Complete 
SELECT count(statistics.type) AS 'Total', statistics.type 
FROM statistics WHERE statistics.datetime like concat(date_format(LAST_DAY(now() - interval 1 day),'%Y-%m-%')) 
GROUP BY statistics.type 
ORDER BY statistics.type asc
==== Previous Month's Circ Stats ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: 
* Status: Complete 
SELECT count(statistics.type) AS 'Total', statistics.type 
FROM statistics 
WHERE statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND statistics.datetime <= LAST_DAY(now() - interval 1 month) 
GROUP BY statistics.type 
ORDER BY statistics.type asc
==== Previous Month's Checkouts/Renewals by Collection Code ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: 
* Status: Complete 
SELECT items.ccode AS Collection, COUNT( statistics.itemnumber ) AS Count 
FROM items 
JOIN statistics USING(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 borrowers 
 JOIN statistics USING(borrowernumber)
WHERE (statistics.datetime like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))) 
 AND statistics.type IN ('issue','renew') 
GROUP BY borrowers.categorycode
==== All Circ Actions on Date ====
* Developer: Nicole C. Engard, ByWater Solutions 
* Module: Circ 
* Purpose: Give you stats for all circ actions on a specific date,
* Status: Complete 
SELECT statistics.type as action, COUNT(statistics.datetime) as count 
FROM statistics 
WHERE DATE(statistics.datetime)='2010-07-06' 
GROUP BY statistics.type;
==== Yesterday's Forgiven Charges (entire system) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: forgiven charges yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
==== Yesterday's Sundry Fees by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: sundry fees yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'M') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
==== Yesterday's Sundry Fees (entire system) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: sundry fees charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines WHERE (accounttype = 'M') and date = (now() - interval 1 day) 
==== Yesterday's Credits by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: credits yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM borrowers 
LEFT JOIN accountlines USING (borrowernumber)
WHERE (accounttype = 'C') and date = (now() - interval 1 day) and borrowers.branchcode = <<Select your Library|branches>>
==== Yesterday's Credits (entire system) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: credits yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines WHERE (accounttype = 'C') and date = (now() - interval 1 day)
==== Yesterday's New Card Fees by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: new card fees yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM borrowers 
JOIN accountlines USING(borrowernumber)
WHERE (accounttype = 'N') and date = (now() - interval 1 day) and borrowers.branchcode = <<Select your Library|branches>>
==== Yesterday's New Card Fees (entire system) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: new card fees yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines WHERE (accounttype = 'N') and date = (now() - interval 1 day)
==== Yesterday's Payments by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: payments yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM borrowers 
JOIN accountlines USING(borrowernumber)
WHERE (accounttype = 'PAY') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
==== Yesterday's Payments (entire system) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: payments yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines WHERE (accounttype = 'PAY') and date = (now() - interval 1 day)
==== Year to Date Fines by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: year to date fines charged for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' 
FROM borrowers 
JOIN accountlines USING(borrowernumber)
WHERE (accounttype = 'F' or accounttype = 'FU' ) and YEAR(date) = YEAR(NOW()) and borrowers.branchcode = <<Select your Library|branches>>
==== Year to Date Fines (entire system) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: year to date fines charged (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' 
FROM accountlines 
WHERE (accounttype = 'F' or accounttype = 'FU' ) and YEAR(date) = YEAR(NOW())
==== Total Fines Owed ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: total amount of fines owed (entire system) 
* Status: Complete 
SELECT FORMAT(Sum(accountlines.amountoutstanding),2) FROM accountlines
==== Yesterday's Amount Collected (entire system) ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: amount actually collected yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Paid Yesterday' 
FROM accountlines
WHERE (accounttype = 'PAY' ) and date = (now() - interval 1 day)
==== 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 
JOIN issues USING(borrowernumber)
JOIN items USING(itemnumber)
JOIN biblio USING(biblionumber)
WHERE (issues.issuedate between DATE_SUB(CURDATE(), INTERVAL 7 DAY) and CURDATE() 
 AND issues.branchcode = <<Select Issuing Library|branches>>)
ORDER BY borrowers.surname ASC, issues.date_due ASC
==== Number of Checkouts by Branch ====
* Developer: Nicole C. Engard, ByWater Solutions 
* Module: Circulation 
* Purpose: Statistical Count by month of number of checkouts made by each branch all in one report 
* Status: Complete 
 SELECT branch, month(datetime) as month, year(datetime) as year, count(datetime) as count 
 FROM statistics 
 WHERE type LIKE 'issue' 
 GROUP BY branch, year, month 
 ORDER BY year, month desc, branch ASC
==== Not Circulating Items ====
* Developer: Bev Church, Joe Tholen 
* Module: Circulation 
* Purpose: List items not circulated since may2008, 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 = <<Select your Library|branches>> AND location = <<Location Code|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 = <<Select your Library|branches>> AND location =<<Location Code|LOC>> AND itemnumber NOT IN 
 (SELECT itemnumber FROM old_issues WHERE date(issuedate) BETWEEN 2008-05-01 AND curdate()) ) 
 ORDER BY itemcallnumber, barcode
==== Patrons w/ Books Due Tomorrow ====
* Developer: Nicole C. Engard, ByWater Solutions, Koha List 
* Module: Circulation 
* Purpose: List patrons with books due tommorrow 
* Status: Complete 
 SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, issues.date_due, items.barcode, biblio.title, biblio.author
 FROM borrowers 
 JOIN issues USING(borrowernumber)
 JOIN items USING(itemnumber)
 JOIN biblio USING(biblionumber)
 WHERE issues.date_due = DATE_ADD(curdate(), INTERVAL 1 DAY) 
 ORDER BY borrowers.surname asc
==== Transfers by Other Branches ====
* Developer: Joe Tholen 
* Module: Circulation 
* Purpose: List total transfers from other branches, by branches, by month 
* Status: Be warned this is done over the previous YEAR. Not for the current one. To combine with ILL stats 
 SELECT frombranch, monthname(datesent) month,COUNT(*) 
 FROM branchtransfers where tobranch="Me" and YEAR(datesent)=YEAR(NOW())-1 
 GROUP BY month 
==== 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 items 
 JOIN branchtransfers USING(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 items 
 JOIN branchtransfers USING(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: Change "MMM" to whatever you need 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="MMM" and YEAR(datesent)=YEAR(NOW())-1 
 GROUP BY month ORDER by month(datesent)
==== Total Forgiven Fines Today ====
* Developer: Nicole C. Engard, ByWater Solutions 
* Module: Circulation 
* Purpose: Total amount forgiven in fines today 
* Status: Complete 
SELECT SUM(amount) 
 FROM accountlines 
 WHERE DATE(timestamp)=CURDATE() AND (accounttype='FOR' OR accounttype='W')
==== Total Fines Paid Today ====
* Developer: Nicole C. Engard, ByWater Solutions 
* Module: Circulation 
* Purpose: Total amount paid in fines today 
* Status: Complete 
SELECT SUM(amount) 
 FROM accountlines 
 WHERE DATE(timestamp)=CURDATE() AND (accounttype='PAY' OR accounttype='C')
==== Yesterday's Fines by branch ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: Fines charged yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' or accounttype = 'FU' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
==== Yesterday's Fines ====
* Developer: Jane Wagner, PTFS 
* Module: Circ 
* Purpose: Fines charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines WHERE (accounttype = 'F' or accounttype = 'FU' ) and date = (now() - interval 1 day)
==== Yesterday's Lost Item Charges by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: lost items charged yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
==== Yesterday's Lost Item Charges ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: lost items charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
==== Yesterday's Account Management Fees by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: acct mgt charged yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
==== Yesterday's Account Management Fees ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: acct mgt fees charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
==== Yesterday's Forgiven Charges by branch ====
* Developer: Jane Wagner, PTFS 
* Module: circ 
* Purpose: forgiven charges yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
==== 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) = XXXX and MONTH(datetime) = X 
 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) = XXXX and MONTH(datetime) = X and DAY(datetime) = X 
 GROUP BY DATE(datetime), substring(itemcallnumber,1,1)
==== Overdue materials ====
* Developer: Sharon Moreland 
* Module: Circulation 
* Purpose: Overdue materials 
* Status: Complete 
SELECT borrowers.surname, borrowers.firstname, borrowers.phone, borrowers.cardnumber, 
 borrowers.address, borrowers.city, borrowers.zipcode, issues.date_due, 
 (TO_DAYS(curdate())-TO_DAYS( date_due)) as 'days overdue', items.itype, 
 items.itemcallnumber, items.barcode, items.homebranch, biblio.title, biblio.author 
FROM borrowers 
LEFT JOIN issues on (borrowers.borrowernumber=issues.borrowernumber) 
LEFT JOIN items on (issues.itemnumber=items.itemnumber) 
LEFT JOIN biblio on (items.biblionumber=biblio.biblionumber) 
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > '30' AND issues.branchcode = 'LIBRARY' 
ORDER BY borrowers.surname asc, issues.date_due asc
==== Long Overdues ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Circulation 
* Purpose: Items that are long overdue (in this case between 42 and 50 days overdue) 
* Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.phone, borrowers.cardnumber, 
 issues.date_due, biblio.title, biblio.author, items.itemcallnumber, items.barcode, 
 items.homebranch, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue' 
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)) > '42' 
AND (TO_DAYS(curdate())-TO_DAYS(date_due)) < '50' 
ORDER BY borrowers.surname ASC, issues.date_due ASC
==== Count of Circ by Alpha Call Number Prefix ====
* Developer: Jared Camins-Esakov and Nicole C. Engard, ByWater Solutions
* Module: Circulation
* Purpose: A statistical report showing how many items with a specific alphabetical prefix have circulated in a month
* Status: Complete
SELECT SUBSTRING_INDEX(itemcallnumber, ' ', 1) AS 'Call# range', count(*) AS count 
FROM statistics 
LEFT JOIN items USING (itemnumber) 
WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = 2010 and 
 MONTH(datetime) = 11 AND SUBSTRING_INDEX(itemcallnumber, ' ', 1) rlike '[a-z]' 
GROUP BY SUBSTRING_INDEX(itemcallnumber, ' ', 1) 
==== Count of Circ by Call Number Prefix ====
* Developer: Jared Camins-Esakov
* Module: Circulation
* Purpose: A statistical report showing how many items with any prefix (meaning the letters/numbers before the first space) 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) = 2010 and 
 MONTH(datetime) = 11 
GROUP BY SUBSTRING_INDEX(itemcallnumber, ' ', 1) 
==== Top 10 Circulating Books ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Circulation
* Purpose: Top 10 circulating books for the last 6 months
* Status: Complete
select count(statistics.datetime) as circs, biblio.title, biblio.author,
 items.ccode 
from statistics 
join items on (items.itemnumber=statistics.itemnumber) 
left join biblio on (biblio.biblionumber=items.biblionumber) 
where DATE(statistics.datetime) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH) 
 AND DATE(statistics.datetime)<=CURRENT_DATE() and 
 statistics.itemnumber is not NULL 
group by biblio.biblionumber 
order by circs DESC 
Limit 10
=== Catalog/Bibliographic Reports ===
==== Accession Register Sorted by Barcode Number Report ====
* Developer: Ata ur Rehman (ata.rehman@gmail.com)
* Module: Catalog
* Purpose: To create an Accession Register Sorted by Barcode Number Report
* Status: Complete
 SELECT items.barcode, biblio.author, biblio.title, items.itemcallnumber, 
 items.holdingbranch, biblioitems.isbn, biblioitems.pages, biblioitems.size, 
 biblioitems.place, biblio.copyrightdate 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 ORDER BY LPAD(items.barcode,30,' ') ASC
==== Total collection size ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: Total collection size
* Status: Complete
 SELECT count(i.biblionumber) AS added, i.itype, i.homebranch, i.location 
 FROM items i 
 WHERE i.dateaccessioned < '2009-01-01' 
 GROUP BY i.homebranch,i.itype,i.location 
 ORDER BY i.homebranch,i.itype,i.location ASC
==== URLs in Catalog ====
* Developer: Lenora Oftedahl
* Module: Catalog
* Purpose: URLs in Catalog
* Status: Needs work as I only want the URLs, not all barcodes
 SELECT items.barcode,biblioitems.url 
 FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.homebranch='MAIN'
==== Null Item Type ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: Null Item Type
* Status: Complete
 SELECT items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.itype IS NULL AND items.homebranch='LIBRARY'
==== Null Barcodes ====
* Developer: Rachel Hollis
* Module: Catalog
* Purpose: Null Barcodes
* Status: Complete
 SELECT items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate 
 FROM items
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
 WHERE Barcode IS NULL
==== Items with "X" CCode ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: Items with "X" CCode
* Status: Complete
 SELECT items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.homebranch='LIBRARY' AND items.ccode='COLLCODENAME' 
 ORDER BY items.dateaccessioned DESC
==== Items with "X" & "Y" ITypes ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: Items with "X" & "Y" ITypes
* Status: Complete
 SELECT items.dateaccessioned,items.itype,items.itemcallnumber,items.barcode,biblio.author,biblio.title, biblio.copyrightdate 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.homebranch='LIBRARY' AND items.itype='ITEMTYPEX' OR items.homebranch='LIBRARY' AND items.itype='ITEMTYPEY' 
 ORDER BY items.dateaccessioned DESC
==== Call Numbers ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: Call Numbers
* Status: Complete
 SELECT items.itype,items.itemcallnumber,items.barcode,biblio.title,biblio.copyrightdate 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.homebranch='LIBRARY' AND items.itemcallnumber LIKE 'FI%' 
 ORDER BY items.itemcallnumber ASC
==== Complete Shelf list ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: Complete Shelf list
* Status: Complete
 SELECT items.price,items.replacementprice,biblio.title,biblio.author,items.itemcallnumber 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.homebranch='LIBRARY' 
 ORDER BY items.itemcallnumber ASC
==== All Barcodes ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: All Barcodes
* Status: Complete
 SELECT items.barcode,items.location,biblio.title,items.itemcallnumber 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.homebranch='LIBRARY'
==== New Bib Records ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: List new bibs in the last two months
* Status: Complete
select monthname(datecreated) as month, year(datecreated) as year, count(biblionumber) as count 
from biblio 
where year(datecreated) = '2010' and (month(datecreated)='09' or month(datecreated)='10') 
group by year(datecreated), month(datecreated)
==== List new items ====
* Developer: Sharon Moreland
* Module: Catalog
* Purpose: List new items
* Status: Complete
 SELECT items.dateaccessioned,biblio.title,items.itemcallnumber 
 FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE DATE (items.dateaccessioned) BETWEEN '2009-03-01' AND'2009-04-27' AND items.homebranch='LIBRARY' 
 ORDER BY items.itemcallnumber ASC
==== Another new items report ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: List new items
* Status: Complete
select monthname(timestamp) as month, year(timestamp) as year, count(itemnumber) as count 
from items 
where year(timestamp) = '2010' and (month(timestamp)='09' or month(timestamp)='10') 
group by year(timestamp), month(timestamp)
==== List of Items added to catalog in last 30 days ====
* Developer: Nora Blake
* Module: Catalog
* Purpose: List of Items added to catalog in last 30 days (includes bibliographic info)
* Status: Complete
 SELECT items.dateaccessioned,items.itemcallnumber,biblio.title,biblio.author 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
 LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.homebranch='LIBRARY' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= items.dateaccessioned 
 ORDER BY biblio.title asc
==== Count of all items ====
* Developer: Michael Hafen
* Module: Catalog
* Purpose: Count of all items
* Status: Complete
 SELECT COUNT(barcode) AS Count FROM items WHERE barcode <> AND barcode IS NOT NULL
==== Count of all items by Item Type ====
* Developer: Michael Hafen
* Module: Catalog
* Purpose: Count of all items by Item Type
* Status: Complete
 SELECT itype AS 'Item Type',COUNT(barcode) AS Count FROM items WHERE barcode <> ''
 AND barcode IS NOT NULL GROUP BY itype
==== Count of all items and broken down by branch ====
* Developer: Zachary Spalding, SENYLRC
* Module: Catalog
* Purpose: Count of all items by Item and broken down by branch
* Status: Complete
SELECT items.homebranch,branches.branchname, count(items.itemnumber) AS items FROM items,branches where items.homebranch=branches.branchcode GROUP BY homebranch ORDER BY homebranch ASC 
==== Count of all titles ====
* Developer: Michael Hafen
* Module: Catalog
* Purpose: Count of all titles
* Status: Complete
 SELECT COUNT(biblionumber) AS Count FROM biblio
==== Statistical Count of total number of items held by each branch ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: Statistical Count of total number of items held by each branch all in one report
* Status: Complete
 SELECT homebranch,count(itemnumber) as items 
 FROM items 
 GROUP BY homebranch 
 ORDER BY homebranch asc 
==== All bibs without items ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: All bibs without items
* Status: Complete
 SELECT biblio.biblionumber, biblio.title 
 FROM biblio 
 LEFT JOIN items on biblio.biblionumber = items.biblionumber 
 WHERE items.itemnumber is NULL 
==== Weeding tool ====
* Developer: Kathy Rippel
* Module: Catalog
* Purpose: Weeding tool, we call this the SuperWeeder because it includes all sorts of data to help in decision making
* Status: Complete
 SELECT CONCAT( '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',biblio.biblionumber, 
 '\">',items.barcode,'</a>' ) as 'Barcode',items.itemcallnumber,biblio.title,biblio.copyrightdate as
 'Copyright',items.dateaccessioned as
 'Accessioned',items.itype,items.issues,items.renewals,(IFNULL(items.issues,0)+IFNULL(items.renewals,0)) as
 Total_Circ,items.datelastborrowed,items.itemlost,items.onloan,items.damaged,items.itemnotes 
 FROM items 
 LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
 (biblioitems.biblionumber=biblio.biblionumber) 
 WHERE items.itype='ITYPE' AND items.holdingbranch='BRANCHCODE' AND (items.itemcallnumber LIKE '37%' OR
 items.itemcallnumber LIKE '38%' OR items.itemcallnumber LIKE '39%') 
 ORDER BY items.itemcallnumber
==== Inventory Report ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: Find all items that haven't been seen since a specific date
* Status: Complete
 SELECT biblio.title, items.barcode, items.itemcallnumber 
 FROM biblio
 LEFT JOIN items ON biblio.biblionumber = items.biblionumber 
 WHERE datelastseen < 'DATE' 
 ORDER BY items.itemcallnumber ASC;
==== Damaged Items with Title ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Damaged Items with Title
* Status: Complete
SELECT items.damaged, items.itemcallnumber, items.barcode, biblio.title, biblio.author 
FROM items 
INNER JOIN biblio ON items.biblionumber = biblio.biblionumber 
WHERE items.damaged = True ORDER BY biblio.title asc
==== Count by Call Number ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Count by Call Number
* Status: Complete
SELECT count(items.itemcallnumber) as 'Number of Items', items.itemcallnumber 
FROM items 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber asc
==== Count by Call Number for items added last month ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Count by Call Number for items added last month 
* Status: Complete
SELECT count(items.itemcallnumber), items.itemcallnumber 
FROM items 
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) 
GROUP BY items.itemcallnumber 
ORDER BY items.itemcallnumber asc 
==== Previous Month Items Created ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Previous Month Items Created 
* Status: Complete
SELECT count(items.itemnumber) as ItemsCreated 
FROM items 
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) 
==== Previous Month Items Deleted ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Previous Month Items Deleted 
* Status: Complete
SELECT count(deleteditems.itemnumber) as ItemsDeleted 
FROM deleteditems 
WHERE deleteditems.timestamp like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%')) 
==== Previous Month Items Created--by item type ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Previous Month Items Created--by item type (The total number of rows shown is misleading -- It matches the first item type total. An empty item type column means unknown item type. Add all the entries for the complete total.) 
* Status: Complete
SELECT items.itype as ItemType, count(items.itemnumber) as ItemsCreated 
FROM items 
WHERE (items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month)) 
GROUP BY items.itype 
==== Previous Month Items Deleted--by item type ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Previous Month Items Deleted--by item type (The total number of rows shown is misleading -- It matches the first item type total. An empty item type column means unknown item type. Add all the entries for the complete total.) 
* Status: Complete
SELECT deleteditems.itype as ItemType, count(deleteditems.itemnumber) as ItemsDeleted 
FROM deleteditems 
WHERE (deleteditems.timestamp like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))) 
GROUP BY deleteditems.itype 
==== Withdrawn Items ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: Withdrawn Items 
* Status: Complete
SELECT biblio.title,biblio.author,items.itemcallnumber,items.barcode,items.datelastborrowed, items.wthdrawn 
FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.wthdrawn != 0 
ORDER BY biblio.title asc 
==== List of URL's from 856 ====
* Developer: LibLime provided to David Schuster
* Module: Catalog
* Purpose: List of URL's from 856 
* Status: Complete
SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml, LOCATE('<subfield code="u">', biblioitems.marcxml, LOCATE('<datafield tag="856"', biblioitems.marcxml)+19), LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="u">', biblioitems.marcxml, LOCATE('<datafield tag="856"', biblioitems.marcxml)+19)) - LOCATE('<subfield code="u">', biblioitems.marcxml, LOCATE('<datafield tag="856"', biblioitems.marcxml)+19)) AS url FROM biblioitems, biblio where biblioitems.biblionumber = biblio.biblionumber and url is not null
==== Count of URL's from 856 ====
* Developer: From listserv provided to David Schuster
* Module: Catalog
* Purpose: count of URL's from 856
* Status: Complete
SELECT count(*) FROM biblioitems where biblioitems url != 'null';
==== Records without items ====
* Developer: Magnus Enger
* Module: Catalog
* Purpose: Records without items, with links to OPAC and Intranet
* Status: Complete
* Note: Revised by Jared Camins-Esakov to provide correct link to OPAC based on OPACBaseURL
SELECT b.title AS Title, CONCAT('<a href=\"', IF(CHAR_LENGTH(systempreferences.value), CONCAT('http://', systempreferences.value), ), '/cgi-bin/koha/opac-detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS OPAC,
 CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS Edit 
FROM systempreferences, biblio AS b 
 LEFT JOIN items AS i ON b.biblionumber = i.biblionumber 
WHERE i.itemnumber IS NULL AND systempreferences.variable='OPACBaseURL'
==== Call Number Shelflist ====
* Developer: Jane Wagner, PTFS
* Module: Catalog
* Purpose: list in call number order
* Status: Completed
SELECT items.itemcallnumber,items.datelastborrowed,biblio.title,biblioitems.publicationyear 
FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
ORDER BY items.cn_sort asc
==== Duplicate titles ====
* Developer: D. Ruth Bavousett, PTFS
* Module: Catalog
* Purpose: Checks for exact duplicates on author/title combo; download for full list (doesn't paginate)
* Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, title, author 
FROM biblio 
GROUP BY CONCAT(title,"/",author) HAVING COUNT(CONCAT(title,"/",author))>1
==== Duplicate titles (with same date) ====
* Developer: Jared Camins-Esakov
* Module: Catalog
* Purpose: Based on druthb's report for duplicate titles, but considers date as well; download for full list (doesn't paginate)
* Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, title, author,copyrightdate 
FROM biblio 
GROUP BY CONCAT(title,"/",author,"/",copyrightdate) HAVING COUNT(CONCAT(title,"/",author,"/",copyrightdate))>1
==== Duplicate ISBNs ====
* Developer: Jared Camins-Esakov
* Module: Catalog
* Purpose: Show records with duplicate ISBNs; download for full list (doesn't paginate)
* Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, isbn FROM biblioitems GROUP BY isbn HAVING COUNT(isbn)>1
==== Duplicate ISBNs with Links to Bib Records ====
* Developer: Zachary Spalding, SENYLRC
* Module: Catalog
* Purpose: Show records with duplicate ISBNs; download for full list (doesn't paginate) and has links to bib records. Based on ISBN report written by Jared Camins-Esakov
* Status: Completed
SELECT GROUP_CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>') AS biblionumbers, isbn FROM biblioitems group by isbn, itemtype HAVING COUNT(isbn)>1
==== Bibs with specific keyword in subjects ====
* Developer: Chris Cormack & Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: This report shows all bib records with a subject that contains a specific keyword in the 650a
* Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber, lcsh 
FROM 
(SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')
AS lcsh FROM biblioitems) 
AS subjects 
WHERE lcsh 
LIKE "%KEYWORD%"
==== Bibs without subjects ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: Shows all bibs without subject headings
* Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber 
FROM 
(SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]') AS sub1, 
ExtractValue(marcxml,'//datafield[@tag="651"]/subfield[@code>="a"]') AS sub2, 
ExtractValue(marcxml,'//datafield[@tag="600"]/subfield[@code>="a"]') AS sub3, 
ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]') AS sub4, 
ExtractValue(marcxml,'//datafield[@tag="611"]/subfield[@code>="a"]') AS sub5, 
ExtractValue(marcxml,'//datafield[@tag="630"]/subfield[@code>="a"]') AS sub6, 
ExtractValue(marcxml,'//datafield[@tag="648"]/subfield[@code>="a"]') AS sub7, 
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code>="a"]') AS sub8, 
ExtractValue(marcxml,'//datafield[@tag="654"]/subfield[@code>="a"]') AS sub9, 
ExtractValue(marcxml,'//datafield[@tag="655"]/subfield[@code>="a"]') AS sub10, 
ExtractValue(marcxml,'//datafield[@tag="656"]/subfield[@code>="a"]') AS sub11, 
ExtractValue(marcxml,'//datafield[@tag="657"]/subfield[@code>="a"]') AS sub12, 
ExtractValue(marcxml,'//datafield[@tag="658"]/subfield[@code>="a"]') AS sub13, 
ExtractValue(marcxml,'//datafield[@tag="662"]/subfield[@code>="a"]') AS sub14 
FROM biblioitems) AS subjects 
WHERE sub1 = "" 
AND sub2 = "" 
AND sub3 = "" 
AND sub4 = "" 
AND sub5 = "" 
AND sub6 = "" 
AND sub7 = "" 
AND sub8 = "" 
AND sub9 ="" 
AND sub10 = "" 
AND sub11 = "" 
AND sub12 = "" 
AND sub13 = "" 
AND sub14 =""
==== Bibs without items ====
* Developer: Frédéric Demians
* Module: Catalog
* Purpose: Get biblionumber of biblio records without items and which itemtype doesn't belongs to a list
* Status: Complete
SELECT
 biblio.biblionumber
FROM
 biblio
RIGHT JOIN
 biblioitems
ON
 biblio.biblionumber = biblioitems.biblionumber
LEFT JOIN
 items
ON
 biblio.biblionumber = items.biblionumber
WHERE
 items.biblionumber IS NULL
 AND
 itype NOT IN ('AGH', 'PER');
==== Bibs Suppressed in OPAC ====
* Developer: Chris Hobbs, New Haven Unified School District
* Module: Catalog
* Purpose: Finds all bibs that have been flagged as Suppressed in 942$n
* Status: Completed
 SELECT concat( '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '">', biblio.title, '</a>' ) AS title, biblio.author
 FROM biblioitems
 JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
 WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="n"]' )
 IN ('Y', '1')
==== List of Items Marked Lost/Missing ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Catalog
* Purpose: Finds all items that are marked as lost in some way.
* Status: Completed
select items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, 
 items.barcode, authorised_values.lib 
from items 
left join biblio on (items.biblionumber=biblio.biblionumber) 
left join authorised_values on (items.itemlost=authorised_values.authorised_value) 
where items.itemlost != 0 and authorised_values.category='LOST'
==== Validate Codabar barcodes used by North American libraries ====
* Developer: Jared Camins-Esakov
* Module: Catalog
* Purpose: Identifies barcodes that are invalid based on the rules at http://www.mecsw.com/specs/codabar.html
* Status: Completed
* Note: Change '8060' to the 4-digit code used by your library
SELECT biblionumber, barcode, CONCAT_WS('; ', lengthproblem, typeproblem, libraryproblem, checksumproblem) 
FROM (
 SELECT
 items.biblionumber AS biblionumber, items.barcode AS barcode, 
 IF(CHAR_LENGTH(TRIM(items.barcode)) <> 14, 'Barcode wrong length', NULL) AS lengthproblem,
 IF(SUBSTR(TRIM(items.barcode), 1, 1) <> '3', 'Not an item barcode', NULL) AS typeproblem,
 IF(SUBSTR(TRIM(items.barcode), 2, 4) <> '8060', 'Wrong library code', NULL) AS libraryproblem,
 IF(MOD(10 - MOD((IF(SUBSTR(TRIM(items.barcode), 1, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 1, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 1, 1) * 2)) + 
 (SUBSTR(TRIM(items.barcode), 2, 1)) + 
 (IF(SUBSTR(TRIM(items.barcode), 3, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 3, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 3, 1) * 2)) + 
 (SUBSTR(TRIM(items.barcode), 4, 1)) + 
 (IF(SUBSTR(TRIM(items.barcode), 5, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 5, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 5, 1) * 2)) + 
 (SUBSTR(TRIM(items.barcode), 6, 1)) + 
 (IF(SUBSTR(TRIM(items.barcode), 7, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 7, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 7, 1) * 2)) + 
 (SUBSTR(TRIM(items.barcode), 8, 1)) + 
 (IF(SUBSTR(TRIM(items.barcode), 9, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 9, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 9, 1) * 2)) + 
 (SUBSTR(TRIM(items.barcode), 10, 1)) + 
 (IF(SUBSTR(TRIM(items.barcode), 11, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 11, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 11, 1) * 2)) + 
 (SUBSTR(TRIM(items.barcode), 12, 1)) + 
 (IF(SUBSTR(TRIM(items.barcode), 13, 1) * 2 >= 10, (SUBSTR(TRIM(items.barcode), 13, 1) * 2) - 9, SUBSTR(TRIM(items.barcode), 13, 1) * 2)), 10), 10) <> SUBSTR(TRIM(items.barcode), 14, 1), 'Check digit bad', NULL) AS checksumproblem
 FROM items) AS quer 
WHERE lengthproblem IS NOT NULL OR libraryproblem IS NOT NULL OR checksumproblem IS NOT NULL
==== Find unused sequential barcode ranges ====
* Developer: Jared Camins-Esakov
* Module: Catalog
* Purpose: Find ranges of unused barcodes.
* Status: Completed
* Note: This query takes a *long* time. Minutes, not seconds. This query will only work on non-checksummed, sequential numeric barcodes
SELECT Convert(l.barcode, UNSIGNED) + 1 AS start, MIN(Convert(fr.barcode, UNSIGNED)) - 1 AS stop
FROM items AS l
 LEFT OUTER JOIN items AS r ON Convert(l.barcode, UNSIGNED) = Convert(r.barcode, UNSIGNED) - 1
 LEFT OUTER JOIN items AS fr ON Convert(l.barcode, UNSIGNED) < Convert(fr.barcode, UNSIGNED)
WHERE r.barcode IS NULL AND fr.barcode IS NOT NULL
GROUP BY l.barcode, r.barcode
ORDER BY l.barcode
=== Statistical reports ===
==== Shows the total number of items circulated from a branch other than the owning branch ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Statistical (Circulation)
* Purpose: Shows the total number of items circulated from a branch other than the owning branch
* Status: Complete
 SELECT count(*) as total 
 FROM statistics 
 LEFT JOIN items on (statistics.itemnumber = items.itemnumber) 
 WHERE statistics.branch != items.homebranch AND statistics.datetime BETWEEN 'DATE1' and 'DATE2'
==== New materials added ====
* Developer: Sharon Moreland
* Module: Statistical (Circulation)
* Purpose: New materials added
* Status: Complete
 SELECT count(i.biblionumber) as added, i.itype, i.homebranch, i.location from items i 
 WHERE YEAR(i.dateaccessioned) = 2009 AND MONTH(i.dateaccessioned) = 04 
 GROUP BY i.homebranch,i.itype,i.location 
 ORDER BY i.homebranch,i.itype,i.location ASC
==== List Active Patrons by Category for a Specific Month ====
* Developer: Jesse Weaver
* Module: Statistical (Circulation, Reports)
* Purpose: List Active Patrons by Category for a Specific Month
* Status: Complete
SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) 
FROM old_issues
 LEFT JOIN borrowers USING (borrowernumber) 
GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode
=== Notices Reports ===
==== Overdue Notices Sent (2 months) ====
* Developer: Nicole C. Engard, ByWater Solutions
* Module: Notices
* Purpose: Count of overdue notices sent in the last two months (by type)
* Status: Complete
SELECT monthname(message_queue.time_queued) AS month, year(message_queue.time_queued) AS year, 
 message_queue.letter_code AS notice, count(message_queue.borrowernumber) AS count 
FROM message_queue 
WHERE (month(message_queue.time_queued) ='09' OR month(message_queue.time_queued) ='10') 
 AND year(message_queue.time_queued) = '2010' AND (message_queue.letter_code = 'ODUE' 
 OR message_queue.letter_code = 'ODUE2' OR message_queue.letter_code = 'ODUE3') AND 
 status = 'sent' 
GROUP BY year(message_queue.time_queued), month(message_queue.time_queued), message_queue.letter_code
=== Serial reports ===
==== Shows the total serial received during the month ====
* Developer: Nikunj Tyagi, DPL
* Module: Serial
* Purpose: Shows the total serials received with Title, Frequency, latest issue detail
* Status: Complete
 SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate,
IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), ,
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19)))
AS FREQUENCY FROM serial, biblio,biblioitems
WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(planneddate) = 03 AND YEAR(planneddate)= 2011 AND (status)=2
ORDER BY serial.subscriptionid ASC
==== 	missing/late/claimed serial during the month ====
* Developer: Nikunj Tyagi, DPL
* Module: Serial
* Purpose: Shows the total serials missing/late/claimed with Title, Frequency, latest issue detail status 3=late,4=missing,5=claimed
* Status: Complete
 SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate, 
IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), , 
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19, 
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml, 
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19))) 
AS FREQUENCY,serial.status FROM serial, biblio,biblioitems
WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(planneddate) = XX AND YEAR(planneddate)= XXXX AND (status) BETWEEN '3' AND '5'
ORDER BY serial.subscriptionid ASC
=== WISHLIST ===
	
Requester 	Module 	Purpose of request 	SQL Request 	Notes
Arron Birch 	Catalog 	To create a report that pulls individual fields of a MARC record 	 	I am trying to run reports of specific fields of a MARC record. Preferable I would like a general report that lets me change what field I would like to run a report for. For the current assignment I am wanting to run a report with the 300 field of the MARC record.
Nora Blake 	Holds 	Statistical Count by month of number of hold requests MADE by each branch all in one report 	 	Don't want to have to run this separately for each site
Nora Blake 	Holds 	Statistical Count by month of number of hold requests FILLED by each branch all in one report 	 	Don't want to have to run this separately for each site
Nora Blake 	Catalog 	Statistical Count by month of total number of items held by each branch all in one report 	 	A report that generates total counts has been written. Is there a way to separate this out by month?
Rachel Hollis 	Catalog 	Mismatches between biblioitem 942 and item 952 	 	We think there is value in a report that identifies (by title, call number and biblio ID) records that have item mismatches, specific to our situation are 942 subfields 2 & c and 952 subfields 2 & y. Our Koha 3.01 biblio item loans are controlled by the 942. Additionally we have libraries that use Dewey, LC and locally developed classification schemes. Administration and System Preferences allow for static and variable data that can get mismatched.
Joe Tholen 	Circulation	List items not circulated in last year, by shelf location, using old_issues and issues 	 	For migrated libraries to weed with.
Scotty Zollars	Cataloging 	List all records with NULL in the source of acquistion field in the item record within a date range. 	 	ILL
Scotty Zollars	Circulation	List interlibrary loan materials check out to other libraries, by day. 	 	For ILL record keeping
Susan Bennett 	Catalog 	I need to eliminate materials that are on the holds shelf waiting for patron pick up from the following SQL. What is the flag in the record? 	 	SELECT items.barcode, items.homebranch, items.itemcallnumber, items.holdingbranch, 
items.location, items.ccode, items.onloan, biblio.author, biblio.title 
FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.holdingbranch="GW" AND items.homebranch<>items.holdingbranch AND items.onloan IS NULL 
ORDER BY items.holdingbranch asc
Scotty Zollars	Circulation	We are only one branch. Our interlibrary loan patrons are community patrons. They have the last name of ILL and the first name of the library, for example Erie Public Library. I need a list of interlibrary loan materials check out to other libraries, by month. i have the following donated so far.	 	SELECT monthname(datesent) month,COUNT(*) 
FROM branchtransfers where frombranch="MMM" and YEAR(datesent)=YEAR(NOW())-1 
GROUP BY month 
ORDER by month(datesent)
Rachel Hollis 	Circulation	Compare number of items owned by library with number circulating 	Count of items currently checked out	I was recently asked for a percentage or number of items that were checked out. We can see what is on the shelf but don't have an idea of the size of the library if nothing were checked out.
Category:Tips_&_Tricks
Category:3.2
Category:Obsolete