SQL Reports Library

From Koha Wiki

Jump to: navigation, search
Library
Home > Documentation
Home > Documentation
Home > Documentation
Home > Koha Versions > 3.10
Home > Koha Versions > 3.12
Home > Koha Versions > 3.14
Resources
Tips & Tricks > Tips & Tricks/Customising Notices and Slips > Tips & Tricks

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

I am currently constructing new pages for each section of this monster page to make things easier to read and navigate. Brooke 20:03, 27 May 2015 (EDT)

Note: If you create a new report, please add it ONLY to one of the new smaller pages below to prevent duplication.

SQL Reports Holds | SQL Reports patrons | SQL Reports circulation


Contents

SQL Reports

Tips

Documentation on SQL tables and fields

Here is the Koha DB schema

The wiki page on DB Schema

A link to more specific schemas (Acquisitions only now)

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
Links by borrower
CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,
CONCAT('<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,
CONCAT('<a href=\"/cgi-bin/koha/members/files.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',p.borrowernumber,'\">', borrowernumber, '</a>') AS borrowernumber,
CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', borrowernumber, '</a>') AS borrowernumber,
CONCAT('<a href=\"/cgi-bin/koha/members/maninvoice.pl?borrowernumber=',borrowernumber,'\" target="_blank">',borrowernumber,'</a>') AS borrowernumber,
CONCAT( '<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&object=', borrowernumber, '\">', 'Circulation log' , '</a>' ) AS borrowernumber,
Links by bib
-- Show bib record
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,
 
-- MARC detail
CONCAT('<a href=\"/cgi-bin/koha/catalogue/MARCdetail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,
 
-- Add item
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,
 
-- Place hold
CONCAT('<a href=\"/cgi-bin/koha/reserve/request.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,
 
-- Edit biblio record
CONCAT('<a href=\"cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS biblionumber,
Links by basket
CONCAT('<a href=\"/cgi-bin/koha/acqui/basket.pl?basketno=', basketno, '\">', basketno, '</a>' ) AS basketno
Links by branchcode

Circ rules:

CONCAT('<a href=\"/cgi-bin/koha/admin/smart-rules.pl?branch=', branchcode, '\">', branchcode, '</a>' ) AS branchcode
Links by item
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">', itemnumber, '</a>' ) AS itemnumber,
CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?itemnumber=', itemnumber, '\">', itemnumber, '</a>' ) AS itemnumber,
CONCAT( '<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&info=', itemnumber, '\">', 'Circulation log' , '</a>' ) AS itemnumber,
Links by subscription
CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-history.pl?subscriptionid=',subscriptionid,'\">', subscriptionid, '</a>') AS subscriptionid
Links by syspref
CONCAT('<a href=\"/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=?variable=', variable, ' : ' ,value, '\">', variable, '</a>' ) AS 'Syspref',

Query MARC

MySQL has some XML handling functions (since MySQL 5.1.5): http://dev.mysql.com/doc/refman/5.6/en/xml-functions.html


For version 17.05.x: the "marcxml" field of the "biblioitems" table changes to the "biblio_metadata" table and the "metadata" field

For example:

 
SELECT 
  ExtractValue((
    SELECT marcxml
    FROM biblioitems
    WHERE
      biblionumber=14),
      '//datafield[@tag="952"]/subfield[@code>="a"]') AS ITEM;

or the equivalent

SELECT
  ExtractValue(marcxml,'//datafield[@tag="952"]/*') AS ITEM
FROM biblioitems
WHERE biblionumber=14;

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

 
SELECT biblionumber, ExtractValue(marcxml, 'count(//datafield[@tag="505"])') AS count505 
FROM biblioitems
HAVING count505 > 1;

will give a list of biblionumbers along with the number of 505 fields (only when there is more than one 505 field)

SELECT biblionumber,  
       substring( ExtractValue(marcxml,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE', 
       title 
FROM biblioitems 
INNER JOIN biblio USING (biblionumber) 
WHERE biblionumber = 14

Control fields can be queried using ExtractValue(marcxml,'//controlfield[@tag="008"]'), and Fixed fields may be extracted using the SQL SUBSTRING() function.

Using svc/barcode in reports

The barcode service provided by /cgi-bin/koha/svc/barcode can generate barcode images which will display in the report:

SELECT
    CONCAT( p.firstname, ' ', p.surname, ' (', p.branchcode, ')' ) AS 'patron (homebranch)',
    CONCAT( 
        '<img src="/cgi-bin/koha/svc/barcode?barcode=',
         cardnumber, 
        '&type=',
        <<Barcode type (Code39 UPCE UPCA QRcode NW7 Matrix2of5 ITF Industrial2of5 IATA2of5 EAN8 EAN13 COOP2of5)>>,
        '"></img>') AS cardnumber,
    cardnumber AS 'cardnumber text'
FROM
    borrowers p

Adding the following values in authorised_values with category 'BCTYPE' will be helpful:

authorised_value lib lib_opac
Code39 Code 39 Barcode must start and end with asterisks e.g. *000042*
UPCA UPC A 11 or 12 numeric characters
UPCE UPC E 6 or 7 numeric characters
QRcode QR Code
NW7 NW7 (Codabar) May contain 0-9 + - / . + A-Z
Matrix2of5 Matrix 2 of 5 Must be entirely numeric
ITF ITF (Interleaved 2 of 5) Must be entirely numeric
Industrial2of5 Industrial 2 of 5 Must be entirely numeric
IATA2of5 IATA 2 of 5 Must be entirely numeric
EAN8 EAN8 Barcode must be 7 or 8 numeric characters
EAN13 EAN13 Barcode must be 12 or 13 numeric characters
COOP2of5 COOP 2 of 5

This allows the report to be re-written as

SELECT
    CONCAT( p.firstname, ' ', p.surname, ' (', p.branchcode, ')' ) AS 'patron (homebranch)',
    CONCAT( 
        '<img src="/cgi-bin/koha/svc/barcode?barcode=',
         cardnumber, 
        '&type=',
        <<Barcode type|BCTYPE>>,
        '"></img>') AS cardnumber,
    cardnumber AS 'cardnumber text' 
FROM
    borrowers p

action_logs

In the action_logs table, timestamp always indicates when the log entry was written, and user is either the borrowernumber of the staff member taking the action or 0 if the action was initiated by Koha itself (e.g. in the case of fines added or updated by fines.pl).

The values of action, object and info vary by module, and are shown below, along with the system preference that enables or disables the those logs.

syspref module action(s) object info
FinesLog FINES [blank], CREATE, MODIFY borrowernumber Data::Dumper output from accountlines
IssueLog, ReturnLog CIRCULATION ISSUE, RETURN borrowernumber (NULL for RETURN) itemnumber
BorrowersLog MEMBERS ADDCIRCMESSAGE, CHANGE PASS, CREATE, DELCIRCMESSAGE, DELETE, MODIFY, RENEW borrwernumber ADDCIRCMESSAGE, DELCIRCMESSAGE => message added or deleted; CHANGE PASS, CREATE, DELETE => blank; MODIFY => 'UPDATE (executed w/ arg: <borrowernumber>)', RENEW => 'Membership renewed'
SubscriptionLog SERIAL ADD, DELETE, MODIFY, RENEW subscription.subscriptionid [blank]
AuthoritiesLog AUTHORITIES ADD, DELETE, MODIFY links to auth_header.authid for ADD and DELETE, this simply says 'authority', for 'MODIFY', contains changes to the authority record.
[Always enabled] SYSTEMPREFERENCE MODIFY NULL systempreferences.value
ReportsLog REPORTS ADD, DELETE(?) saved_sql.id (i.e. the report number) saved_sql.savedsql (sql query)
CataloguingLog CATALOGUING ADD, DELETE, MODIFY itemnumber or biblionumber literal 'item' or 'biblio', followed by data dumper dump of modified data.
HoldsLog HOLDS  ? reserve_id Data dumper of reserves?
LetterLog  ?  ?  ?  ?
CronjobLog  ?  ?  ?

Runtime Parameters

If you feel that your report might be too resource intensive you might want to consider using runtime parameters to your query. Runtime parameters basically make a filter appear before the report is run to save your system resources.

There is a specific syntax that Koha will understand as 'ask for values when running the report'. The syntax is <<Question to ask|authorized_value>>.

  • The << and >> are just delimiters. You must put << at the beginning and >> at the end of your parameter
  • The 'Question to ask' will be displayed on the left of the string to enter.
  • The authorized_value can be omitted if not applicable. If it contains an authorized value category, or the exact word branches (for libraries), itemtypes (for item types), date (for a date pop up) or categorycode (for patron category), a list with the Koha authorized values or the branches, item types, or patron categories will be displayed instead of a free field Note that you can have more than one parameter in a given SQL Note that entering nothing at run time won't probably work as you expect. It will be considered as "value empty" not as "ignore this parameter". For example entering nothing for : "title=<<Enter title>>" will display results with title='' (no title). If you want to have to have something not mandatory, use "title like <<Enter title>>" and enter a % at run time instead of nothing

Examples:

SELECT surname,firstname 
FROM borrowers 
WHERE branchcode=<<Enter patrons library|branches>> AND surname LIKE <<Enter filter FOR patron surname (% IF none)>>


SELECT * 
FROM items 
WHERE homebranch = <<Pick your branch|branches>> AND barcode LIKE <<Partial barcode value here>>

Tip:

You have to put "%" in a text box to 'leave it blank'. Otherwise, it literally looks for "" (empty string) as the value for the field.

You can get around the necessity of having users enter '%' by constructing the LIKE statement as follows:

SELECT * 
FROM items 
WHERE
    homebranch = <<Pick your branch|branches>> 
    AND barcode LIKE CONCAT( '%', <<Partial barcode value here>>, '%')

Runtime Variables

If you have duplicate parameters you need during runtime, instead of asking users to enter multiple instances of the same information, you can use variables. Unlike typical MYSQL scripts, you can not set variables prior to the SELECT statement. However, you can set variables within other statement expressions. Variables typically start with '@' and must be set with ':='. Take the following example:

SELECT * 
FROM items i
WHERE i.homebranch=<<Pick your branch|branches>>
UNION
SELECT *
FROM deleteditems di
WHERE di.homebranch=<<Pick your branch|branches>>

Instead of asking for the same library twice, you could ask for it on the first instance, and pass that value along in a variable, like this:

SELECT * 
FROM items i
WHERE i.homebranch=@TargetBranch:=<<Pick your branch|branches>>
UNION
SELECT *
FROM deleteditems di
WHERE di.homebranch=@TargetBranch

If you run your query and find that your results are not as you expect, it's possible that the part of the query which contains the run-time variables may be getting eliminated by the mysql optimizer. To get around this, you can use a union with a subquery (which won't get optimized away -- one of the few times when the quirks of the mysql optimizer works in your favor)... this will be unioned with your regular query:

SELECT
    *
FROM
    (
    SELECT
        ( @CallNumber := <<Call Number (USE % AS wildcard) >>) AS title,
        ( @StartDate := <<Start date|date>>) AS author,
        ( @EndDate := <<End date|date>>) AS publishercode,
        0 AS 'publication year',
        0 AS editionstatement,
        0 AS itemlost,
        0 AS withdrawn,
        0 AS 'item type',
        0 AS 'issue count',
        0 AS 'renewal count',
        0 AS 'local use count'
    ) AS set_variables
WHERE 0 = 1
UNION 
SELECT
    b.title,
    b.author,
    bi.publishercode,
    SUBSTR(ExtractValue(bi.marcxml,'//controlfield[@tag="008"]'),8,4)  AS 'publication year',
    bi.editionstatement,
    i.itemlost,
    i.withdrawn,
    i.itype AS 'item type',
    COUNT( IF( statistics.type = 'issue', statistics.itemnumber, NULL ) )  AS 'issue count',
    COUNT( IF( statistics.type = 'renew', statistics.itemnumber, NULL ) )  AS 'renewal count',
    COUNT( IF( statistics.type = 'localuse', statistics.itemnumber, NULL ) )  AS 'local use count'
FROM
    biblio b
    INNER JOIN biblioitems bi USING (biblionumber)
    INNER JOIN deleteditems i ON (
        b.biblionumber = i.biblionumber
        AND i.itemcallnumber LIKE @CallNumber
        AND i.dateaccessioned >= @StartDate
        AND date(i.timestamp) <=  @EndDate
    )
    LEFT JOIN statistics USING ( itemnumber )
WHERE
   date( statistics.datetime ) BETWEEN @StartDate AND @EndDate
GROUP BY itemnumber

The WHERE 0 = 1 in the where clause of the first query is used so that the rows where you are setting the variables don't display.

How to avoid errors

Cast datetime or timestamp to date in BETWEEN tests

Without the cast, '2016-03-14 19:01:34' is not between '2016-03-12' and '2016-03-14':

SELECT '2016-03-14 19:01:34' BETWEEN '2016-03-12' AND '2016-03-14' AS `is between`;
+------------+
| IS BETWEEN |
+------------+
|          0 |
+------------+

With the cast, '2016-03-14 19:01:34' is between '2016-03-12' and '2016-03-14':

SELECT date ( '2016-03-14 19:01:34' ) BETWEEN '2016-03-12' AND '2016-03-14' AS `is between`;
+------------+
| IS BETWEEN |
+------------+
|          1 |
+------------+

Use DATEDIFF() to subtract dates

When selecting dates that are close to each other, it looks like the minus-operator ('-') does what it's supposed to:

SELECT DATE('2016-12-21'), DATE('2016-12-21') - DATE('2016-12-20');
+--------------------+-----------------------------------------+
| DATE('2016-12-21') | DATE('2016-12-21') - DATE('2016-12-20') |
+--------------------+-----------------------------------------+
| 2016-12-21         |                                       1 |
+--------------------+-----------------------------------------+
1 row IN SET (0.00 sec)

However, subtracting dates that are farther apart shows that this doesn't actually work:

SELECT DATE('2016-12-21'), DATE('2016-12-21') - DATE('2016-05-13');
+--------------------+-----------------------------------------+
| DATE('2016-12-21') | DATE('2016-12-21') - DATE('2016-05-13') |
+--------------------+-----------------------------------------+
| 2016-12-21         |                                     708 |
+--------------------+-----------------------------------------+
1 row IN SET (0.00 sec)


The real difference in days, as shown using DATEDIFF():

SELECT DATE('2016-12-21'), DATEDIFF( DATE('2016-12-21'), DATE('2016-05-13') );
+--------------------+----------------------------------------------------+
| DATE('2016-12-21') | DATEDIFF( DATE('2016-12-21'), DATE('2016-05-13') ) |
+--------------------+----------------------------------------------------+
| 2016-12-21         |                                                222 |
+--------------------+----------------------------------------------------+
1 row IN SET (0.00 sec)

SQL Report Summary Template

List # of copies of items with bib details

  • Developer: Matthew Charlesworth
  • Module: Catalogue
  • Purpose: The report allows direct interaction with the records in the Staff Interface (using example of CONCAT with A HREF and TITLE codes). Lists Bib details of items and # of copies as well as bibnumber to go directly to item record.
  • Status: Completed
SELECT DISTINCT CONCAT('<a title="Search for Title with Biblionumber:
',b.biblionumber,'"
href="/cgi-bin/koha/catalogue/search.pl?q=ti%3A',REPLACE (b.title, '
', '+'),'">Search</a>') AS "Search for Title", b.biblionumber, CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'">',b.title,'</a>')
AS "Item Title", b.author, t.editionstatement, t.publishercode, t.isbn, count(i.itemnumber) AS "Copies" FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) GROUP BY b.biblionumber ORDER BY Copies ASC

Holds

We are in the process of moving holds to a separate page -- Please put new holds reports in SQL Reports Holds

Duplicate Holds

  • Developer: Liz Rea (NEKLS)
  • Module: Holds
  • Purpose: This report will detect "double-click" placed duplicate holds. Do note that it doesn't necessarily mean anything (currently) as processes done on one of the duplicates will do the same to the other (deleting one deletes both, for example).
  • Status: Completed
SELECT reserves.biblionumber, reserves.borrowernumber, biblio.title, borrowers.firstname, borrowers.surname 
FROM reserves 
LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber) 
LEFT JOIN borrowers ON (reserves.borrowernumber=borrowers.borrowernumber) 
GROUP BY reserves.borrowernumber, reserves.biblionumber 
HAVING (COUNT(reserves.borrowernumber)>=2 AND COUNT(reserves.biblionumber)>=2)


Patrons with Holds Waiting at Library

Moved to SQL_Reports_Holds#Patrons_with_Holds_Waiting_at_Library

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 i.homebranch, h.branchcode, monthname(h.reservedate) AS month, 
       year(h.reservedate) AS year, count(*) 
FROM old_reserves h
LEFT JOIN items i USING (itemnumber)
WHERE h.branchcode != i.homebranch AND year(h.reservedate) = <<Year>> 
GROUP BY month(h.reservedate), h.branchcode

List of all Patrons from a Single Branch with open Hold Requests

Moved to SQL_Reports_Holds#List_of_all_Patrons_from_a_Single_Branch_with_open_Hold_Requests

List of all items currently on loan at another library

Moved to SQL_Reports_Holds#List_of_all_items_currently_on_loan_at_another_library

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 issues 
LEFT JOIN items ON issues.itemnumber=items.itemnumber 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE 
  issues.branchcode= <<Issuing branch|branches>>
  AND 
  items.holdingbranch !=  items.homebranch 
ORDER BY
  items.homebranch, issues.issuedate, biblio.title

List of titles with holds and on loan in a specific cardnumber

Moved to SQL_Reports_Holds#List_of_titles_with_holds_and_on_loan_in_a_specific_cardnumber_

Holds placed in date range

Moved to SQL_Reports_Holds#Holds_placed_in_date_range

Holds filled in date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Holds
  • Purpose: It will show you with a count of holds filled in that time period
  • Status: Complete
SELECT count(*) AS 'holds filled'
FROM old_reserves
WHERE date(timestamp) BETWEEN <<Between (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND found='F'

Monthly in holds placed by branch in current year

  • Developer: Nick Clemens
  • 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
 month(reservedate) = <<Enter month MM>> AND year(reservedate)=year(now())
GROUP BY branchcode

Monthly holds both placed and filled, by branch

Moved to SQL_Reports_Holds#Monthly_holds_both_placed_and_filled.2C_by_branch

Monthly holds filled by branch

Moved to SQL_Reports_Holds#Monthly_holds_filled_by_branch

Overdues With Holds Waiting

Moved to SQL_Reports_Holds#Overdues_With_Holds_Waiting

Top 10 Titles Placed on Hold in the Last 6 Months

Moved to SQL_Reports_Holds#Top_10_Titles_Placed_on_Hold_in_the_Last_6_Months

Holds to Pull

Moved to SQL_Reports_Holds#Holds_to_Pull

Holds to Pull at Branch

  • Developer: Nicole C. Engard, ByWater Solutions (modified by Nick Clemens, ByWater Solutions)
  • Module: Holds/Circulation
  • Purpose: List items that are on hold, not checked out and not waiting on the holds shelf at a specific branch. If hold is next available it is marked as such and all local items available are grouped in the report
  • Status: Complete

Warning -- this report excludes *patrons* who have waiting items, but does not exclude the *items* that are waiting.

SELECT b.title, b.author, IF(i.itemnumber IS NULL, "Next Available","Item Specfic") AS Hold_type,IFNULL(i.itemcallnumber, GROUP_CONCAT(i2.itemcallnumber)) AS Call_Number_s, IFNULL(i.barcode,GROUP_CONCAT(i2.barcode)) AS Barcode_s, 
       date(r.timestamp) AS "hold date", r.branchcode AS 'pickup branch', 
       IFNULL(i.homebranch,i2.homebranch) AS 'owning branch', p.surname, p.firstname, p.cardnumber, CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'">Title Record</a>') AS Link_to_title
FROM reserves r
LEFT JOIN biblio b ON (r.biblionumber=b.biblionumber)
LEFT JOIN items i ON (r.itemnumber = i.itemnumber)
LEFT JOIN items i2 ON (i2.biblionumber=b.biblionumber)
LEFT JOIN borrowers p USING (borrowernumber)
WHERE IFNULL(r.itemnumber,i2.itemnumber) NOT IN (SELECT issues.itemnumber FROM issues)
     AND IFNULL(r.itemnumber,i2.itemnumber) NOT IN (SELECT branchtransfers.itemnumber FROM branchtransfers WHERE datearrived IS NULL) 
     AND r.waitingdate IS NULL AND IFNULL(i.homebranch,i2.homebranch)=<<Branch filled at|branches>>
GROUP BY reserve_id
ORDER BY Call_Number_s ASC

Count of Holds by Month

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Holds/Circulation
  • Purpose: This report asks for you to enter a year (twice) and then shows you holds counts for all months that year.
  • Status: Complete


SELECT month, sum(count) AS holds
FROM (
SELECT MONTHNAME(reservedate) AS month, count(*) AS count
FROM reserves
WHERE YEAR(reservedate) = <<Hold Year (yyyy)>>
GROUP BY month
UNION ALL
SELECT MONTHNAME(reservedate) AS month, count(*) AS count
FROM old_reserves
WHERE YEAR(reservedate) = <<Repeat Hold Year (yyyy)>>
GROUP BY month
) AS myholds
GROUP BY month
ORDER BY month ASC


Cancelled Holds

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Holds
  • Purpose: A list of holds that were cancelled by branch and date range
  • Status: Complete
SELECT b.title, b.author, p.surname, p.firstname, r.reservedate, 
       r.cancellationdate, r.branchcode 
FROM old_reserves r 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN borrowers p USING (borrowernumber) 
WHERE r.cancellationdate BETWEEN <<Cancelled BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND r.branchcode =<<Branch|branches>>


Single Title Holds

Moved to SQL_Reports_Holds#Single_Title_Holds

Holds Ratio by Homebranches

  • Developer: George H. Williams - Latah County Library District
  • Module: Holds
  • Purpose: Essentially the same as "Hold Ratios" in the circulation reports but with item Homebranch information instead of Holdingbranch
  • Notes: I didn't build the link to the biblio in the title field (as it is in the circulation report) so it would be easy for our staff to download the file as a spreadsheet if necessary. I also included some very basic 'notforloan' data for their information.
  • Status: Complete
SELECT COUNT(DISTINCT reserves.borrowernumber) AS HOLDCOUNT,
     COUNT(DISTINCT items.itemnumber) AS ITEMCOUNT,
     CAST(COUNT(DISTINCT reserves.borrowernumber) AS DECIMAL) / CAST(COUNT(DISTINCT items.itemnumber) AS DECIMAL) AS RATIO,
     biblio.title,
     CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber,'\" target="_blank">', biblio.biblionumber, '</a>' ) AS 'LINK_TO_BIBLIO',
     GROUP_CONCAT(DISTINCT items.homebranch SEPARATOR ' // ') AS HOMEBRANCHES,
     GROUP_CONCAT(DISTINCT items.location SEPARATOR ' // ') AS LOCATIONS,
     GROUP_CONCAT(DISTINCT items.itype SEPARATOR ' // ') AS ITYPES,
     GROUP_CONCAT(DISTINCT items.itemcallnumber SEPARATOR ' // ') AS CALLNUMBERS,
     GROUP_CONCAT(DISTINCT items.notforloan SEPARATOR ' // ') AS NOTLOAN
  FROM  reserves LEFT JOIN items ON items.biblionumber=reserves.biblionumber
     LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber
  WHERE items.itemlost=0
     AND items.damaged=0
  GROUP BY biblio.biblionumber
  HAVING CAST(COUNT(DISTINCT reserves.borrowernumber) AS DECIMAL) / CAST(COUNT(DISTINCT items.itemnumber) AS DECIMAL) >= 3.0
  ORDER BY RATIO DESC

Holds Queue Workaround

  • Developer: George H. Williams (Latah County Library District) and Christopher Brannon (Coeur d'Alene Public Library)
  • Module: Holds
  • Purpose: In Koha 3.10 there is a bug that is making the built in "Holds queue" report in the circulation module work incorrectly (bug 9950). This report can act as a replacement for the built in report until the bug is fixed. UPDATE The fix for bug 9950 is now available as of Koha 3.10.8.
  • Status: Deprecated -- the workaround is no longer needed if you upgrade to 3.10.8 or later
SELECT items.location, authorised_values.lib_opac AS 'COLLECTIONCODE', items.itemcallnumber, items.enumchron, biblio.author, concat(biblio.title, ' ',ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]')) AS 'TITLE',items.barcode, reserves.suspend
  FROM (((((reserves INNER JOIN biblio ON reserves.biblionumber = biblio.biblionumber) 
     INNER JOIN borrowers ON reserves.borrowernumber = borrowers.borrowernumber) 
     INNER JOIN items ON biblio.biblionumber = items.biblionumber) 
     LEFT JOIN biblioitems bi ON (biblio.biblionumber=bi.biblionumber)
     LEFT JOIN hold_fill_targets ON (reserves.biblionumber = hold_fill_targets.biblionumber) 
     AND (reserves.borrowernumber = hold_fill_targets.borrowernumber)) 
     LEFT JOIN branchtransfers ON items.itemnumber = branchtransfers.itemnumber) 
     LEFT JOIN authorised_values ON items.ccode = authorised_values.authorised_value
  WHERE (((authorised_values.category)="ccode") 
     AND ((reserves.reservedate)<=now()) 
     AND ((reserves.waitingdate) IS NULL) 
     AND ((reserves.priority)=1)
     AND ((items.itemnumber NOT IN (SELECT itemnumber FROM reserves WHERE itemnumber=items.itemnumber AND found IS NOT NULL)))
     AND ((items.notforloan)=0) 
     AND ((items.damaged)=0) 
     AND ((items.itemlost)=0)
     AND ((items.withdrawn)=0) 
     AND ((items.onloan) IS NULL) 
     AND ((reserves.itemnumber) IS NOT NULL 
     AND (reserves.itemnumber)=items.itemnumber) 
     AND ((items.itype)<>"REF") 
     AND ((hold_fill_targets.itemnumber) IS NULL)) 
     OR (((authorised_values.category)="ccode") 
     AND ((hold_fill_targets.itemnumber)=items.itemnumber))
    AND ((reserves.found) IS NULL)
  GROUP BY items.holdingbranch, authorised_values.lib_opac, items.itemcallnumber, items.enumchron, biblio.author, biblio.title, items.barcode, reserves.branchcode
  HAVING items.holdingbranch=<<Branch filled at|branches>>
     AND Count(branchtransfers.datesent)=Count(branchtransfers.datearrived)
     AND reserves.suspend=0
  ORDER BY items.location, authorised_values.lib_opac, items.itemcallnumber, items.enumchron, biblio.author, biblio.title ASC

Average wait time on holds

  • Developer: Melia Meggs, ByWater Solutions
  • Module: Holds
  • Purpose: Average wait of a hold (from the date placed to the arrival of the item to the branch)
  • Status: Complete
SELECT o.itemnumber, 
      (SELECT AVG(ABS(DATEDIFF(o.reservedate, o.waitingdate)))) AS 'Average days waiting for hold', 
      b.title, b.author, b.copyrightdate, i.barcode, i.dateaccessioned, i.ccode 
FROM old_reserves o 
LEFT JOIN items i ON (o.itemnumber = i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber = b.biblionumber) 
WHERE o.waitingdate IS NOT NULL AND o.cancellationdate IS NULL
      AND o.reservedate BETWEEN <<Hold placed BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> 
GROUP BY o.itemnumber, b.title, b.author, b.copyrightdate,
i.barcode, i.dateaccessioned, i.ccode 
ORDER BY (SELECT AVG(ABS(DATEDIFF(o.reservedate, o.waitingdate)))) ASC

Average wait time on holds

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Holds
  • Purpose: Average wait of a hold (from the date placed to the arrival of the item to the branch)
  • Status: Complete
  • Notes: Differs from the previous report in the ordering of the results. Titles are shown in the order of the sum of the wait times -- A title with six holds which wait for one day ranks above a title with a single hold that waits 5 days. There's no date range, although one could easily be added.
SELECT
    title,
    biblionumber, 
    SUM( datediff(waitingdate, reservedate ))/count(biblionumber) AS average_hold_days, 
    count(biblionumber) AS 'hold count'
FROM
    old_reserves INNER JOIN biblio USING (biblionumber) 
WHERE
    found = 'F' 
GROUP BY
    biblionumber
ORDER BY SUM( datediff(waitingdate, reservedate )) DESC

Titles with more than X holds

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Holds
  • Purpose: Titles with more than the entered number of holds on them
  • Status: Complete
SELECT concat(b.title, ' ', ExtractValue(m.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, 
       count(h.reservedate) AS 'holds' 
FROM biblio b 
LEFT JOIN biblioitems m USING (biblionumber) 
LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber) 
GROUP BY b.biblionumber 
HAVING count(h.reservedate) >= <<Min number of holds>>


Stale Holds

Moved to SQL_Reports_Holds#Stale_Holds

Unfilled Holds

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Holds
  • Purpose: This report will list all holds no waiting and provide info on whether it is a title or item level hold as well as showing the availability of items on the record
  • Status: Completed
SELECT reserves.found,borrowers.surname, borrowers.firstname, borrowers.cardnumber, reserves.reservedate 
AS 'date reserved', reserves.priority, CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'">',biblio.title,'</a>') AS title, biblio.author, GROUP_CONCAT(items.barcode,'-',IF(r2.found IS NOT NULL,'reserved',IF(onloan IS NOT NULL,'onloan',IF(notforloan!=0,'notforloan',IF(itemlost!=0,'itemlost','available')))) SEPARATOR ' | ') AS itemstatuses, IF(reserves.itemnumber IS NULL, 'Next Available',barcode) AS 'Specific Item'
FROM reserves
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN biblio USING (biblionumber)
LEFT JOIN biblioitems USING (biblionumber)
LEFT JOIN items USING (biblionumber)
LEFT JOIN reserves r2 ON r2.itemnumber=items.itemnumber
WHERE (reserves.found IS NULL OR reserves.found != 'W')
GROUP BY reserves.reserve_id
ORDER BY title

Holds by Year, Month and Status

  • Developer: Bartn Chittenden, ByWater Solutions
  • Module: Holds
  • Purpose: Statistical overview of holds, broken out by YEAR, MONTH and Status
  • Status: Completed
SELECT
     YEAR(timestamp),
     MONTH(timestamp),
     STATUS,
     count(*) FROM (
             SELECT
                CASE
                    WHEN found IS NULL THEN 'PLACED'
                    WHEN found = 'W' THEN 'WAITING'
                    WHEN found = 'S' THEN 'SUSPENDED'
                    WHEN found = 'T' THEN 'IN TRANSIT'
                END  AS STATUS,
                timestamp
             FROM reserves
         UNION
            SELECT
                CASE
                    WHEN found IS NULL THEN 'CANCLED'
                    WHEN found = 'F' THEN 'FILLED'
                END  AS STATUS,
                timestamp
             FROM old_reserves
     ) AS holds
GROUP BY
     YEAR(timestamp), MONTH(timestamp), STATUS
ORDER BY
     YEAR(timestamp), MONTH(timestamp), STATUS

Patron Reports

We are in the process of moving patrons to a separate page -- Please put new patrons reports in SQL Reports patrons

Patrons Whose Age Violates Category Age Limits

  • Developer: Kyle M Hall, ByWater Solutions
  • Module: circ
  • Purpose: Lists all patrons whose age is above or below the maximum and/or minimum age range for his or her category
  • Status: Complete


SELECT
   c.description AS category,
   b.surname,
   b.firstname,
   b.dateofbirth AS DoB,
   IFNULL(c.dateofbirthrequired,0) AS MinAge,
   IFNULL( c.upperagelimit, 999 ) AS MaxAge,
   FLOOR(DATEDIFF (NOW(), b.dateofbirth)/365)  AS age  
FROM
   borrowers b 
LEFT JOIN
   categories c USING ( categorycode ) 
WHERE
   (
      c.upperagelimit > 0 
      OR c.dateofbirthrequired > 0 
   ) 
HAVING
   age NOT BETWEEN MinAge AND MaxAge

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

Patron Birthday Report

Moved to SQL_Reports_Patrons#Patron_Birthday_Report

Patrons of specific age range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: patrons
  • Purpose: This report shows patrons between the age of 12 and 13
  • Status: Complete
SELECT cardnumber, firstname, surname, dateofbirth, 
      (YEAR(CURDATE( )) - YEAR(dateofbirth) - IF(RIGHT(CURDATE( ),5) < RIGHT(dateofbirth,5),1,0)) AS 'age in years', 
      categorycode 
FROM borrowers 
WHERE DATEDIFF(now(), dateofbirth) < ((13*365)+4) 
      AND DATEDIFF(now(), dateofbirth) > ((12*365)+4)

Patrons with Staff Permissions

Moved to SQL_Reports_Patrons#Patrons_with_Staff_Permissions

Superlibrarians

Moved to SQL_Reports_Patrons#Superlibrarians

Patrons with staff permission, and if they are superlibrarians

Moved to SQL_Reports_Patrons#Patrons_with_staff_permission.2C_and_if_they_are_superlibrarians

New Patrons by Category at Branch

Moved to SQL_Reports_Patrons#New_Patrons_by_Category_at_Branch

New Patrons by Category in Date Range

Moved to SQL_Reports_Patrons#New_Patrons_by_Category_in_Date_Range

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 surname, firstname, borrowernumber 
  FROM borrowers 
  WHERE borrowernumber 
  NOT IN (SELECT borrowernumber FROM issues) 
  AND YEAR(dateexpiry) = <<Year>>

Missing Emails

  • Developer: Sharon Moreland
  • Module: Patrons
  • Purpose: Missing e-mails
  • Status: Complete


 SELECT cardnumber, surname, firstname, branchcode, debarred, dateexpiry 
  FROM borrowers 
  WHERE email IS NULL

Patrons w/ Checked Out Items

  • Developer: Nora Blake
  • Module: Circulation
  • Purpose: List of items checked out to patrons
  • Status: Complete


  SELECT issues, biblio.title, author, surname, firstname, borrowers.sort1, 
         items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate 
  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 issues.branchcode=<<Checked out at|branches>>
  ORDER BY issues.branchcode, borrowers.sort1, borrowers.surname, issues.issuedate, biblio.title

New Patron Count (previous month)

Moved to SQL_Reports_Patrons#New_Patron_Count_.28previous_month.29

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)

Moved to SQL_Reports_Patrons#New_Patrons_by_Branch_.28year_to_date.29

Count of New Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: Count of patrons added between two dates at a branch
  • Status: Complete
SELECT COUNT(borrowernumber) AS 'New Patrons' 
FROM borrowers 
WHERE dateenrolled BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND branchcode=<<Branch|branches>>


Count of patrons who's cards will expire after a specific date, group by category

  • Developer: E. Guertin
  • Module: Patrons
  • Purpose: Count of patrons with valid card at a specific date (expiration after a specific date), count by patron category.
  • Status: Complete
SELECT categorycode, COUNT(*) 
FROM borrowers
WHERE dateexpiry > <<YYYY-MM-DD>>
GROUP BY 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(cardnumber) AS count
FROM borrowers  
WHERE dateexpiry < <<Today's Date (yyyy-mm-dd)|date>>

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 p.surname, p.firstname, p.cardnumber, a.code, a.attribute 
FROM borrowers p
LEFT JOIN borrower_attributes a USING (borrowernumber)
GROUP BY a.attribute 
ORDER BY p.surname, p.firstname ASC

Patrons with a Specific Attribute Code

Moved to SQL_Reports_Patrons#Patrons_with_a_Specific_Attribute_Code

Patrons with a Specific Attribute Value

Moved to SQL_Reports_Patrons#Patrons_with_a_Specific_Attribute_Value

Duplicate Patrons

Moved to SQL_Reports_Patrons#Duplicate_Patrons

Restricted Patrons

Moved to SQL_Reports_Patrons#Restricted_Patrons

Patrons with notes or messages

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: Patrons with notes and messages on their accounts
  • Status: Completed
SELECT b.cardnumber, b.surname, b.firstname,
b.opacnote, b.borrowernotes, group_concat(DISTINCT m.message separator ', ') AS circmesages
FROM borrowers b
LEFT JOIN messages m USING (borrowernumber)
WHERE b.branchcode=<<Branch|branches>> AND ((b.opacnote IS NOT
NULL AND b.opacnote != '') OR (b.borrowernotes IS NOT NULL AND
b.borrowernotes != '') OR (m.message IS NOT NULL AND
m.message != '')) GROUP BY b.borrowernumber ORDER BY b.surname ASC,
b.firstname ASC

Patrons with No Checkouts

Moved to SQL_Reports_Patrons#Patrons_with_No_Checkouts

Patron with messages but no email

  • Developer: Amy Boisvert, VOKAL
  • Module: Patrons
  • Purpose: Patrons with email addresses that do not have the patron messaging preference for holds checked.
  • Status: Completed
SELECT b.surname, b.firstname, b.cardnumber, b.email
FROM borrowers b
     LEFT JOIN (SELECT p.borrowernumber
                FROM borrower_message_preferences p 
                INNER JOIN borrower_message_transport_preferences t
                ON p.borrower_message_preference_id=t.borrower_message_preference_id
                WHERE p.message_attribute_id=4) e 
     ON b.borrowernumber=e.borrowernumber
WHERE b.branchcode=<<Your branch|branches>>
AND IFNULL(b.email,'') <>'' AND e.borrowernumber IS NULL

Borrower messaging preferences by branch

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Patrons
  • Purpose: Show messaging preferences for all borrowers at a given branch
  • Status: Completed
SELECT 
    borrowernumber,
    days_in_advance, 
    wants_digest AS 'digest only', 
    message_name, 
    group_concat( DISTINCT message_transport_type SEPARATOR ',') AS 'message type'
FROM 
    borrower_message_preferences
    INNER JOIN message_attributes USING (message_attribute_id)
    INNER JOIN message_transports USING (message_attribute_id) 
    INNER JOIN borrowers USING (borrowernumber)
WHERE
    borrowers.branchcode = <<Branch|branches>>
GROUP BY borrowernumber, message_name
ORDER BY borrowernumber

Count Active Patrons

Moved to SQL_Reports_Patrons#Count_Active_Patrons

Count Active Patrons by Category for a Specific Month

  • Developer: Jesse Weaver
  • Module: Statistical (Circulation, Reports)
  • Purpose: Count 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

List Active Patrons

Moved to SQL_Reports_Patrons#List_Active_Patrons

Count of active patrons by category, filtered by date range

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Statistical (Circulation, Reports)
  • Purpose: Break down count of active borrowers by category
  • Status: Complete
SELECT
    count(*) AS count,
    categorycode
FROM
    borrowers
WHERE
    borrowernumber IN (
        SELECT
            borrowernumber
        FROM
            statistics
        WHERE
             DATE( datetime ) BETWEEN <<Has activity BETWEEN (YYYY-MM-dd)|date>> AND <<and|date>>
    )
GROUP BY
    categorycode

Patron search by sort1

Moved to SQL_Reports_Patrons#Patron_search_by_sort1

Expired Patrons

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Patrons
  • Purpose: A list of expired patrons with the money they owe and their guarantor information
  • Status: Complete
SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname, 
       p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, ' (', g.cardnumber, ')'),'') AS guarantor, 
       p.relationship, ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
WHERE p.dateexpiry < NOW() 
GROUP BY p.borrowernumber
ORDER BY p.dateexpiry ASC


Guarantor List

  • Developer: Bernardo Gonzalez Kriegel
  • Module: Patrons
  • Purpose: A list of guarantors and guarantees
  • Status: Complete
SELECT 
  IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'') AS guarantor,
  IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'') AS guarantee
FROM
(  
  SELECT * 
  FROM borrowers
  WHERE guarantorid  IS NOT NULL
) AS p
LEFT JOIN borrowers AS g 
  ON p.guarantorid  = g.borrowernumber
ORDER BY g.borrowernumber

Patron Permissions

Moved to SQL_Reports_Patrons#Patron_Permissions

Permissions Check

Moved to SQL_Reports_Patrons#Permissions_Check

Patron without image

Moved to SQL_Reports_Patrons#Patron_without_image

Missing or invalid email format

Moved to SQL_Reports_Patrons#Missing_or_invalid_email_format

Ex Heavy Borrowers

  • Developer: Chris Hall for Horowhenua Library Trust, Catalyst
  • Module: Borrowers
  • Purpose: To show which borrowers, given a certain date range, have transformed from heavy borrowers to light borrowers. The report takes two ranges of dates, calculates the number of issues in each, and according to the thresholds set in the report, shows us borrowers who used to borrow a lot, but now don't borrow as much.
  • Status: Completed
SELECT first.borrowernumber, first.firstname, first.surname, first.cardnumber, issues_before, issues_after FROM(
SELECT borrowernumber, firstname, surname, cardnumber, count(old_issues.itemnumber) AS issues_before
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of first range (yyyy-mm-dd)|date>>
AND issuedate < <<End of first range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS first
JOIN (
SELECT borrowernumber, count(old_issues.itemnumber) AS issues_after
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of second range (yyyy-mm-dd)|date>>
AND issuedate < <<End of second range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS second
WHERE first.borrowernumber = second.borrowernumber
AND issues_after < 20 AND issues_before > 60 -- borrowers borrowed fewer than 20 items in the second range, and more than 60 in the first range.

Export patrons

  • Developer: Heinrich Hartl
  • Module: Borrowers
  • Purpose: To create a sample spreadsheet for patron import or to export patron data from one library for import into another library
  • Status: Completed
SELECT cardnumber, surname, 
          firstname, title, othernames, initials, streetnumber, streettype, address, address2, city, state, zipcode, country, email, phone, mobile, fax,  
          emailpro, phonepro, B_streetnumber, B_streettype, B_address, B_address2, B_city, B_state, B_zipcode, B_country, B_email, B_phone, dateofbirth, 
          branchcode, categorycode, dateenrolled, dateexpiry, gonenoaddress, lost, debarred, debarredcomment, contactname, contactfirstname, contacttitle,
          guarantorid, borrowernotes, relationship, ethnicity, ethnotes, sex, password, flags, userid, opacnote, contactnote, sort1, sort2, altcontactfirstname,
          altcontactsurname, altcontactaddress1, altcontactaddress2, altcontactaddress3, altcontactstate, altcontactzipcode, altcontactcountry, altcontactphone,
          smsalertnumber, privacy
 
FROM borrowers 
WHERE branchcode=<<Select your branch|branches>> 
ORDER BY surname ASC, firstname ASC

Count of deleted patrons

  • Developer: George H. Williams (Northeast Kansas Library System)
  • Module: Patrons
  • Purpose: Count patrons deleted during a specified date range grouped by home branch and category
  • Status: Complete
  • Version: Should work in any version provided you have the BorrowersLog system preference set to log changes to borrowers
  • Notes: If you write the query to say "WHERE action_logs.action = 'DELETE'" Koha will give you an error message saying that "DELETE" is an SQL keyword that you can't use in the Koha reporting module. If you write it, instead, to say "WHERE actionlogs.action LIKE 'DELET%'" you can work around the keyword issue because you're not using the actual keyword.
SELECT
  deletedborrowers.branchcode AS PATRON_HOME_LIBRARY,
  deletedborrowers.categorycode AS PATRON_CATEGORY,
  Count(deletedborrowers.borrowernumber) AS COUNT_OF_PATRONS_DELETED
FROM
  action_logs JOIN
  deletedborrowers
    ON deletedborrowers.borrowernumber = action_logs.object
WHERE
  (action_logs.timestamp BETWEEN <<between the beginning of the day ON "START DATE"|date>>  AND (<<and the end of the day ON "END DATE"|date>>+ INTERVAL 1 DAY)) AND
  action_logs.action LIKE 'DELET%'
GROUP BY
  PATRON_HOME_LIBRARY,
  PATRON_CATEGORY
  WITH ROLLUP

Circulation Reports

We are in the process of moving circulation to a separate page -- Please put new circulation reports in SQL Reports circulation

Date Wise List of Checked In Books

Moved to SQL_Reports_Circulation#Date_Wise_List_of_Checked_In_Books

Date Wise List of Checked Out Books

Moved to SQL_Reports_Circulation#Date_Wise_List_of_Checked_Out_Books

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

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

Moved to SQL_Reports_Circulation#Circulation_numbers_in_a_time_frame_for_all_itemtypes.2C_per_branch

Circulation of Two Call Numbers

Moved to SQL_Reports_Circulation#Circulation_of_Two_Call_Numbers

Track In House Use

Moved to SQL_Reports_Circulation#Track_In_House_Use

Track In House Use Hourly

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Number of internal circs on a particular day in a particular time range
  • Status: Complete
SELECT hour(datetime) AS hour, count(*) AS count 
FROM statistics 
WHERE type='localuse' AND date(datetime)=<<Date (yyyy-mm-dd)|date>> 
      AND time(datetime) BETWEEN <<Time BETWEEN (hh:mm)>> 
      AND <<and (hh:mm)>>


Track In House Use in Hourly Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: Number of internal circs broken out by hour on a particular day
  • Status: Complete
SELECT hour(datetime) AS hour, count(*) AS count 
FROM statistics 
WHERE type='localuse' AND date(datetime)=<<Date (yyyy-mm-dd)|date>>

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

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


All Checked Out Books

Moved to SQL_Reports_Circulation#All_Checked_Out_Books

Overdues w/ Contact Info

Moved to SQL_Reports_Circulation#Overdues_w.2F_Contact_Info

Overdues Call List w/ Guarantor

Moved to SQL_Reports_Circulation#Overdues_Call_List_w.2F_Guarantor

Overdues by Homeroom/Attribute

Moved to SQL_Reports_Circulation#Overdues_by_Homeroom.2FAttribute

Overdues w/item info when patron has no email

Moved to SQL_Reports_Circulation#Overdues_w.2Fitem_info_when_patron_has_no_email

Overdues by Item Type

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Circulation
  • Purpose: A report that shows overdue items based on item type.
  • Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, 
       p.phone, p.address, p.city, p.zipcode, 
       c.date_due, 
       (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', 
       b.title, b.author, i.itemcallnumber, 
       i.barcode
FROM borrowers p
LEFT JOIN issues c USING (borrowernumber) 
LEFT JOIN items i USING (itemnumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
WHERE i.itype = <<Item Type|itemtypes>> AND c.date_due < curdate()
ORDER BY p.surname ASC, c.date_due ASC

Checkouts by Call Number (previous month)

Moved to SQL_Reports_Circulation#Checkouts_by_Call_Number_.28previous_month.29

Renewals by Call Number (previous month)

Moved to SQL_Reports_Circulation#Renewals_by_Call_Number_.28previous_month.29

Checkouts by Item Type (previous month)

  • Developer: Galen Charlton, Equinox
  • Module: Circ
  • Purpose:
  • Status: Complete
  • Note: This can take a while to run because of the union of items and deleteditems, but has the advantage that items that get circulated, then deleted, during the previous month will get reported using their correct item type.
SELECT  all_items.itype AS "Item Type" ,count(*) AS 'Checkouts' 
FROM statistics 
JOIN (
  SELECT itemnumber, itype FROM deleteditems
  UNION
  SELECT itemnumber, itype FROM items 
) AS all_items USING (itemnumber)
WHERE statistics.type = 'issue' 
AND statistics.datetime >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') 
AND statistics.datetime <= LAST_DAY(now() - interval 1 month)
GROUP BY all_items.itype 
ORDER BY all_items.itype ASC;

Checkouts by 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  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 datetime BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND type IN ('issue','renew') 
GROUP BY itype

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

Moved to SQL_Reports_Circulation#Previous_Month.27s_Checkouts.2FRenewals_by_Collection_Code

Previous Month Checkouts/Renews by Patron Category

Moved to SQL_Reports_Circulation#Previous_Month_Checkouts.2FRenews_by_Patron_Category

All Circ Actions on Date

Moved to SQL_Reports_Circulation#All_Circ_Actions_on_Date

All Circ Actions on Patron categories and Date

Moved to SQL_Reports_Circulation#All_Circ_Actions_on_Patron_categories_and_Date

Checkouts & Renewals in Date Range

Moved to SQL_Reports_Circulation#Checkouts_.26_Renewals_in_Date_Range

Weekly Checked Out by Branch

Moved to SQL_Reports_Circulation#Weekly_Checked_Out_by_Branch

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

Not Circulating Items (Date Specific)

  • Developer: Bev Church, Joe Tholen, edited by Nick Clemens, ByWater Solutions (to remove NOT IN construction)
  • Module: Circulation
  • Purpose: List items not circulated in specific date range, by shelf location (weeding tool)
  • Status: Completed


SELECT barcode, homebranch AS 'branch', itemcallnumber, title 
FROM items 
LEFT JOIN biblio USING (biblionumber)
LEFT JOIN issues USING (itemnumber)
LEFT JOIN old_issues USING (itemnumber)
WHERE issues.date_due IS NULL AND (old_issues.issuedate IS NULL OR old_issues.issuedate NOT BETWEEN <<Not checked out BETWEEN start date|date>> AND <<End date|date>>) AND homebranch = <<Home branch again|branches>> AND location = <<Shelving location again|LOC>> 
ORDER BY itemcallnumber, barcode

Non Circulating Items

Moved to SQL_Reports_Circulation#Non_Circulating_Items

Non Circulating Items in X Years

  • Developer: Marion J. Makarewicz and Nicole C. Engard, ByWater Solutions, revised by Nick Clemens, 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 biblio b USING (biblionumber)
LEFT JOIN issues USING (itemnumber)
WHERE issues.issuedate IS NULL AND i.datelastborrowed < DATE_SUB(CURDATE(), INTERVAL <<Number of years>> YEAR)

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

Patrons w/ Books Due Tomorrow

Moved to SQL_Reports_Circulation#Patrons_w.2F_Books_Due_Tomorrow

Patrons w/ Items Issued Today

Moved to SQL_Reports_Circulation#Patrons_w.2F_Items_Issued_Today

Count of Transfers by Other Branches

Moved to SQL_Reports_Circulation#Count_of_Transfers_by_Other_Branches

List of Transfers to Other Branches

Moved to SQL_Reports_Circulation#List_of_Transfers_to_Other_Branches

Transfers as Interlibrary Loans

Moved to SQL_Reports_Circulation#Transfers_as_Interlibrary_Loans

Transfers as Interlibrary Borrows

Moved to SQL_Reports_Circulation#Transfers_as_Interlibrary_Borrows

Materials Checked out to Other Libraries

Moved to SQL_Reports_Circulation#Materials_Checked_out_to_Other_Libraries

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

Moved to SQL_Reports_Circulation#List_that_totals_the_circulation_of_each_Dewey_section.2C_F.2C_and_periodicals.2C_by_month

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

Moved to SQL_Reports_Circulation#List_that_totals_the_circulation_of_each_Dewey_section.2C_F.2C_and_periodicals.2C_by_day

Overdue materials

Moved to SQL_Reports_Circulation#Overdue_materials

Long Overdues

Moved to SQL_Reports_Circulation#Long_Overdues

Count of Circ by Alpha Call Number Prefix

Moved to SQL_Reports_Circulation#Count_of_Circ_by_Alpha_Call_Number_Prefix

Circ by Literary Form

Moved to SQL_Reports_Circulation#Circ_by_Literary_Form

Top 10 Circulating Books

Moved to SQL_Reports_Circulation#Top_10_Circulating_Books

Low Circulating Items

Moved to SQL_Reports_Circulation#Low_Circulating_Items

Overdues at a Specific Branch

Moved to SQL_Reports_Circulation#Overdues_at_a_Specific_Branch

Items with no Circs in a specific timeframe

Moved to SQL_Reports_Circulation#Items_with_no_Circs_in_a_specific_timeframe

Checkout by Shelving Location

Moved to SQL_Reports_Circulation#Checkout_by_Shelving_Location

Checkins by Shelving Location

SQL_Reports_Circulation#Checkins_by_Shelving_Location

Renewals by Shelving Location

Moved to SQL_Reports_Circulation#Checkout_by_Shelving_Location

Local Use by Shelving Location

Moved to SQL_Reports_Circulation#Local_Use_by_Shelving_Location

Circ Transaction Counts

Moved to SQL_Reports_Circulation#Circ_Transaction_Counts

Checkouts & Renewal Counts by Call Number

Moved to SQL_Reports_Circulation#Checkouts_.26_Renewal_Counts_by_Call_Number

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

Moved to SQL_Reports_Circulation#Detailed_report_of_long-overdues_charged-off_in_the_last_week

Average Checkouts

Moved to SQL_Reports_Circulation#Average_Checkouts

Self Check Circ Stats

Moved to SQL_Reports_Circulation#Self_Check_Circ_Stats

Old circulation issues since the beginning

Moved to SQL_Reports_Circulation#Old_circulation_issues_since_the_beginning

List of total check-out books (which are not returned, sort datewise and branchwise)

Moved to SQL_Reports_Circulation#List_of_total_check-out_books_.28which_are_not_returned.2C_sort_datewise_and_branchwise.29

List of total Holds placed at a specific branch during a specified period

Moved to SQL_Reports_Circulation#List_of_total_Holds_placed_at_a_specific_branch_during_a_specified_period

Issues placed at a specific branch during a specified period

Moved to SQL_Reports_Circulation#List_of_items_checked_out_in_a_date_range

Percentage of circs by collection code

Moved to SQL_Reports_Circulation#Percentage_of_circs_by_collection_code

Borrower count by age group and each groups circulation count for a duration, for those cardholders which have circulated during the given duration

Moved to SQL_Reports_Circulation#Borrower_count_by_age_group_and_each_groups_circulation_count_for_a_duration.2C_for_those_cardholders_which_have_circulated_during_the_given_duration

Issues Details for a Selected Branch for the Week To Date

Moved to SQL_Reports_Circulation#Issues_Details_for_a_Selected_Branch_for_the_Week_To_Date

List of items checked out in a date range

  • Developer: Nicole Engard and Christopher Brannon
  • Module: Circulation
  • Purpose: List of items checked out in a date range
  • Status: Complete
SELECT b.title, b.author, i.itemcallnumber, i.barcode, c.issuedate
FROM (SELECT iss.issuedate, iss.itemnumber FROM issues iss, 
(SELECT @StartDate:=<<Between (yyyy-mm-dd)|date>>,@EndDate:=<<and (yyyy-mm-dd)|date>>) AS var 
WHERE date(iss.issuedate) BETWEEN @StartDate AND @EndDate UNION ALL 
SELECT oi.issuedate, oi.itemnumber FROM old_issues oi WHERE date(oi.issuedate) 
BETWEEN @StartDate AND @EndDate) AS c
LEFT JOIN items i USING (itemnumber)
LEFT JOIN biblio b USING (biblionumber)
WHERE i.homebranch=<<Item belongs TO|branches>>

Circ in a date range by item type

Moved to SQL_Reports_Circulation#Circ_in_a_date_range_by_item_type

Circ in a date range by shelving location

Moved to SQL_Reports_Circulation#Circ_in_a_date_range_by_shelving_location

Monthly circ in a date range

Moved to SQL_Reports_Circulation#Monthly_circ_in_a_date_range

Items filtered by branch, collection code and last checkout date

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Circulation
  • Purpose: Based on the built in 'Home › Reports › Items with no checkouts'. Items last checked out before a given date, filtered by Collection Code and Item home branch.
  • Status: Complete
SELECT *
FROM (
    SELECT
    @homebranch := <<Home branch|branches>>  COLLATE utf8_unicode_ci   AS barcode,
    @ccode  := <<Collection Code|CCODE>> COLLATE utf8_unicode_ci  AS branch,
    @lastcheckout  := <<Last checked out before|date>> COLLATE utf8_unicode_ci  AS itemcallnumber,
    0  AS title,
    0  AS biblionumber,  
    0  AS author
    ) AS `set variables`
WHERE 0 = 1
UNION
SELECT
    items.barcode        AS barcode,
    items.homebranch     AS branch,
    items.itemcallnumber AS itemcallnumber,
    biblio.title         AS title,
    biblio.biblionumber  AS biblionumber,
    biblio.author        AS author
FROM
    items
    LEFT JOIN biblio     USING (biblionumber)
    LEFT JOIN (
        SELECT
            itemnumber,
            max( issuedate ) AS issuedate
        FROM
            ( SELECT * FROM issues
                UNION
              SELECT * FROM old_issues ) AS all_issues
        GROUP BY itemnumber
    ) AS last_checkout USING (itemnumber)
WHERE       
        items.homebranch = @homebranch
    AND items.ccode = @ccode
    AND date( last_checkout.issuedate ) < @lastcheckout

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

Course Reserves Reports

All course reserve items

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Course Reserves
  • Purpose: All titles on reserve


SELECT b.title, i.itype, t.itype AS 'course item type', i.ccode, t.ccode AS 'course ccode', 
i.itemcallnumber, i.barcode, i.itemnotes, c.course_name 
FROM course_items t 
LEFT JOIN  items i USING (itemnumber) 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN course_reserves r USING (ci_id) 
LEFT JOIN courses c USING (course_id)

Catalog/Bibliographic Reports

Find Approved User Tags for Adding to Bib Record

  • Developer: Rob Hilliker, Edsel Ford Memorial Library, and Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: Identify approved tags that haven't already been added to a local index term field (690_4$a)
  • Status: Complete
SELECT
    CONCAT('<a target="_blank" href="/cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=',biblionumber,'&frameworkcode=&op=#tab6XX">Edit Bib</a>') AS 'Bib Record', 
    GROUP_CONCAT(term) AS 'User Tag', 
    ExtractValue(marcxml,'//datafield[@tag="690" AND @ind2="4"]/subfield[@code="a"]') AS 'Indexed Tags'
FROM tags_all t
    LEFT JOIN biblioitems b USING (biblionumber)
    LEFT JOIN tags_approval ta USING (term)
WHERE 
    approved='1' 
    AND ExtractValue(marcxml,'//datafield[@tag="690" AND @ind2="4"]/subfield[@code="a"]') NOT LIKE CONCAT('%',term,'%')
GROUP BY biblionumber

Count of collection by Dewey 10s (tens) optional branch/itemtype limit

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: Counts number of items in a dewey 10s group, will ignore 1 prefix followed by space
  • Status: Complete
SELECT
    CONCAT(IF(
        itemcallnumber REGEXP '^[^0-9]+[[:space:]][0-9]{3}[^0-9]+.*', 
        LEFT(SUBSTR(itemcallnumber,LOCATE(' ',itemcallnumber)+1),2) ,
        LEFT(itemcallnumber,2)
    ),'0') AS Dewey10,
    COUNT(itemnumber)
FROM items
WHERE
    itemcallnumber REGEXP'^[0-9]{3}[^0-9]+.*|^[0-9]{3}$|^[^0-9]+[[:space:]][0-9]{3}[^0-9]+.*|^[0-9]{3}$'
    AND IF(<<Limit BY homebranch|YES_NO>>,homebranch=<<Branch|branches>>,1)
    AND IF(<<Limit BY itemtype|YES_NO>>,itype=<<Itemtype|itemtypes>>,1)
GROUP BY Dewey10

Largest Records

  • Developer: Kyle M Hall, ByWater Solutions
  • Module: Catalog
  • Purpose: Helps identify records that are too large for Zebra to handle
  • Status: Complete
SELECT CONCAT("<a href='/cgi-bin/koha/catalogue/detail.pl?biblionumber=", biblionumber, "'>", title, "</a>" ) AS Record, 
       Length(marcxml)   AS "MARC XML Size", 
       Count(itemnumber) AS Items 
FROM   biblioitems 
       LEFT JOIN biblio USING ( biblionumber ) 
       LEFT JOIN items USING ( biblionumber ) 
GROUP  BY biblionumber 
ORDER  BY Length(marcxml) DESC, 
          Count(itemnumber) DESC 
LIMIT  20

Mismatched Callnumbers

  • Developer: Abdullrahman Hegazy, Hamada
  • Module: Catalog
  • Purpose: Cataloging quality control, it shows the mismatched items callnumber in the same record.
  • Status: Complete
SELECT concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',item1.biblionumber,'>',item1.biblionumber,'</a>') AS record, item1.itemcallnumber,item2.itemcallnumber FROM `items` AS item1 ,`items` AS item2
WHERE item1.biblionumber=item2.biblionumber AND item1.itemcallnumber<>item2.itemcallnumber
GROUP BY item1.biblionumber

Invalid barcode length

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Cataloging quality control "You have to enter the length used in your library"
  • Status: Complete
SELECT items.itemcallnumber,concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') AS Title,items.biblionumber,items.barcode,CHAR_LENGTH(REPLACE(items.barcode, ' ', '')) AS Length
FROM items
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE  CHAR_LENGTH(REPLACE(barcode, ' ', '')) !=<<Length>>
OR barcode IS NULL
ORDER BY items.barcode,items.itemcallnumber,CHAR_LENGTH(REPLACE(barcode, ' ', ''))

Records with out classification number

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Cataloging quality control
  • Status: Complete
SELECT concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.biblionumber,'</a>') AS biblionumber,concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') AS Title, ExtractValue(marcxml,'//datafield[@tag="082"]/subfield[@code="a"]') AS ClassificationNumber
FROM biblioitems, biblio
WHERE biblio.biblionumber=biblioitems.biblionumber
AND (ExtractValue(marcxml,'//datafield[@tag="082"]/subfield[@code="a"]') = '' OR ExtractValue(marcxml,'//datafield[@tag="082"]/subfield[@code="a"]') IS NULL)

Items with out Callnumber

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Cataloging quality control
  • Status: Complete
SELECT concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') AS Title,Barcode, itemcallnumber
FROM items,biblioitems,biblio
WHERE items.biblionumber=biblioitems.biblionumber
AND items.biblionumber=biblio.biblionumber
AND (items.itemcallnumber IS NULL OR items.itemcallnumber = '')

Records with out ISBN

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Cataloging quality control
  • Status: Complete
SELECT itemcallnumber, isbn, concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') AS Title
FROM biblioitems,biblio,items
WHERE biblio.biblionumber=biblioitems.biblionumber
AND items.biblionumber=biblio.biblionumber
AND (isbn IS NULL OR isbn ='')
GROUP BY biblio.biblionumber

Barcode Search Report

  • Developer: Ata ur Rehman (ata.rehman@gmail.com)
  • Module: Catalog
  • Purpose: Barcode search report. To verify if a record available against provided barcode. Barcode can be searched with wild cards '%' or '_'
  • Status: Complete
SELECT  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers, items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate 
FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.homebranch =<<Branch|branches>> AND items.barcode LIKE <<Enter Barcode>>
ORDER BY LPAD(items.barcode,30,' ') ASC

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 CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers, 
       items.barcode, items.dateaccessioned, items.itemcallnumber, biblioitems.isbn, biblio.author, biblio.title, biblioitems.pages, 
       biblioitems.publishercode, biblioitems.place, biblio.copyrightdate 
FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.homebranch =<<Branch|branches>> 
ORDER BY LPAD(items.barcode,40,' ') 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 < <<Acquired before (yyyy-mm-dd)|date>>  
  GROUP BY i.homebranch,i.itype,i.location 
  ORDER BY i.homebranch,i.itype,i.location ASC

Count of items by branch, then by item type

  • Developer: Pablo Bianchi
  • Module: Catalog
  • Status: Complete
SELECT homebranch, itype AS  'Item Type', COUNT( barcode ) AS Count
FROM items
WHERE barcode <>  '' AND barcode IS NOT NULL 
GROUP BY homebranch, itype
ORDER BY homebranch, itype ASC

Number of items on each collection (952$8)

  • Developer: Pablo Bianchi
  • Module: Catalog
  • Status: Complete
SELECT
	i.ccode AS  "Collection code",
	av.lib AS  "Collection name",
	COUNT( i.barcode ) AS  "Count"
FROM items i
LEFT JOIN authorised_values av ON ( av.authorised_value = i.ccode ) 
	AND av.category =  'CCODE'
WHERE i.ccode IS NOT NULL
GROUP BY i.ccode

Items not for loan

  • Developer: Pablo Bianchi
  • Module: Catalog
  • Status: Complete
SELECT
	CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber, '\">', b.title, '</a>' ) AS Title,
	ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",
	ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="n"]') AS "Part name",
	ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="p"]') AS "Part",
	b.author AS 'Author',
	b.copyrightdate AS 'Year',
	i.barcode AS Barcode, 
	i.itemcallnumber AS 'Callnumber', 
	i.itype AS 'Item Type'
FROM biblio b
LEFT JOIN items i USING ( biblionumber ) 
LEFT JOIN biblioitems bi USING ( biblionumber ) 
WHERE i.notforloan = '1'
ORDER BY b.title

Count of items on each OAI set

  • Developer: Pablo Bianchi
  • Module: Catalog
  • Status: Complete
SELECT
	os.name AS "Set",
	COUNT(osb.biblionumber) AS Count
FROM oai_sets_biblios osb
LEFT JOIN oai_sets os ON (os.id=osb.set_id) 
#LEFT JOIN biblio b ON (b.biblionumber=osb.biblionumber)
ORDER BY os.name ASC

Total Collection Size by Date

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Total collection size by item type and branch by a specific date (for example first of the month)
  • Status: Complete
SELECT COALESCE(homebranch,'*GRAND TOTAL*') AS homebranch, 
       IFNULL(itype, "") AS itype, count(itype) AS count 
FROM items 
WHERE dateaccessioned < <<Added before (yyyy-mm-dd)|date>> 
GROUP BY homebranch, itype 
WITH rollup

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=<<Home branch|branches>>

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=<<Home branch|branches>>

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 biblio
  JOIN items USING(biblionumber)
  WHERE (items.barcode IS NULL OR items.barcode = '')

Null Location

  • Developer: Georgia Katsarou
  • Module: Catalog
  • Purpose: Null Location in Item
  • Status: Complete
SELECT items.biblionumber,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate, items.barcode, items.itemnumber FROM biblio 
JOIN items USING (biblionumber) 
WHERE (items.location IS NULL OR items.location = '')

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=<<Home branch|branches>> AND items.ccode=<<Collection|CCODE>> 
  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=<<Home branch|branches>> AND items.itype=<<Item type|itemtypes>>) 
        OR (items.homebranch=<<Second home branch|branches>> AND items.itype=<<Second item type|itemtypes>>) 
  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=<<Home branch|branches>> AND items.itemcallnumber LIKE concat(<<Call number like>>, '%')
  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=<<Home branch|branches>> 
  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=<<Home branch|branches>>

New Bib Records between dates

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List new bibs in specific time frame
  • Status: Complete
SELECT monthname(datecreated) AS month, year(datecreated) AS year, count(biblionumber) AS count 
FROM biblio 
WHERE datecreated BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY year(datecreated), month(datecreated)

New Bib Records between dates (verbose)

  • Developer: Pablo Bianchi
  • Module: Catalog
  • Purpose: List new bibs in specific time frame but giving a lot of information. Remove AND al.action LIKE 'ADD' to have biblio log between dates.
  • Status: Complete
SELECT
	al.user AS Borrowernumber,
	p.cardnumber AS 'CardNumber',
	p.userid AS 'Username',
	concat(p.surname, ', ', p.firstname) AS "Surname, Name",
	al.action_id,
	al.timestamp,
	al.action,
	al.object AS "Biblionumber",
	CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber, '\">', b.title, '</a>' ) AS "Title",
	ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",
	ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="n"]') AS "Part name",
	ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="p"]') AS "Part",
	b.author AS 'Author',
	b.copyrightdate AS 'Year',
	al.info
FROM action_logs al
LEFT JOIN borrowers p ON  (al.user = p.borrowernumber)
LEFT JOIN biblio b ON (al.object = b.biblionumber)
LEFT JOIN biblioitems bi ON (al.object = bi.biblionumber)
WHERE al.module='CATALOGUING'
	AND al.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
	AND al.info LIKE 'biblio%'
	AND al.action LIKE 'ADD'
ORDER BY al.action_id DESC

Average Age by Collection Code

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: This report averages the publication year of titles in your collection to get an age report
  • Status: Complete
SELECT round(avg(b.copyrightdate)) AS 'average year' 
FROM biblio b
LEFT JOIN items i 
USING (biblionumber) 
WHERE b.copyrightdate IS NOT NULL AND i.ccode = <<Collection|CCODE>>

Bibs Marked On Order

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List all the bib records that have been marked as on order
  • Status: Complete
SELECT b.title, b.author, i.barcode, 
       i.itemcallnumber, b.copyrightdate 
FROM biblio b
LEFT JOIN items i USING (biblionumber)
WHERE i.notforloan = '-1' 
ORDER BY b.title

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 <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND items.homebranch=<<Home branch|branches>> 
  ORDER BY items.itemcallnumber ASC

Another new items report

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List new items between specific dates
  • Status: Complete
SELECT monthname(timestamp) AS month, year(timestamp) AS year, count(itemnumber) AS count 
FROM items 
WHERE timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY year(timestamp), month(timestamp)

List of new items added in a date range

  • Developer: Furrukh Hussian Zai
  • Module: Catalog
  • Purpose: List of new items added in a date range
  • Status: Complete
SELECT items.dateaccessioned,items.barcode,items.itemcallnumber,biblio.author,biblio.title,biblioitems.publishercode FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN
<<Between Date (2017-08-01)>> AND <<and (2017-08-31)>>
ORDER BY items.barcode DESC

List of new items added in a date & subject range

  • Developer: Furrukh Hussian Zai
  • Module: Catalog
  • Purpose: List of new items added in a date & subject range
  • Status: Complete
SELECT * FROM(SELECT items.dateaccessioned,items.barcode,items.itemcallnumber,biblio.title,biblio.author,biblioitems.publishercode,(SELECT ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')) AS Subject
FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)  
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE  items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) AS t
WHERE  Subject LIKE concat('%',<<Subject>>,'%')
ORDER BY dateaccessioned DESC

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=<<Home branch|branches>> AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= items.dateaccessioned 
  ORDER BY biblio.title ASC

Count of all items: categorized by DDC

  • Developer: Abdullrahman Hegazy
  • Module: Catalog
  • Purpose: Count of all items categorized by 1st grade of Dewey Decimal Classes
  • Status: Complete
SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "Call Number Code",COUNT(itemnumber) AS "Books Count"
FROM biblio,items
WHERE biblio.biblionumber=items.biblionumber
AND SUBSTRING(itemcallnumber,1,1) REGEXP '^[0-9].*'
AND items.itemlost = '0' 
AND items.damaged ='0'
GROUP BY SUBSTRING(itemcallnumber,1,1)
ORDER BY SUBSTRING(itemcallnumber,1,1) 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(biblio.title) AS Count FROM biblio

Count of all Bibs and Items per Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A count of all unique bibs and total items held at each branch
  • Status: Complete
SELECT homebranch, count(DISTINCT biblionumber) AS bibs, 
       count(itemnumber) AS items 
FROM items 
GROUP BY homebranch 
ORDER BY homebranch ASC


Count of all Bibs and Items by item type

  • Developer: Agnes Rivers-Moore
  • Module: Catalog
  • Purpose: A count of titles and items by item type, with item type descriptions.
  • Status: Complete
SELECT items.itype, itemtypes.description, count(DISTINCT items.biblionumber) AS bibs, 
count(items.itemnumber) AS items 
FROM items, itemtypes
WHERE items.itype=itemtypes.itemtype AND items.barcode IS NOT NULL 
GROUP BY items.itype
ORDER BY itemtypes.description

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 where last item deleted

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: All bibs without items where the last item was deleted
  • Status: Complete
SELECT b.biblionumber, b.title, b.author
FROM biblio b
LEFT JOIN items i USING (biblionumber) 
WHERE i.itemnumber IS NULL 
      AND b.biblionumber IN (SELECT biblionumber FROM deleteditems)
GROUP BY b.biblionumber

All bibs where last item deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: All bibs without items where the last item was deleted in a specific timeframe (often used for notifying OCLC of holdings changes)
  • Status: Complete
SELECT b.biblionumber, b.title, b.author
FROM biblio b
LEFT JOIN items i USING (biblionumber) 
WHERE i.biblionumber NOT IN (SELECT biblionumber FROM items) AND 
      b.biblionumber IN (SELECT biblionumber FROM deleteditems WHERE date(timestamp) = <<Deleted ON (yyyy-mm-dd)|date>>)

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= <<Item type code|itemtypes>> AND items.holdingbranch=<<Branch code|branches>> 
      AND items.itemcallnumber BETWEEN <<Call number between>> AND <<and>>
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 b.title, i.barcode, i.itemcallnumber, i.itemlost, i.damaged,
      IF(i.onloan IS NULL, '', 'checked out') AS onloan
 FROM biblio b
 LEFT JOIN items i USING (biblionumber)
 WHERE datelastseen < <<Last seen before (yyyy-mm-dd)|date>> 
                 AND i.homebranch=<<Home branch|branches>>
 ORDER BY datelastseen DESC, i.itemcallnumber ASC

Inventory Report by Location

  • Developer: Jason O'Neil. Original by Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Find all items that haven't been seen since a specific date, filtered by shelving location, and with borrower details for items that are currently checked out.
  • Status: Complete
SELECT b.title, i.barcode, i.itemcallnumber, datelastseen, i.location,
      IF(i.onloan IS NULL, '', 'checked out') AS onloan,
      IF(p.cardnumber IS NULL, '', p.cardnumber) AS cardnumber,
      IF(p.firstname IS NULL, '', p.firstname) AS firstname,
      IF(p.surname IS NULL, '', p.surname) AS surname
 FROM biblio b
 LEFT JOIN items i USING (biblionumber)
 LEFT JOIN issues c ON (i.itemnumber=c.itemnumber) 
 LEFT JOIN borrowers p ON (p.borrowernumber=c.borrowernumber) 
 WHERE datelastseen < <<Last seen before (yyyy-mm-dd)|date>> 
                 AND i.homebranch=<<Home branch|branches>>
                 AND i.location=<<Location|LOC>>
 ORDER BY onloan DESC, datelastseen DESC, i.itemcallnumber ASC

Items added by Collection

  • Developer: Katrin Fischer and Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Count of items added by collection in a specific date range
  • Status: Complete
SELECT count(ccode), ccode AS collection
FROM (
SELECT ccode, dateaccessioned FROM items
UNION ALL
SELECT ccode, dateaccessioned FROM deleteditems
)
AS itemsadded
WHERE date(dateaccessioned) BETWEEN
<<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY ccode

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

Action log entries of items damaged within the last day

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Catalog
  • Purpose: Items damaged within the last day
  • Status: Complete
SELECT
      b.title
    , b.author
    , i.itemnumber
    , i.barcode
    , i.timestamp
    , l.*
FROM items i
LEFT JOIN biblio b USING ( biblionumber )
LEFT JOIN action_logs l ON (l.timestamp >= timestamp( SUBDATE(CURDATE(), INTERVAL 1 DAY) ) AND l.object = i.itemnumber )
WHERE
    i.damaged = 1
    AND DATE(i.timestamp) >= SUBDATE(CURDATE(), INTERVAL 1 DAY)
    AND l.info LIKE '%damaged%'
ORDER BY i.timestamp 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

Items Added in Date Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Items added in a time period (will ask for date range twice)
  • Status: Complete
SELECT sum(count) AS added
FROM (
SELECT count(*) AS count FROM items WHERE date(dateaccessioned) BETWEEN <<Added
BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
UNION ALL
SELECT count(*) AS count FROM deleteditems WHERE date(dateaccessioned) BETWEEN
<<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
) AS items

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

Items Deleted in Date Range at Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Items deleted at a branch in a time period
  • Status: Complete
SELECT count(*) AS "Items Deleted"
FROM deleteditems
WHERE timestamp BETWEEN <<Deleted BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND homebranch=<<Owning branch|branches>>

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 (w/ details)

  • Developer: Jane Wagner, PTFS
  • Module: Catalog
  • Purpose: Withdrawn Items
  • Status: Complete
SELECT biblio.title,biblio.author,items.itemcallnumber,items.barcode,items.datelastborrowed, items.withdrawn 
FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
WHERE items.withdrawn != 0 
ORDER BY biblio.title ASC

Withdrawn Items 3.12- (barcodes only)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Barcodes of items marked as withdrawn (best used for batch deleting)
  • Status: Complete
  • Version: 3.12-
SELECT barcode
FROM items
WHERE withdrawn != 0 
ORDER BY barcode ASC

Withdrawn Items 3.14+ (barcodes only)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Barcodes of items marked as withdrawn (best used for batch deleting)
  • Status: Complete
  • Version: 3.14+
SELECT barcode
FROM items
WHERE withdrawn != 0 
ORDER BY barcode 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';

Bibs with 856s

  • Developer: Myka Kennedy Stephens, Lancaster Theological Seminary
  • Module: Catalog
  • Purpose: list of all 856s, including all subfields, for each biblio that has at least one 856
  • Status: Complete
SELECT biblionumber,
     ExtractValue(marcxml,'count(//datafield[@tag="856"])') AS count856,
     ExtractValue(marcxml,'//datafield[@tag="856"]/*') AS link 
FROM biblioitems
HAVING count856 > 0

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, ByWater Solutions
  • 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, ByWater Solutions
  • 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 Alternative

  • Developer: Nick Clemens, VOKAL
  • Module: Catalog
  • Purpose: Building from the duplicate isbn report, but normalizing to 13-digits and adding a fast link to merge the highest and lowest bibnumbers. Long and maybe a bit clunky, but very effective.
  • Status: Completed
SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title, 
       b.author, IF(LEFT(REPLACE(TRIM(i.isbn),'-',''),3)<>'978',CONCAT('978',LEFT(REPLACE(TRIM(i.isbn),'-',''),9),(MOD(10-MOD((CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),1,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),3,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),5,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),7,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),9,1),UNSIGNED INTEGER))*3+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),2,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),4,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),6,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),8,1),UNSIGNED INTEGER)+38,10),10))),LEFT(REPLACE(TRIM(i.isbn),'-',''),13))AS NormISBN, CONCAT('<a href=\"http://staff.kohavt.org/cgi-bin/koha/cataloguing/merge.pl?biblionumber=',MIN(b.biblionumber),'&biblionumber=',MAX(b.biblionumber),'\">Merge</a>') AS FastMerge, GROUP_CONCAT(DISTINCT b.typelist SEPARATOR '::') AS TypeDiscrepCheck
FROM (SELECT b2.biblionumber, b2.title,b2.author, COUNT(i2.barcode) AS itemcount, GROUP_CONCAT(DISTINCT i2.itype) AS typelist FROM biblio b2 JOIN items i2 ON i2.biblionumber=b2.biblionumber GROUP BY b2.biblionumber HAVING itemcount>0) b 
LEFT JOIN biblioitems i ON (i.biblionumber=b.biblionumber)
WHERE i.isbn IS NOT NULL AND i.isbn<>''
GROUP BY CONCAT(substr(b.title,1,9),"/",NormISBN) 
HAVING COUNT(CONCAT(substr(b.title,1,9),"/",NormISBN))>1
ORDER BY COUNT(b.biblionumber) ASC


Duplicate Titles (using title and ISBN)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Show records with duplicate titles (using the first 9 characters) and duplicate ISBNs
  • Status: Completed
SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title, 
       b.author, GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns 
FROM biblio b 
LEFT JOIN biblioitems i 
ON (i.biblionumber=b.biblionumber)
GROUP BY CONCAT(substr(b.title,0,9),"/",i.isbn) 
HAVING COUNT(CONCAT(substr(b.title,0,9),"/",i.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

Duplicate ISBNs in Time Frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Show records with duplicate ISBNs added within a specific time frame.
  • Status: Completed
SELECT GROUP_CONCAT(CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') 
       SEPARATOR ', ') AS biblionumbers, b.title, b.author 
FROM biblio b
LEFT JOIN biblioitems i USING (biblionumber)
WHERE b.datecreated BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY substring_index(i.isbn, ' ', 1) 
HAVING COUNT(substring_index(i.isbn, ' ', 1))>1

Duplicate bibs using the 001

  • Developer: Katrin Fischer and Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Show records with duplicate 001 fields
  • Status: Completed
SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, 
       ExtractValue(marcxml,'//controlfield[@tag="001"]') AS id 
FROM biblioitems  
GROUP BY id  
HAVING count(id) > 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 CONCAT( '%', <<Subject>>, '%' )

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 RDA specific fields

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A report to find bibs that have RDA fields (336-339)
  • 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="336"]/subfield[@code>="a"]') AS rda1, 
ExtractValue(marcxml,'//datafield[@tag="337"]/subfield[@code>="a"]') AS rda2, 
ExtractValue(marcxml,'//datafield[@tag="338"]/subfield[@code>="a"]') AS rda3, 
ExtractValue(marcxml,'//datafield[@tag="339"]/subfield[@code>="a"]') AS rda4 
FROM biblioitems) AS rda 
WHERE rda1 != "" 
OR rda2 != "" 
OR rda3 != "" 
OR rda4 != ""

Identify records with mismatched 008 vs Copyright/Publication date

  • Developer: Liz Rea, Catalyst IT, for New Zealand Educational Institute
  • Module: Catalog
  • Purpose: This report shows records that have a mismatch between the 008 publication date and the catalogued biblio.copyrightdate. This report can help identify records that have incorrect 008 fields so that the sorting by publication date remains consistent.
  • Status: Complete
SELECT CONCAT('<a target="new" href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>') AS Title, biblio.copyrightdate, control008.008pubdate 
FROM (SELECT biblionumber, SUBSTR(ExtractValue(biblioitems.marcxml,'//controlfield[@tag="008"]'),8,4)  AS 008pubdate FROM biblioitems) AS control008 
JOIN biblio USING(biblionumber)  
WHERE biblio.copyrightdate != control008.008pubdate 
AND control008.008pubdate != '';

Items with notes

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records with either a public or nonpublic note
  • Status: Complete
SELECT b.title, b.author, i.itemcallnumber, i.barcode, 
       i.itemnotes AS 'public note',
       ExtractValue(i.more_subfields_xml,'//datafield[@tag="999"]/subfield[@code>="x"]')
       AS 'nonpublic note' 
FROM items i 
LEFT JOIN biblio b USING (biblionumber) 
WHERE i.itemnotes IS NOT NULL 
      OR i.more_subfields_xml IS NOT NULL


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 i.itemnumber, b.title, b.author, i.itemcallnumber, 
       i.barcode, v.lib 
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value) 
WHERE i.itemlost != 0 AND v.category='LOST'

List of Items Marked Lost/Missing w/ Hold Info

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Finds all items that are marked as lost in some way and shows if they're on hold.
  • Status: Completed
SELECT i.itemnumber, i.reserves, i.issues, i.datelastseen,
i.dateaccessioned, i.ccode, b.title, b.author,
i.itemcallnumber, i.barcode, v.lib, 
IF(h.reservedate IS NULL, '', 'on hold') AS holds
FROM items i
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
WHERE i.itemlost != 0 AND v.category='LOST'

List of Items Marked Lost/Missing w/ Holds past 6 months

  • Developer: Agnes Rivers-Moore, Hanover Public Library
  • Module: Catalog
  • Purpose: Finds all items that are marked as lost/missing, since 6 months ago, with title link and shows if they're on hold. Uses the new lost_on date.
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',title,'</a>') AS Title, i.location, i.itemcallnumber,
i.barcode, i.itemlost_on, v.lib, i.issues, i.datelastseen, i.dateaccessioned,
IF(h.reservedate IS NULL, '', 'on hold') AS holds
FROM items i
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
WHERE i.itemlost != 0 AND v.category='LOST' AND i.itemlost_on > DATE_SUB(now(),INTERVAL 6 MONTH)
ORDER BY i.itemlost_on DESC

List of Items Marked Lost/Missing, Choose Lost Status

  • Developer: Barton Chittenden
  • Module: Circulation
  • Purpose: Finds all items that are marked as lost/missing, choose lost status.
  • Status: Completed
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', title, '</a>' ) AS title,
    itemnumber,
    barcode
FROM
    items
    INNER JOIN biblio USING (biblionumber)
WHERE
    items.itemlost = <<Lost STATUS|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


Title/Subtitle List

  • Developer: Katrin Fischer
  • Module: Catalog
  • Purpose: List of full titles (title and subtitle) with call numbers
  • Status: Completed
SELECT concat(b.title, ' ', ExtractValue((
    SELECT marcxml 
    FROM biblioitems b2
    WHERE b.biblionumber = b2.biblionumber),
      '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, 
    b.author, i.itemcallnumber FROM biblio b LEFT JOIN items i ON (i.biblionumber=b.biblionumber)

Records Cataloged with a Specific Framework

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides a list of titles cataloged with a specific framework, handy for finding items added using Fast Add.
  • Status: Completed
SELECT title, author 
FROM biblio 
WHERE frameworkcode=<<Enter Framework Code>>

Withdrawn Titles List to Send to OCLC

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides title, author and OCLC number of withdrawn titles for sending to OCLC to update your holdings in a batch.
  • Status: Completed
SELECT b.title, b.author, ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') AS 'OCLC Number' 
FROM biblio b 
LEFT JOIN items i USING (biblionumber) 
LEFT JOIN biblioitems m USING (biblionumber)
WHERE i.withdrawn > 0

Deleted Titles List to Send to OCLC

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Provides title, author, isbn and OCLC number of deleted titles at a branch in a specific time period for sending to OCLC to update your holdings in a batch.
  • Status: Completed
SELECT b.title, b.author, m.isbn, 
       ExtractValue(m.marcxml, '//controlfield[@tag="001"]') AS 'OCLC Number 001'
FROM biblio b
LEFT JOIN deleteditems i USING (biblionumber)
LEFT JOIN biblioitems m USING (biblionumber)
WHERE b.biblionumber NOT IN (SELECT biblionumber FROM items) 
      AND i.homebranch=<<Branch|branches>> AND 
      i.timestamp BETWEEN <<Deleted BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>>

Collection Evaluation Report

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Collection Evaluation report asks for branch, shelving location, data acquired range and date last borrowed range and returns titles
  • Status: Completed
SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber 
FROM biblio b 
LEFT JOIN items i 
USING (biblionumber) 
WHERE i.homebranch=<<Branch|branches>> AND i.location=<<Shelving location|LOC>> 
          AND i.dateaccessioned BETWEEN <<Date acquired BETWEEN (yyyy-mm-dd)|date>> AND 
         <<and (yyyy-mm-dd)|date>> AND i.datelastborrowed BETWEEN 
         <<Date last checked out BETWEEN (yyyy-mm-dd)|date>> AND 
         <<and (yyyy-mm-dd)|date>>
ORDER BY i.itemcallnumber ASC

Collection Evaluation Report 2

  • Developer: Nicole C. Engard and Ian Walls, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows entire collection with publication info pulled from the 008 (Tip: would be wise to add a filter of some sort to this)
  • Status: Completed
SELECT b.title, b.author, i.dateaccessioned, i.location, i.itemcallnumber,
i.itype, i.datelastborrowed, i.issues, substring(ExtractValue((
    SELECT marcxml
    FROM biblioitems b2
    WHERE b.biblionumber = b2.biblionumber), 
'//controlfield[@tag="008"]'),8,4) AS 'pub date'
FROM biblio b LEFT JOIN items i USING (biblionumber)

Titles by General Materials Designation (MARC 245$h)

  • Developer: Ian Walls, ByWater Solutions
  • Module: Catalog
  • Purpose: Shows each distinct GMD value in the catalog, with a count of titles for that value. Good for profiling materials, and spotting minor spelling errors
  • Status: Completed
SELECT ExtractValue(marcxml, '//datafield[@tag="245"]/subfield[@code="h"]') AS GMD, 
       count(*) AS COUNT
       FROM biblioitems 
       GROUP BY GMD ORDER BY COUNT DESC


Count of items added by cataloger

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Asks for librarian's borrower number and shows them with a count of items they've added. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT count(timestamp) AS 'items added' 
FROM action_logs 
WHERE module='CATALOGUING' AND user=<<Borrower number>> 
      AND info='item' AND action='ADD'


Count of items added by cataloger

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Counts the number of cataloging actions each cataloger performed in a date range. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT concat(p.firstname, ' ', p.surname) AS staff, concat(a.action, ' ', a.info) AS action, count(a.timestamp) AS count 
FROM action_logs a 
LEFT JOIN borrowers p ON (a.user=p.borrowernumber)
WHERE a.module='CATALOGUING' AND a.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND a.info IN ('item','biblio')
GROUP BY p.borrowernumber, concat(a.action, ' ', a.info)

Count of bibs modified by cataloger

  • Developer: Ramiro Uviña
  • Module: Catalog
  • Purpose: Asks for date range and shows you them with a count of bibs they've modified. [Requires CataloguingLog to be on]
  • Status: Completed
SELECT user,count(user) AS 'bibs modified'
FROM action_logs WHERE module='CATALOGUING' AND info LIKE 'BEFORE%' AND action='MODIFY'
AND (timestamp BETWEEN <<Modified BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>)
GROUP BY user

Titles on a particular branch and shelving location

  • Developer: Nicole C. Engard, ByWater Solutions (Posted by Rachel)
  • Module: Catalog
  • Purpose: Creates a list of titles (245a and 245b), authors, and call numbers along with home-branch and library location.
  • Status: Completed
SELECT concat(b.title, ' ', 
       ExtractValue(m.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, i.itemcallnumber 
FROM biblio b 
LEFT JOIN items i USING (biblionumber)
LEFT JOIN biblioitems m USING (biblionumber)
WHERE i.homebranch=<<homebranch|branches>> AND i.location=<<Shelving Location|LOC>>

Language Material Bibs

  • Developer: Chris Cormack, Catalyst and Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List of biblionumbers where the leader says 'language material' and has a specific item type.
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
       AS biblionumber 
FROM biblioitems,
  (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,1) 
   AS leader6 FROM biblioitems) 
AS leaders 
WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND leaders.leader6 = 'a' 
      AND itemtype = <<Item Type|itemtypes>>


Materials based on biblio item type

  • Developer: Nicole C. Engard, ByWater Solutions and Melia Meggs, ByWater Solutions
  • Module: Catalog
  • Purpose: count of the collection by bibliographic item type as cataloged in the fixed fields.
  • Status: Completed
SELECT 
CASE SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,2) 
      WHEN 'am' THEN 'Book'
      WHEN 'as' THEN 'Serial'
      WHEN 'cm' THEN 'Score'
      WHEN 'em' THEN 'Map'
      WHEN 'gm' THEN 'Video recording and motion pictures'
      WHEN 'im' THEN 'Non-music sound recording'
      WHEN 'jm' THEN 'Music sound recording'
      WHEN 'mm' THEN 'Computer file'
      WHEN 'rm' THEN 'Three Dimensional item'
      WHEN 'tm' THEN 'Manuscript'
      ElSE 'unknown' END
   AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems
GROUP BY bibtype

Authors not in the Authorities

  • Developer: MJ Ray, software.coop
  • Module: Catalog
  • Purpose: List of author names found on biblio records but not authority records
  • Status: Production
SELECT DISTINCT(author) AS heading
FROM biblio
WHERE author NOT IN
   (SELECT ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code="a"]')
      AS heading
    FROM auth_header
    WHERE authtypecode='PERSO_NAME')
ORDER BY heading


Terms not in the Authorities

  • Developer: Adapted from a report by Bernardo Gonzalez Kriegel
  • Module: Catalog
  • Purpose: List of terms found on biblio records in 6XX fields that are not in Authorities, with associated biblio numbers.
  • Status: Completed
SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code="a"]') AS heading
FROM biblioitems
WHERE length(ExtractValue(marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(marcxml, '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
ORDER BY heading


Classes of MARC fields missing authorities

  • Developer: Barton Chittenden
  • Module: Catalog
  • Purpose: Bibs with personal name missing authority link
  • Status: Completed

This report is easily extended to

  • Bibs with corporate name missing authority link ( s/00/10/g )
  • Bibs with meeting name missing authority link ( s/00/11/g )

And with only slightly more tweaking,

  • Bibs with uniform title missing authority link ( tags 130, 630, 730 or 830 ),
  • Bibs with "Series Statement/Added Entry-Title" missing authority link (tag 440 )
  • Bibs with Subject or Genre missing authority link ( tags 650, 651, or 655 )
SELECT
    CONCAT(
        '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
        biblionumber,
        '\">',
        biblionumber,
        '</a>'
    ) AS biblionumber,
    CONCAT_WS( '|',
        ExtractValue( marcxml, '//datafield[@tag=100]/subfield[@code="a"]' ),
        ExtractValue( marcxml, '//datafield[@tag=400]/subfield[@code="a"]' ),
        ExtractValue( marcxml, '//datafield[@tag=600]/subfield[@code="a"]' ),
        ExtractValue( marcxml, '//datafield[@tag=800]/subfield[@code="a"]' )
    ) AS 'personal name'
FROM
    biblioitems
WHERE
    ( length(ExtractValue(marcxml, '//datafield[@tag="100"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="100"]/subfield[@code="9"]')) = 0 )
    OR ( length(ExtractValue(marcxml, '//datafield[@tag="400"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="400"]/subfield[@code="9"]')) = 0 )
    OR ( length(ExtractValue(marcxml, '//datafield[@tag="600"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="600"]/subfield[@code="9"]')) = 0 )
    OR ( length(ExtractValue(marcxml, '//datafield[@tag="800"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="800"]/subfield[@code="9"]')) = 0 )

Lost Items & Who Lost Them

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report should show all items that are marked lost and who lost them. It's not fool proof, but it's the closest I can get.
  • Status: Complete
SELECT i.itemnumber, i.ccode, b.title, b.author, i.itemcallnumber, 
       i.enumchron, i.itemnotes, i.barcode, v.lib AS 'lost', c.borrowernumber
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
LEFT JOIN ( SELECT itemnumber, borrowernumber, issuedate, timestamp, returndate
            FROM issues UNION SELECT itemnumber, borrowernumber, issuedate, timestamp,
            returndate FROM old_issues ) c 
     ON (c.itemnumber=i.itemnumber) 
LEFT JOIN statistics s ON (s.itemnumber=i.itemnumber)
WHERE i.itemlost != 0 AND v.category='LOST' AND
      date(s.datetime)=date(c.issuedate) AND s.type='issue'

Bib records that contain only lost items

  • Developer: Barton Chittenden
  • Module: Statistical (Catalog)
  • Purpose: Find
  • Status: Complete
SELECT
        CONCAT(
            '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
            bib.biblionumber, 
            '\">', 
            bib.title, 
            '</a>'
        ) AS Title,
        bib.title,
        bib.biblionumber,
        lostitems.holdingbranch,
        lostitems.barcode,
        lostitems.ccode,
        lostitems.dateaccessioned AS 'Accession date',
        lostitems.onloan AS 'Checkout date'
FROM
        biblio bib
        LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND items.itemlost = 0)
        LEFT JOIN items AS lostitems ON (bib.biblionumber = lostitems.biblionumber AND lostitems.itemlost != 0)
GROUP BY bib.biblionumber
HAVING count(items.itemnumber) = 0

Basic Item Information By Call Number Range

  • Developer: Jared Camins and Chris Nighswonger
  • Module: Catalog
  • Purpose: This report returns a set of items limited by a range of call numbers. The data included in the result set are: Call Number, Title, Author. A link is provided for easy viewing of the item details.
  • Status: Complete
SELECT CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '\">View Details</a>' ) AS 'View Details', items.itemcallnumber, biblio.title, biblio.author 
FROM items 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE items.itemcallnumber BETWEEN <<starting call number>> AND <<ending call number>>

Biblio Items without a Koha Item Type

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: Displays the biblionumber, title, and author of biblioitems that do not have an associated Koha Item Type.
  • Status: Complete
SELECT biblio.biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' ) = ""

Bib records added/deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the bibs added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging cataloging actions.
SELECT l.action, count(l.timestamp) AS 'bibs'
FROM action_logs l
LEFT JOIN borrowers p ON (p.borrowernumber=l.user)
WHERE module='CATALOGUING' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
      AND l.info!='item'
GROUP BY l.action


Item records added/deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the items added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging cataloging actions.
SELECT l.action, count(l.timestamp) AS 'items'
FROM action_logs l
LEFT JOIN borrowers p ON (p.borrowernumber=l.user)
WHERE module='CATALOGUING' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
      AND l.info='item'
GROUP BY l.action


Authorities records added/deleted in time frame

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the authorities added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging authority actions (AuthoritiesLog system preference activated).
SELECT l.action, count(l.timestamp) AS 'authorities'
FROM action_logs l
LEFT JOIN borrowers p ON (p.borrowernumber=l.user)
WHERE module='AUTHORITIES' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY l.action

Authorities records added in time frame, with headings

  • Developer: Stefano Bargioni 2017-03-09, Pontificia Università della Santa Croce
  • Module: Statistical (Catalog)
  • Purpose: This report will show the authorities added to Koha in a time period.
  • Status: Complete
SELECT authid, datecreated, authtypecode, concat(
ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), -- PERSO_NAME
ExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- CORPO_NAME
ExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- MEETI_NAME
ExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- UNIF_TITLE
ExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- CHRON_TERM
ExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- TOPIC_TERM
ExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- GEOGR_NAME
ExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  -- GENRE/FORM
) main_heading
FROM `auth_header`
WHERE datecreated BETWEEN <<(FROM yyyy-mm-dd)>> AND <<(TO yyyy-mm-dd)>>
ORDER BY datecreated, authtypecode, main_heading

All titles with 008 for Continuing Resource

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report lists all titles that are coded as continuing resources in the 008
  • Status: Complete
SELECT b.title, m.issn
FROM biblioitems m 
LEFT JOIN biblio b USING (biblionumber) 
WHERE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),22,1) IN ('d','l','m','n','p','w')

Bibs with Different Item Types

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records that have multiple item types attached.
  • Status: Complete
SELECT b.title, b.biblionumber, count(DISTINCT itype) AS types 
FROM biblio b 
LEFT JOIN items i USING (biblionumber) 
GROUP BY i.biblionumber 
HAVING count(DISTINCT itype) > 1


Author List by Branch

  • Developer: Nick Clemens, VOKAL
  • Module: Catalog
  • Purpose: A list of authors that match search criteria
  • Status: Complete
SELECT  ' ' AS Checkbox, b.title, b.author, i.itemcallnumber, i.barcode
FROM items i
JOIN biblio b USING (biblionumber)
WHERE i.homebranch=<<Branch|branches>> AND 
      b.author LIKE CONCAT(<<Author: Last Name, First Name>>,'%')
ORDER BY b.title

Biblios with like data in a subfield of a field

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: This report displays the Biblionumber, Title, and Author of all Biblios with a specific value in a subfield of a field.
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE ExtractValue(marcxml, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

Mismatches between 2 fields and 2 subfields

  • Developer: Joseph Alway
  • Module: Catalog
  • Purpose: This report displays the Biblionumber, Title, and Author of all Biblios that Do Not Match 2 sets of fields and subfields.
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 1 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 2 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
NOT LIKE ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 1(XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 2(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")

List all records with at least one subject

  • Developer: David Cook
  • Module: Catalog
  • Purpose: This report creates a list of all records with at least one subject tag. It also lists those subject tags and the biblionumber for the record.
  • Status: Complete
SELECT biblionumber,ExtractValue(marcxml,'//datafield[substring(@tag,1,1) = "6"]/@tag') AS 'Subject Tags' -- ,marcxml
FROM biblioitems
WHERE ExtractValue(marcxml,'//datafield[substring(@tag,1,1) = "6"]/@tag')
ORDER BY `Subject Tags`;


Bibs with diff item types attached

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records that have items with different item types attached.
  • Status: Complete
SELECT b.title, b.author, b.biblionumber, count(DISTINCT i.itype) AS 'item types', 
       count(i.itemnumber) AS items 
FROM biblio b 
LEFT JOIN items i USING (biblionumber) 
GROUP BY b.biblionumber 
HAVING count(DISTINCT i.itype) > 1

Bibs with Series info

  • Developer: Joy Nelson, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of bib records with series info
  • Status: Complete
SELECT i.biblionumber, i.itemnumber, i.barcode, i.itemcallnumber, i.location, i.itype, 
       b.title, b.author, i.enumchron, b.seriestitle, 
       ExtractValue(bi.marcxml,'//datafield[@tag="830"]/subfield[@code="a"]') AS Series 
FROM items i 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN biblioitems bi ON (b.biblionumber=bi.biblionumber)


Percentage of collection by collection code

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Number of items per collection code with the percentage of the total collection
  • Status: Complete
SELECT
   x.ccode AS collection,
   x.allitems AS 'items',
   (x.allitems * 100)/(
     SELECT COUNT(itemnumber) AS 'total'
     FROM items
   ) AS 'Percentage of total collection'
FROM
   (SELECT
     i.ccode,
     COUNT(i.itemnumber) AS 'allitems'
   FROM items i
   GROUP BY i.ccode) x

List records with notes by note tag

  • Developer: David Cook, Prosentient Systems
  • Module: Catalog
  • Purpose: List bibliographic records that have notes fields, and list those note fields by tag number
  • Status: Complete
SELECT biblionumber,ExtractValue(marcxml,'//datafield/@tag/text()[substring(.,1,1) = "5"]') AS 'notes'
FROM biblioitems
HAVING notes <> ''

All bibs without items

All bibs without items - Simple
  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Bib records without items
  • Status: Complete
SELECT biblionumber, title 
FROM biblio 
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)
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'
Records with item count
  • Developer: Heather Braum and Paul A at the request of Satish MV
  • Module: Catalog
  • Purpose: Records with item count
  • Status: Complete
  • Note: Revised by Matthew Charlesworth to provide correct link to Staff Interface
SELECT DISTINCT
 CONCAT('<a title="Search for all records sharing the title:',b.title,'" href="/cgi-bin/koha/catalogue/search.pl?q=ti%3A',
 REPLACE(REPLACE (b.title, ' ', '+'),'?',''),'">Search</a>') AS "Search for Title",
 b.biblionumber,
 CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'">',b.title,'</a>') AS "Item Title",
 b.author,
 t.editionstatement,
 t.publishercode,
 t.isbn,
 count(i.biblionumber) AS "Copies"
FROM biblio b LEFT JOIN biblioitems t USING(biblionumber)
LEFT JOIN items i USING(biblionumber)
GROUP BY b.biblionumber
ORDER BY Copies ASC


All 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');
All bibs without items - With link to biblio
  • Developer: Tomás Cohen
  • Module: Catalog
  • Purpose: Get biblionumber of biblio records without items
  • Status: Complete
SELECT CONCAT('<a href="http://',
     (SELECT value FROM systempreferences WHERE variable='staffClientBaseURL'),
     '/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
     biblionumber,
     '">',
     biblionumber,
     '</a>') AS 'biblionumber', title
FROM biblio
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)
All bibs without items - With link to biblio's add items screen
  • Developer: Barton Chittenden
  • Module: Catalog
  • Purpose: Quick access to add items screen for itemless bibs.
  • Status: Complete
SELECT
    CONCAT(
         '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',
         biblionumber,
         '\">', 
         title, '</a>' 
    ) AS 'Add Item to'
FROM biblio 
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)

Average age of collection by item type

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: Average age of collection by item type uses the 008 date field
  • Status: Complete
SELECT itype, avg(mid(ExtractValue(marcxml,'//controlfield[@tag="008"]'),8,4))
FROM biblioitems
LEFT JOIN items USING (biblioitemnumber)
WHERE SUBSTR(ExtractValue(biblioitems.marcxml,'//controlfield[@tag="008"]'),8,4) REGEXP '[0-9]{4}'
GROUP BY itype

Count of items in a location

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: This will count the items in a location showing locations that have 0 items in them
  • Status: Complete
SELECT v.lib AS loc, count(i.itemnumber) AS items 
FROM authorised_values v 
LEFT JOIN items i  ON (i.location=v.authorised_value) 
WHERE v.category='LOC' 
GROUP BY v.id

List of all normalized ISBNs

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: A list of all ISBNs in your system normalized using the code from Nick Clemens.
  • Status: Complete
SELECT IF(LEFT(REPLACE(TRIM(i.isbn),'-',''),3)<>'978',CONCAT('978',LEFT(REPLACE(TRIM(i.isbn),'-',''),9),(MOD(10-MOD((CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),1,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),3,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),5,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),7,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),9,1),UNSIGNED INTEGER))*3+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),2,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),4,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),6,1),UNSIGNED INTEGER)+CONVERT(SUBSTR(REPLACE(TRIM(i.isbn),'-',''),8,1),UNSIGNED INTEGER)+38,10),10))),LEFT(REPLACE(TRIM(i.isbn),'-',''),13)) AS NormISBN 
FROM biblioitems i 
WHERE i.isbn IS NOT NULL AND i.isbn != ''

Items in a location with lists

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: This will list all items in a specific location and the lists they are in (if any).
  • Status: Complete
SELECT b.title, i.barcode, i.location, group_concat(l.shelfname, ' || ') 
FROM items i 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN virtualshelfcontents c ON (b.biblionumber=c.biblionumber) 
LEFT JOIN virtualshelves l USING (shelfnumber) 
WHERE i.location=<<Location|LOC>> 
GROUP BY i.itemnumber

7 Random new titles for coverflow plugin

  • Developer: Ed Veal and Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: This will list 7 random new titles for the coverflow plugin
  • Status: Complete
SELECT b.biblionumber, m.isbn, b.title
  FROM items i
  LEFT JOIN biblioitems m USING (biblioitemnumber)
  LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
  WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ''
  ORDER BY rand()
  LIMIT 7

Items by Like Call Number, Branch and Item Type

  • Developer: Rebecca Crago, Systems and Teaching Librarian, Mercyhurst University
  • Module: Catalog
  • Purpose: Search items by like call number, by item type and branch location.
  • Status: Complete
SELECT itemcallnumber, biblio.title
FROM items
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
LEFT JOIN biblioitems ON items.biblionumber=biblioitems.biblioitemnumber
WHERE items.homebranch=<<Home branch|branches>>
AND biblioitems.itemtype = <<Item Type|itemtypes>>
AND items.itemcallnumber LIKE concat(<<Call number like>>, '%')

Titles added in date range using 005

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Catalog
  • Purpose: List titles added between a date range listed in the 005
  • Status: Complete
SELECT b.title, m.isbn, b.biblionumber 
FROM biblio b l
eft JOIN bibioitems m USING (biblionumber) 
WHERE ExtractValue(m.marcxml,'//controlfield[@tag="005"]') BETWEEN DATE_FORMAT(<<Added BETWEEN (yyyy-mm-dd)|date>>, '%Y%m%d%H%i%s') 
      AND DATE_FORMAT(<<and (yyyy-mm-dd)|date>>, '%Y%m%d%H%i%s')


Age of collection by copyright date

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Count number of Items in collection, grouped by copyright date range.
  • Status: Complete
SELECT
    CONCAT(
        ( copyrightdate DIV 5 ) * 5,
        ' - ',
        ( copyrightdate DIV 5 ) * 5 + 4
    ) AS 'Copyright date range',
    COUNT(*) AS 'Count of items'
FROM
    biblio
    INNER JOIN items USING (biblionumber)
WHERE copyrightdate IS NOT NULL
GROUP BY copyrightdate DIV 5
ORDER BY copyrightdate DIV 5

Titles without leading articles (by 245 second indicators

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: Useful if you need a report alphabatized by title no including things like A, AN, THE etc.
  • Status: Complete
SELECT SUBSTRING(title,ExtractValue(marcxml,'//datafield[@tag="245"]/@ind2')+1) AS Title
FROM biblio
LEFT JOIN biblioitems USING (biblionumber)
ORDER BY Title

Syntax-highlighted MARC XML

  • Developer: Eric Phetteplace, California College of the Arts
  • Module: Catalog
  • Purpose: See a record's full XML with highlighting that makes it easier to read
  • Status: Complete
SELECT CONCAT(
    '<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.5.0/styles/monokai-sublime.min.css">',
    '<script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.5.0/highlight.min.js"></script>',
    '<script>hljs.initHighlightingOnLoad();</script>',
    '<pre><code class="xml">',
    REPLACE(REPLACE(marcxml, '<', '&lt;'), '>', '&gt;'), '</code></pre>') AS MARCXML
FROM biblioitems
JOIN biblio USING (biblionumber)
WHERE biblionumber = <<biblionumber>>

Find the import batch related to a title

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: See the batches containing a given title
  • Status: Complete
SELECT 
    CONCAT('<a href="/cgi-bin/koha/tools/manage-marc-import.pl?import_batch_id=',import_batch_id,'">Link to import</a>') AS Linker,
    title,
    import_record_id,
    matched_biblionumber,
    import_batch_id,
    file_name, comments 
FROM import_biblios 
JOIN import_records USING (import_record_id) 
JOIN import_batches USING (import_batch_id) 
WHERE title LIKE CONCAT('%',<<Enter partial OR FULL title>>,'%')

Find records marked deleted with leader/05='d'

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Catalog
  • Purpose: See records marked deleted
  • Status: Complete
SELECT biblionumber, title, author, ExtractValue(metadata,'//leader') AS "Leader Field", SUBSTRING(ExtractValue(metadata,'//leader'),6,1) AS "Position05"
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE SUBSTRING(ExtractValue(metadata,'//leader'),6,1) = 'd'

List items for Reading groups - Provide the number of copies needed

  • Developer: Brenda Turnbull, LiveWire CIC
  • Module: Catalog
  • Purpose: List of items that can be used for reading groups - enter the number of copies needed for an item Looks for Item types Adult Fiction and Junior Fiction in various locations A
  • Status: Complete
SELECT   
      b.title AS   'Item                   Title          ',
      b.author AS '       Author       ',
      i.itemcallnumber AS CallNo,  
      i.itype AS 'Item/ Type',  
      i.location ,   
      CONCAT( Extractvalue(bi.marcxml,  '//datafield[@tag="264"]/subfield[@code>="c"]'),  Extractvalue(bi.marcxml,  '//datafield[@tag="260"]/subfield[@code>="c"]') )AS PUBYR , 
             CONCAT( COUNT(i.barcode),   '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',i.biblionumber,'\">'," see more ",'</a>')   AS' Possible No. / of Copies',
 i.itemnotes AS 'Public Notes', abstract AS Abstract
FROM items i
LEFT JOIN biblio b USING (biblionumber)   
LEFT JOIN biblioitems bi USING (biblionumber)               
WHERE    i.itype IN ('AF','JF')
 AND i.location IN ('A', 'G','SFG','H','X','LP','R','SF','Y','T','TC')
GROUP BY i. biblionumber  HAVING COUNT(i.barcode) > <<How many copies needed? >>
ORDER BY b.title


Records with non-ascii characters in RDA copyright field

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Find records with non-ascii characters in 264$c
  • Status: Complete
SELECT
    CONCAT(
        '<a href=\"/cgi-bin/koha/catalogue/MARCdetail.pl?biblionumber=', biblionumber, '\">',
        biblionumber, '</a>' 
    ) AS biblionumber,
    ExtractValue( marcxml, '//datafield[@tag=264]/subfield[@code="c"]' ) AS '264$c'
FROM
    biblioitems
    INNER JOIN biblio USING (biblionumber)
WHERE
    ExtractValue( marcxml, '//datafield[@tag=264]/subfield[@code="c"]' ) <> CONVERT( ExtractValue( marcxml, '//datafield[@tag=264]/subfield[@code="c"]' )  USING ASCII)

Find biblionumbers for marc records containing '�'

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Catalog
  • Purpose: Records imported with character-set mis-matches will often contain the unicode REPLACEMENT CHARACTER '�', represented in UTF-8 as 0xEFBFBD. This query can be used to find these characters.
  • Status: Complete
  • Notes: For some reason, this does not return records when run as a report. It works in koha-mysql however.
SELECT
    biblionumber
FROM
    biblioitems
WHERE
    hex(marcxml) LIKE '%EFBFBD%'


Duplicated barcods after removing leading zeros

  • Developer: Pablo Bianchi
  • Module: Catalog
  • Purpose: After a big import of records trying to remove leading zeros from barcods result in duplicates.
  • Status: Complete
  • Notes: TODO: make biblionumber links to records.
SELECT COUNT(*) AS "Reps",
       CAST(barcode AS UNSIGNED) AS "Barcodes duplicated without zeros",
       GROUP_CONCAT(biblionumber SEPARATOR ' ') AS "Biblionumbers"
FROM items
GROUP BY CAST(barcode AS UNSIGNED)
HAVING COUNT(*) > 1 AND COUNT(CASE WHEN homebranch = <<Pick your branch|branches>> THEN 1 END) >= 1
ORDER BY COUNT(*) DESC, CAST(barcode AS UNSIGNED) DESC


Missing barcode in a range

SELECT (i1.barcode + 1) AS gap_starts_at, 
       (SELECT MIN(i3.barcode) -1 FROM items i3 WHERE i3.barcode > i1.barcode) AS gap_ends_at
FROM items i1
# Range TO CHECK:
WHERE i1.barcode BETWEEN 0 AND 10000 
AND NOT EXISTS (SELECT i2.barcode FROM items i2 WHERE i2.barcode = i1.barcode + 1)
HAVING gap_ends_at IS NOT NULL

Accounting Reports (Fines/Credits/Etc)

Fines w/ Patron & Item Info

  • Developer: Kyle M Hall
  • Module: Accounting
  • Purpose: List of unpaid fines with patron and item information
  • Status: Complete
SELECT 
    b.surname, b.firstname, b.email, bib.title, i.barcode,
    a.amountoutstanding, ni.issuedate, ni.date_due, 
    IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate 
FROM accountlines a 
  LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber ) 
  LEFT JOIN items i ON ( a.itemnumber = i.itemnumber ) 
  LEFT JOIN biblio bib ON ( i.biblionumber = bib.biblionumber ) 
  LEFT JOIN ( SELECT * FROM issues UNION SELECT * FROM old_issues ) ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber ) 
WHERE 
    a.amountoutstanding > 0 
GROUP BY a.description
ORDER BY b.surname, b.firstname, ni.timestamp DESC

Patrons w/ Fines

  • Developer: Katrin Fischer
  • Module: Accounting
  • Purpose: List patrons with their fine amounts
  • Status: Complete
SELECT 
    (SELECT CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ', b.firstname,'</a>') 
    FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron, 
    format(sum(amountoutstanding),2) AS 'Outstanding',
    (SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'
FROM 
    accountlines a, borrowers b
WHERE 
    (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber)  > '0.00'
    AND a.borrowernumber = b.borrowernumber
GROUP BY 
    a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC


Patrons w/ Fines at Branch

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: List patrons with their fine amounts limited by branch
  • Status: Complete
SELECT p.surname, p.firstname, p.borrowernumber, p.cardnumber,
format(sum(a.amountoutstanding),2) AS owes
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
WHERE a.amountoutstanding > 0 AND p.branchcode=<<Branch|branches>>
GROUP BY a.borrowernumber

Patrons w/ More Than an Amount in Fines

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: List patrons with who owe greater than or equal to an amount entered when the report is run.
  • Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, p.address, p.address2, p.city, p.state, p.phone, p.branchcode AS 'patron branch',
       p.debarred , p.debarredcomment, p.dateexpiry, format(sum(a.amountoutstanding),2) AS  'amount owed'
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
GROUP BY a.borrowernumber
HAVING sum(a.amountoutstanding) >= <<Owe more than>>
ORDER BY p.surname, p.firstname

Patrons w/ credits

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • Purpose:
  • Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, address, city, zipcode, round(Sum(accountlines.amountoutstanding),2) AS 'total owed' 
FROM accountlines LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) WHERE amountoutstanding != 0 
GROUP BY accountlines.borrowernumber HAVING sum(accountlines.amountoutstanding) < 0 
ORDER BY borrowers.surname, borrowers.firstname

Collections Report for Unique Management

  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS
  • Module: Accounting
  • Purpose: Outputs patrons with fines in certain categories, with more than $X in fines, and no fine payments in the last 60 days.
  • Status: Completed
  • Note: Updated by Jared Camins-Esakov, C & P Bibliography Services on 16 May 2012. If you were using a previous version of this report, please update the report and contact Unique immediately to tell them to disregard previous reports. The logic was reversed.
  • Note: This report, with the NOT IN line, eliminates any patron who has *ever* paid on their account - it is too broad and we've removed it from our reports we use at NEKLS now (see the next report for an updated specific version).
  SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, 
  borrowers.address, borrowers.city, borrowers.zipcode, borrowers.email, borrowers.phone,
  borrowers.dateofbirth, borrowers.debarred, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
  FROM borrowers, accountlines 
  WHERE borrowers.categorycode IN ('BONN-CITY', 'OTT-CITY') 
  AND borrowers.borrowernumber 
  NOT IN (SELECT DISTINCT borrowernumber FROM accountlines WHERE accountlines.date < DATE_SUB(CURDATE(),INTERVAL 60 DAY) AND (accountlines.accounttype IN ('PAY', 'C') ) )
  AND borrowers.borrowernumber = accountlines.borrowernumber 
  GROUP BY borrowers.borrowernumber 
  HAVING SUM(accountlines.amountoutstanding) >= 25.00 
  ORDER BY borrowers.surname ASC;
  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
  • Module: Accounting
  • Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
  • Status: Completed
  • Note: New Delinquent Report - first report sent to Unique, sent weekly after that
  • Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT borrowers.cardnumber, borrowers.borrowernumber, borrowers.surname, borrowers.firstname, borrowers.address, borrowers.city, borrowers.zipcode, borrowers.phone, borrowers.mobile, borrowers.phonepro AS "Alt Ph 1", borrowers.B_phone AS "Alt Ph 2", borrowers.branchcode, categories.category_type AS "Adult or Child", borrowers.dateofbirth, MAX(accountlines.date) AS "Most recent charge", FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
LEFT JOIN borrowers USING(borrowernumber) 
LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') 
AND ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL )
AND accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) 
AND accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day) 
GROUP BY borrowers.borrowernumber 
HAVING Due >=25.00 
ORDER BY borrowers.surname ASC


  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
  • Module: Accounting
  • Purpose: Produces report of patrons in particular branch codes with their sort1 field set to "yes" and fines of more than $25.
  • Status: Completed
  • Note: Update report - weekly report sent to Unique
SELECT borrowers.borrowernumber, borrowers.surname, borrowers.firstname, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
LEFT JOIN borrowers USING(borrowernumber) 
LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN (BRANCHCODES SEPARATED BY COMMAS) 
AND borrowers.sort1 = 'yes'
GROUP BY borrowers.borrowernumber 
ORDER BY borrowers.surname ASC
  • Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
  • Module: Accounting
  • Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
  • Status: Completed
  • Note: New Delinquent Report Linked - used to easily add the $10 fee and set the sort1 field to "yes" for new delinquent accounts
  • Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT CONCAT ('<a href=\"/cgi-bin/koha/members/maninvoice.pl?borrowernumber=',borrowers.borrowernumber,'\" target="_blank">',borrowers.cardnumber,'</a>') AS "Link to Fines",  borrowers.borrowernumber, borrowers.surname, borrowers.firstname, borrowers.address, borrowers.city, borrowers.zipcode, borrowers.phone, borrowers.mobile, borrowers.phonepro AS "Alt Ph 1", borrowers.B_phone AS "Alt Ph 2", borrowers.branchcode, categories.category_type AS "Adult or Child", borrowers.dateofbirth, MAX(accountlines.date) AS "Most recent charge", FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
LEFT JOIN borrowers USING(borrowernumber) 
LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') 
AND ( borrowers.sort1 != 'yes' OR borrowers.sort1 IS NULL )
AND accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) 
AND accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day) 
GROUP BY borrowers.borrowernumber 
HAVING Due >=25.00 
ORDER BY borrowers.surname ASC

Incremental Fines w/ Patron & Item Info

  • Developer: Ramprasad Joshi
  • Module: Accounting
  • Purpose: List of unpaid fines with patron and item information, with an incremental charge: $1 per day the first fortnight overdue, $2 for the next, $5 daily after that; it can be tailored by patron category.
  • Status: One Example
SELECT
    borrowers.cardnumber,borrowers.categorycode,borrowers.surname,issues.date_due,
    (TO_DAYS(curdate())-TO_DAYS( date_due)) AS daysoverdue,
    items.barcode AS 'Accession Number',
    biblio.title,biblio.author,
    IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=15,(TO_DAYS(curdate())-TO_DAYS( date_due)),
      IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=30,2*(TO_DAYS(curdate())-TO_DAYS( date_due))-15,5*(TO_DAYS(curdate())-TO_DAYS( date_due))-105))
     AS fine
  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 (borrowers.categorycode=<<Patron Category|categorycode>>) AND (TO_DAYS(curdate())-TO_DAYS(date_due)) > '0'
  ORDER BY borrowers.cardnumber ASC

Total Forgiven Fines Today

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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'

Yesterday's Forgiven Charges (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • 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: Accounting
  • 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: Accounting
  • 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: Accounting
  • Purpose: credits yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'C') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Credits (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • 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: Accounting
  • 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 accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'N') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's New Card Fees (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • 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: Accounting
  • Purpose: payments yesterday for a particular branch (edit branchcode as needed)
  • Status: Complete


SELECT 
  round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'PAY') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

Yesterday's Payments (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • 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: Accounting
  • 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 accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND YEAR(date) = YEAR(NOW()) AND borrowers.branchcode = 'LIB'

Year to Date Fines (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • 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: Accounting
  • Purpose: total amount of fines owed (entire system)
  • Status: Complete


SELECT FORMAT(Sum(accountlines.amountoutstanding),2) FROM accountlines

Writeoff fine (Date Range wise)

  • Developer: Nikunj Tyagi, DPL
  • Module: Accounting
  • Purpose: writeoff Amount (Date range wise) with patron details (entire system)
  • Status: Complete


SELECT borrowers.borrowernumber, borrowers.cardnumber, accountlines.amount, accountlines.date
FROM accountlines, borrowers WHERE borrowers.borrowernumber = accountlines.borrowernumber AND accounttype = 'W' AND date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>

Payment (fine) detail (Date Range)

  • Developer: Nikunj Tyagi, DPL
  • Module: Accounting
  • Purpose: Payment (Date range wise) with patron details (entire system)
  • Status: Complete


SELECT borrowers.borrowernumber, borrowers.cardnumber, accountlines.amount, accountlines.date
FROM accountlines, borrowers WHERE borrowers.borrowernumber = accountlines.borrowernumber AND accounttype = 'pay' AND date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>

Yesterday's Amount Collected (entire system)

  • Developer: Jane Wagner, PTFS
  • Module: Accounting
  • 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)

Amount Collected in specific Date Range (entire system)

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: Asks you to enter the date range for which you would like to see all of the money collected at all branches.
  • Status: Complete
SELECT FORMAT(abs(sum(amount)),2) AS 'Total Collected' 
FROM accountlines 
WHERE (accounttype='C' OR accounttype='PAY') AND 
      timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>


Accounting for date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: List of all accounting details in date range
  • Status: Complete
SELECT 
CASE accounttype 
      WHEN 'A' THEN 'Account management fee'
      WHEN 'C' THEN 'Credit'
      WHEN 'F' THEN 'Overdue Fine'
      WHEN 'FOR' THEN 'Forgiven'
      WHEN 'FU' THEN 'Overdue Fine Still Accruing'
      WHEN 'L' THEN 'Lost Item'
      WHEN 'LR' THEN 'Lost and Returned'
      WHEN 'M' THEN 'Sundry'
      WHEN 'N' THEN 'New Card'
      WHEN 'PAY' THEN 'Payment'
      WHEN 'W' THEN 'Writeoff'
      ELSE accounttype END  
 AS transaction, SUM(amount)
 FROM accountlines
 WHERE DATE(timestamp) BETWEEN <<Collected BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>
 GROUP BY accounttype

Payments collected at a branch in a date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Accounting
  • Purpose: Fines collected at a branch in a date range (uses the logged in user's branch)
  • Status: Complete
SELECT FORMAT(abs(sum(a.amount)),2) AS 'Total Collected' 
FROM accountlines a
LEFT JOIN borrowers p ON (a.manager_id=p.borrowernumber)
WHERE a.accounttype IN ('C','PAY') AND 
      a.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND
p.branchcode=<<Branch|branches>>

Amount due on lost items (deleted and current)

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Accounting
  • Purpose: Amount due on lost items (deleted and current)
  • Status: Complete
SELECT
    FORMAT( SUM( amountoutstanding ), 2) AS 'Amount Due'
FROM
    items i
    LEFT JOIN deleteditems di USING ( itemnumber )
    LEFT JOIN accountlines a ON ( a.itemnumber =  COALESCE (i.itemnumber, di.itemnumber) )
WHERE
    COALESCE ( i.itemlost, di.itemlost  ) != 0

Forgiven fines for items checked in during a date range (Fines amnesty week check)

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Accounting
  • Purpose: Fines forgiven on books checked in during a date range
  • Status: Complete
SELECT surname, firstname, cardnumber, description, amountoutstanding, itemcallnumber, holdingbranch,barcode, datetime AS CheckInDate, a.timestamp AS FineDate  FROM statistics 
LEFT JOIN accountlines a USING (borrowernumber,itemnumber)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
WHERE type='return' AND accounttype='FFOR' AND datetime BETWEEN <<Start date|date>> AND <<End date|date>>

Fines outstanding for items checked in during a date range (Fines amnesty week check)

  • Developer: Nick Clemens, ByWater Solutions
  • Module: Accounting
  • Purpose: Fines outstanding on books checked in during a date range
  • Status: Complete
SELECT surname, firstname, cardnumber, description, amountoutstanding, itemcallnumber, holdingbranch,barcode, datetime AS CheckInDate, a.timestamp AS FineDate  FROM statistics 
LEFT JOIN accountlines a USING (borrowernumber,itemnumber)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
WHERE type='return' AND amountoutstanding > 0 AND datetime BETWEEN <<Start date|date>> AND <<End date|date>>

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 <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

Patrons with most checkouts in date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: This report will show the top 20 patrons who have checked out the most in a specific time period.
  • Status: Complete
SELECT concat(b.surname,', ',b.firstname) AS name, 
       count(s.borrowernumber) AS checkouts 
FROM statistics s 
LEFT JOIN borrowers b 
USING (borrowernumber) 
WHERE s.datetime BETWEEN <<Top checkouts BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY s.borrowernumber 
ORDER BY count(s.borrowernumber) DESC 
LIMIT 20

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) = <<Year accessioned (yyyy)>> AND MONTH(i.dateaccessioned) = <<Month accessioned (mm)>> 
  GROUP BY i.homebranch,i.itype,i.location 
  ORDER BY i.homebranch,i.itype,i.location ASC

Inactive Borrowers

  • Developer: Jonathan Field
  • Module: Statistical (Circulation, Reports)
  • Purpose: List of Borrowers who have not used the library within a given period
  • Status: Complete
SELECT DISTINCT borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.email 
FROM borrowers
WHERE NOT EXISTS (SELECT borrowernumber FROM statistics WHERE borrowers.borrowernumber = borrowernumber AND statistics.datetime >= 'YYYY-MM-DD')


Number of links clicked in the last month

  • Developer: Liz Rea, Catalyst IT for Albany Senior High School, New Zealand
  • Module: Statistical
  • Purpose: Count of links clicked in the last month
  • Status: Complete
SELECT count(*) FROM linktracker WHERE timeclicked > DATE_SUB(now(), INTERVAL 1 MONTH)

List of links clicked in the last month

  • Developer: Liz Rea, Catalyst IT for Albany Senior High School, New Zealand
  • Module: Statistical
  • Purpose: List of links clicked in the last month
  • Status: Complete
SELECT count(url) AS 'times', url FROM linktracker WHERE timeclicked > DATE_SUB(now(), INTERVAL 1 MONTH) GROUP BY url

Statistic for daily catalogers achievement in date range for bib records

  • Developer: Karam Qubsi
  • Module: Cataloging
  • Purpose: Statistic for daily catalogers achievement in date range for bib records (you can change the date range I make it for the whole 2014 year in this example )
  • Status: Complete
SELECT DATE_FORMAT(I.timestamp, '%d-%c-%Y') AS Date,
B.userid AS Staff,
count(I.timestamp) AS Count
FROM action_logs I
LEFT JOIN borrowers B
ON I.user=B.borrowernumber
WHERE I.module='CATALOGUING' AND I.action='ADD' AND date(I.timestamp) BETWEEN '2014-01-01' AND '2014-12-31' 
 AND I.info='biblio'
GROUP BY Date,B.userid
ORDER BY DATE(timestamp) DESC

Statistic for daily catalogers achievement in date range for Item records

  • Developer: Karam Qubsi
  • Module: Cataloging
  • Purpose: Statistic for daily catalogers achievement in date range for item records (you can change the date range I make it for the whole 2014 year in this example )
  • Status: Complete
SELECT DATE_FORMAT(I.timestamp, '%d-%c-%Y') AS Date,
B.userid AS Staff,
count(I.timestamp) AS Count
FROM action_logs I
LEFT JOIN borrowers B
ON I.user=B.borrowernumber
WHERE I.module='CATALOGUING' AND I.action='ADD' AND date(I.timestamp) BETWEEN '2014-01-01' AND '2014-12-31' 
 AND I.info='item'
GROUP BY Date,B.userid
ORDER BY DATE(timestamp) DESC


Percentage lost broken down by homebranch

  • Developer: Barton Chittenden
  • Module: Cataloging
  • Purpose: Find out if certain branches are losing more items
  • Status: Complete
SELECT
  homebranch,
  count(homebranch),
  sum( IF(itemlost=0,0,1) ) AS 'Number Lost',
  (sum( IF(itemlost=0,0,1) ) * 100 / count(homebranch)) AS 'Percentage lost'
FROM items
GROUP BY homebranch
HAVING count(homebranch) > 0
ORDER BY (sum( IF(itemlost=0,0,1) ) * 100 / count(homebranch))


Statistics for college loans

  • Developer: Jussef Martínez
  • Module: Statistical
  • Purpose: Count the number of loans made by users of a faculty.
  • Status: Complete
SELECT
  u.sort1 AS 'Department',
  COUNT(lg.object) AS 'No. Loans'
 
FROM action_logs lg
 
  LEFT JOIN borrowers u ON (lg.object = u.borrowernumber)
  LEFT JOIN categories k ON (u.categorycode = k.categorycode )
 
WHERE
  lg.module = 'circulation' AND action = 'issue' AND
  DATE(lg.timestamp) BETWEEN <<fecha inicial|date>> AND <<fecha final|date>>
 
GROUP BY u.sort1
ORDER BY u.sort2 ASC

Librarians activity

  • Developer: Josef Moravec
  • Module: Statistical
  • Purpose: Count the actions performed by librarians in particular modules of Koha
  • Status: Complete
SELECT 
  CONCAT_WS(" ", b.firstname, b.surname) AS name, 
  al.module, 
  COUNT(*) AS count
 
FROM action_logs al
  JOIN borrowers b ON al.user = b.borrowernumber
 
WHERE 
  timestamp >= <<From|date>> 
  AND timestamp <= DATE_ADD(<<To|date>>, INTERVAL 1 DAY)  
  AND user != 0
 
GROUP BY al.user, al.module

Notices Reports

Overdue Notices

Notices Sent

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Notices
  • Purpose: Count of overdue notices sent in a specific time frame (by type). Uses the following codes for overdue messages: ODUE, ODUE2, ODUE3. Edit notice names as necessary. See Notices Available below.
  • 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 message_queue.time_queued BETWEEN <<Sent BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
      AND message_queue.letter_code IN ('ODUE', 'ODUE2', 'ODUE3' )
      AND STATUS = 'sent' 
GROUP BY year(message_queue.time_queued), month(message_queue.time_queued), message_queue.letter_code

Overdue Notices Available

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list of letter codes used in trigger table.
  • Status: Complete
SELECT 
    DISTINCT code 
FROM 
   letter 
   INNER JOIN (
             SELECT DISTINCT letter1 AS code FROM overduerules 
       UNION SELECT DISTINCT letter2 AS code FROM overduerules 
       UNION SELECT DISTINCT letter3 AS code FROM overduerules
   ) AS overdue_trigger USING (code)

Patrons ordered by days overdue

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list of patrons with overdue items, ordered by days overdue.
  • Status: Complete

I would like to highlight rows where 'days overdue' matches (delay1, delay2, delay3) from overduerules.

SELECT
    datediff(CURRENT_DATE, date_due) AS 'days overdue',
    count(*) AS 'count',
    CONCAT(
        '<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=',
        borrowernumber,
        '\">',
        firstname,
        ' ',
        surname,
        '</a>'
    ) AS borrowernumber
FROM
    issues
    INNER JOIN borrowers USING (borrowernumber)
WHERE datediff(CURRENT_DATE, date_due) > 1
GROUP BY datediff(CURRENT_DATE, date_due), borrowernumber
ORDER BY datediff(CURRENT_DATE, date_due) ASC, count(*) DESC

Patrons with overdue notices triggered today

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list of patrons with overdue notices triggered today.
  • Status: Complete
SELECT
    datediff(CURRENT_DATE, date_due) AS 'days overdue',
    count(*) AS 'count',
    CONCAT(
        '<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=',
        borrowernumber,
        '\">',
        firstname,
        ' ',
        surname,
        '</a>'
    ) AS borrowernumber
FROM
    issues
    INNER JOIN (
              SELECT DISTINCT delay1 AS delay FROM overduerules
        UNION SELECT DISTINCT delay2 AS delay FROM overduerules
        UNION SELECT DISTINCT delay3 AS delay FROM overduerules
    ) AS odr ON ( datediff(CURRENT_DATE, date_due) = odr.delay )
    INNER JOIN borrowers USING (borrowernumber)
WHERE datediff(CURRENT_DATE, date_due) > 1
GROUP BY datediff(CURRENT_DATE, date_due), borrowernumber
ORDER BY datediff(CURRENT_DATE, date_due) ASC, count(*) DESC

Patrons ordered by count of items with third notices

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Print a list patrons with a count of items which would trigger a third notice.
  • Status: Complete
SELECT
    count(*) AS 'count',   
    CONCAT(
        '<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=',
        borrowernumber,
        '\">',
        firstname,
        ' ',
        surname,
        '</a>'
    ) AS borrowernumber
FROM
    issues
    INNER JOIN borrowers USING (borrowernumber)
WHERE
    date_add( date_due, INTERVAL (SELECT max(delay3) FROM overduerules) DAY ) < CURRENT_DATE
GROUP BY
    borrowernumber ORDER BY count(*)

Notices and slips, select content

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Find Notices and Slips with selected content.
  • Status: Complete
SELECT
    module, code, branchcode, name, is_html, title, message_transport_type
FROM
    letter
WHERE
    content LIKE '<<Text in notice (use % as wildcard)>>'

Overduerules in a single column

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Show Overduerules in a single column
  • Status: Complete
  • Comments: This query is probably a useful base for more complex queries.
SELECT
    letter_number,
    branchcode,
    categorycode,
    delay,      
    letter_code,
    debarred
FROM
   (
            SELECT
                '1' AS letter_number, branchcode, categorycode,
                delay1 AS delay, letter1 AS letter_code,
                debarred1 AS debarred
            FROM overduerules
        UNION
            SELECT
                '2' AS letter_number, branchcode, categorycode,
                delay2 AS delay, letter2 AS letter_code,
                debarred2 AS debarred
            FROM overduerules
        UNION
            SELECT
                '3' AS letter_number, branchcode, categorycode,
                delay3 AS delay, letter3 AS letter_code,
                debarred3 AS debarred
            FROM overduerules
   ) AS odr

Show overdue notices in message queue

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Show counts of overdue notices by letter code, message transport type and status, ordered by date.
  • Status: Complete
SELECT
    count(*),
    date(time_queued),
    letter_code,
    message_transport_type AS mtt,
    STATUS
FROM
    message_queue
    INNER JOIN
       (
                SELECT letter1 COLLATE utf8_unicode_ci AS letter_code FROM overduerules
            UNION
                SELECT letter2 COLLATE utf8_unicode_ci AS letter_code FROM overduerules
            UNION
                SELECT letter3 COLLATE utf8_unicode_ci AS letter_code FROM overduerules
       ) AS odr USING (letter_code)
GROUP BY
    date(time_queued), letter_code, mtt,STATUS
ORDER BY
    date(time_queued), letter_code, mtt,STATUS

Acquisition Reports

Orders in Date Range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show order information for a specific date period.
  • Status: Complete. Broken in Koha 3.16. aqorders.notes field was replaced with aqorders.order_internalnote and aqorders.order_vendornote
SELECT v.name AS vendor, b.title AS 'book title', 
       format(o.listprice,2) AS 'list price', 
       format(o.unitprice,2) AS 'actual price',
       ba.basketname, o.notes 
FROM aqorders o 
LEFT JOIN aqbasket ba USING (basketno) 
LEFT JOIN aqbooksellers v ON (v.id = ba.booksellerid) 
LEFT JOIN biblio b USING (biblionumber) 
WHERE o.entrydate BETWEEN <<Ordered BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

All Orders

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show the information for every order in the system.
  • Status: Complete
SELECT v.name AS Vendor, concat(c.basketname, ' (', c.basketno, ')') AS Basket,
       c.creationdate AS Ordered,  concat(p.firstname, ' ', 
       p.surname) AS 'Managed by', b.title, 
       ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS subtitle, 
       b.author, bi.isbn, bi.publishercode, 
       o.rrp AS RRP, o.ecost AS EST, o.quantity AS Qty,
       format(o.listprice*o.quantity,2) AS Total, f.budget_name AS Fund 
FROM aqorders o 
LEFT JOIN aqbudgets f USING (budget_id) 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN biblioitems bi USING (biblionumber) 
LEFT JOIN aqbasket c USING (basketno) 
LEFT JOIN aqbooksellers v ON (c.booksellerid=v.id) 
LEFT JOIN borrowers p ON (c.authorisedby=p.borrowernumber) 
ORDER BY v.name, c.basketno ASC


Items ordered vs. received

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show the number of items ordered vs. received for a given vendor.
  • Status: Complete
SELECT
    aqbasket.basketno, 
    aqbasket.basketname, 
    aqbasket.creationdate, 
    aqbasket.closedate, 
    sum(aqorders.quantity) AS 'Items ordered', 
    sum(aqorders.quantityreceived) AS 'Items received',
    sum(aqorders.quantity) - sum(aqorders.quantityreceived) AS difference
FROM
    aqbooksellers
    INNER JOIN aqbasket ON (aqbooksellers.id = aqbasket.booksellerid) 
    INNER JOIN aqorders USING (basketno) 
WHERE
    aqbooksellers.id = 91
    AND aqbasket.creationdate BETWEEN <<Ordered BETWEEN|date>> AND <<and|date>>
GROUP BY basketno
ORDER BY aqbasket.creationdate

Ledger

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show's a ledger of all items ordered
  • Status: Complete. Works on Koha 3.16. Changed biblioitemnumber to biblionumber in the join on items.
SELECT b.name AS vendor, i.itype, p.budget_branchcode AS branch, k.basketno,  
       o.entrydate AS 'order date', format(o.listprice,2) AS 'list price', 
       format(o.unitprice,2) AS 'unit price', o.quantity, 
       format(o.totalamount,2) AS 'total amount', o.datereceived AS 'date received' 
FROM aqbasket k 
LEFT JOIN aqbooksellers b ON (k.booksellerid=b.id) 
LEFT JOIN aqorders o USING (basketno)
LEFT JOIN items i USING (biblionumber)
LEFT JOIN aqbudgets p USING (budget_id)

Titles ordered in a Fund

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Titles ordered in a specific fund (will search for any part of the fund code)
  • Status: Complete
SELECT b.title, b.author 
FROM biblio b 
LEFT JOIN aqorders a 
USING (biblionumber) 
LEFT JOIN aqbudgets aq 
USING (budget_id) 
WHERE a.datereceived BETWEEN <<Date received BETWEEN (yyyy-mm-dd)|date>> 
          AND <<and (yyyy-mm-dd)|date>> AND
          aq.budget_code LIKE concat(<<Budget code>>,'%')

Amount Encumbered

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Total encumbered against each budget
  • Status: Complete
SELECT b.budget_name, format(sum(b.budget_amount),2) AS 'amount budgeted',
          format(sum(o.listprice*o.quantity),2) AS 'amount encumbered' 
FROM aqorders o 
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NULL 
GROUP BY b.budget_name

Amount Spent

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Total spent against each budget
  • Status: Complete
SELECT b.budget_name, format(sum(b.budget_amount),2) AS 'amount budgeted',
          format(sum(o.listprice*o.quantity),2) AS 'amount spent' 
FROM aqorders o 
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NOT NULL 
GROUP BY b.budget_name

Tax Receipt

  • Developer: Paul A., Naval Marine Archive
  • Module: Acquisitions
  • Purpose: To allow a Charity to produce an "inventory" for Tax Receipting purposes; selection by Donor and Accession date.
  • Status: Complete
SELECT
items.barcode AS Barcode,items.dateaccessioned AS 'Acc Date', 
biblio.title AS Title, biblio.author AS Author,biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Year, 
biblioitems.editionstatement AS Edition,
items.price AS FMV,
ExtractValue(more_subfields_xml,
'/collection/record/datafield[\@tag=\"999\"]/subfield[\@code=\"x\"]') AS 'Condition'
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE
items.booksellerid = <<Pick your donor>>
AND DATE(items.dateaccessioned) BETWEEN <<Accessioned BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
 
ORDER BY biblio.author ASC

Orders with like data in a subfield of a field

  • Developer: Joseph Alway
  • Module: Acquisitions
  • Purpose: Displays the Biblionumber, Title, Author, Basket Name, List Price, Budget Code, and Vendor of All Orders with a like value in a subfield of a field.
  • Status: Complete
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title,
    biblio.author, aqbasket.basketname AS PO, format(aqorders.listprice,2) AS 'List Price', aqbudgets.budget_code AS Fund, aqbooksellers.name AS Vendor
FROM
    biblioitems
    JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
    JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
    LEFT JOIN aqorders ON (biblioitems.biblionumber = aqorders.biblionumber)
    LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
    LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id)
    LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
WHERE
    ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
        LIKE <<Search Term (USE % AS wildcard)>>
    AND biblio.biblionumber = aqorders.biblionumber

Orders received in a date range

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Orders received in a date range
  • Status: Complete
SELECT t.title, b.name AS vendor, p.budget_name AS fund, format(o.listprice,2) AS 'list price',
format(o.unitprice,2) AS 'unit price', o.quantity,
format(o.totalamount,2) AS 'total amount', o.datereceived AS 'date received', o.sort1
FROM aqbasket k
LEFT JOIN aqbooksellers b ON (k.booksellerid=b.id)
LEFT JOIN aqorders o USING (basketno)
LEFT JOIN items i USING (biblionumber)
LEFT JOIN aqbudgets p USING (budget_id)
LEFT JOIN biblio t USING (biblionumber)
WHERE o.datereceived BETWEEN <<Received BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

Orders not received

  • Developer: Joseph Alway
  • Module: Acquisitions
  • Purpose: List orders that haven't been received.
  • Status: Complete
SELECT 
    CONCAT('<a href=\"/cgi-bin/koha/acqui/basket.pl?basketno=',basketno,'\">',basketno,'</a>') AS Basket,
    CONCAT('<a href=\"/cgi-bin/koha/acqui/neworderempty.pl?ordernumber=',a.ordernumber,'\">',a.ordernumber,'</a>') AS 'Order',
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',a.biblionumber,'\">',a.biblionumber,'</a>') AS Biblio,
    b.title AS Title, b.author AS Author, format(a.listprice,2) AS 'List Price', format(a.discount,2) AS 'Discount', format(a.ecost,2) AS 'Estimated Cost',
    u.budget_code AS 'Fund Code', u.budget_name AS 'Fund Name', a.order_internalnote AS 'Order Notes', a.entrydate AS 'Date Ordered'
FROM
    aqorders a
    LEFT JOIN biblio b
        USING (biblionumber) 
    LEFT JOIN aqinvoices i
        USING (invoiceid)
    LEFT JOIN aqbudgets u
        USING (budget_id)
WHERE
    a.ordernumber IS NOT NULL
    AND a.datecancellationprinted IS NULL
    AND a.datereceived IS NULL
ORDER BY u.budget_name ASC

List of Order given to specific Vendor during current active budget

  • Developer: Vinod Kumar Mishra (http://mishravk.com/)
  • Module: Acquisitions
  • Purpose: It gives detail about all the titles and order given to specific vendor during current active financial year.
  • Status: Complete tested on Koha 16.11.07
SELECT bb.name AS 'Vendor Name',b.basketname AS 'Basket Name', b.creationdate AS 'Basket Creation Date',
r.currency AS 'Currency', r.listprice AS 'Price', r.quantity AS 'Quantity', r.rrp AS 'Unit Price',
r.ecost AS 'Discounted Price',  format(r.ecost*r.quantity,2) AS 'Total Price',
r.orderstatus AS 'Status', bbb.budget_code AS 'Fund Code'
 
FROM aqorders r
 
LEFT JOIN aqbasket b ON b.basketno=r.basketno
LEFT JOIN aqbooksellers bb ON bb.id=b.booksellerid
LEFT JOIN aqbudgets bbb ON bbb.budget_id=r.budget_id
LEFT JOIN aqbudgetperiods bbbb ON bbbb.budget_period_id=bbb.budget_period_id
 
WHERE bb.id = <<Koha Vendor Code>> AND bbbb.budget_period_active LIKE '1'

List of Pending Books Requested/Suggested for purchase

  • Developer: Vinod Kumar Mishra (http://mishravk.com/)
  • Module: Acquisitions
  • Purpose: This will provide list of books which is suggested by various users and not yet approved. It contains information about suggester name, His ID, Status, Author, Title, Year, Publisher, ISBN, Quantity, Currency, Price, Total price and Budget head.
  • Status: Complete tested on Koha 16.11.07
  • Note: You may replace 'ASKED' with Available, Ordered etc. status from last line (WHERE STATUS LIKE 'ASKED') of this query to get the report accordingly.
SELECT bb.surname 'Suggested By', bb.cardnumber 'Member ID', r.STATUS, r.author 'Author',
r.title 'Title', r.copyrightdate 'Year', r.publishercode 'Publisher', r.isbn 'ISBN',
r.quantity 'Quantity',r.currency 'Currency', r.price 'Price', r.total 'Total Price',
b.budget_code 'Department'
 
FROM suggestions r
 
LEFT JOIN aqbudgets b ON r.budgetid=b.budget_id
LEFT JOIN borrowers bb ON r.suggestedby=bb.borrowernumber
 
WHERE STATUS LIKE 'ASKED'

Single Basket Order List

  • Developer: Vinod Kumar Mishra (http://mishravk.com/) Got help from Mr. Monirul Purkait
  • Module: Acquisitions
  • Purpose: Provide much details of the title entered in a particular basket like requester, fund, vendor etc.
  • Status: Complete tested on Koha 16.11.07
SELECT 
	v.name AS Vendor,
	concat(c.basketname, ' (', c.basketno, ')') AS Basket,
    c.creationdate AS Ordered,
    concat(pp.firstname, ' ',pp.surname, ' (', pp.cardnumber, ')') AS 'Requested By',
    concat(p.firstname, ' ',  p.surname) AS 'Managed by', 
    b.title AS 'Title', 
    ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS 'Subtitle', 
    b.author AS 'Author/Editor', bi.isbn AS 'ISBN', bi.publishercode AS 'Publisher', 
    o.currency AS 'Currency',o.listprice AS 'Original Price', o.rrp AS 'Price (INR)',o.discount AS 'Discount %', o.ecost AS 'Discounted Price', o.quantity AS 'Copies',
    format(o.ecost*o.quantity,2) AS Total, f.budget_name AS 'Fund/Dept. Name' 
FROM aqorders o
LEFT JOIN aqbudgets f USING (budget_id) 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN biblioitems bi USING (biblionumber) 
LEFT JOIN aqbasket c USING (basketno) 
LEFT JOIN aqbooksellers v ON (c.booksellerid=v.id) 
LEFT JOIN borrowers p ON (c.authorisedby=p.borrowernumber) 
LEFT JOIN suggestions s ON (b.biblionumber = s.biblionumber)
LEFT JOIN borrowers pp ON (s.suggestedby=pp.borrowernumber)
 
WHERE
 
	b.biblionumber IS NOT NULL                
	AND c.basketname LIKE CONCAT( '%', <<Basketname>>, '%')
 
ORDER BY v.name, c.basketno ASC

List of all Active Vendors

  • Developer: Vinod Kumar Mishra (http://mishravk.com/)
  • Module: Acquisitions
  • Purpose: Provide list of all active Vendors in Koha along with contact details and Koha ID.
  • Status: Complete tested on Koha 16.11.07
SELECT r.id 'Koha ID', r.name'Firm Name',i.name 'Contact Person',i.phone 'Phone/Mobile', i.email 'Email',r.postal 'Postal Address',r.fax 'Fax'
 
FROM aqbooksellers  r
LEFT JOIN aqcontacts i ON i.id=r.id
 
WHERE r.active LIKE '1'

Items list by accession number range and sorted by call number

  • Developer: Vinod Kumar Mishra (http://mishravk.com/)
  • Module: Acquisitions
  • Purpose: Display/Print of New Arrivals list Sorted by call number (Subject wise) may be useful for cover flow and public reports.
  • Status: Complete tested on Koha 16.11.07
  • Note: Replace 'XXXX' with your range of barcode
SELECT biblio.author,biblio.title,items.itemcallnumber,items.barcode,isbn
 
FROM items
 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
 
WHERE cast(items.barcode AS UNSIGNED) BETWEEN 'XXXX' AND 'XXXX' ORDER BY items.itemcallnumber ASC

Total amount of order given to each Vendor in active budget year

  • Developer: Vinod Kumar Mishra (http://mishravk.com/)
  • Module: Acquisitions
  • Purpose: This report gives you data about the total amount of order given to a particular vendor in active budget year. It will help in proper distribution of orders among the vendors.
  • Status: Complete tested on Koha 16.11.07
SELECT  b.booksellerid 'Vendor ID', bb.name AS 'Vendor Name', SUM(r.ecost * r.quantity) AS 'Total Amount'
 
FROM aqorders r
 
LEFT JOIN aqbasket b ON b.basketno=r.basketno
LEFT JOIN aqbooksellers bb ON bb.id=b.booksellerid
LEFT JOIN aqbudgets bbb ON bbb.budget_id=r.budget_id
LEFT JOIN aqbudgetperiods bbbb ON bbbb.budget_period_id=bbb.budget_period_id
 
WHERE NOT r.orderstatus='cancelled' AND bbbb.budget_period_active LIKE '1'
 
GROUP BY bb.id

Detail Accession Register Report/Format

  • Developer: Vinod Kumar Mishra (http://mishravk.com/)
  • Module: Acquisitions
  • Purpose: This report will help all Libraries to print the accession record of a book and maintain the register especially as per Indian GFR2017 prescribed accession register format.
  • Status: Complete tested on Koha 16.11.07
SELECT 
	oo.dateaccessioned AS 'Date', oo.barcode AS 'Acc. No.', ooo.title AS 'Title',
ooo.author AS 'Author/Editor', concat_ws(' , ', o.editionstatement, oo.enumchron) AS 'Ed./Vol.',
concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher',
ooo.copyrightdate AS 'Year', o.pages AS 'Page(s)', ooooooo.name AS 'Source',
oo.itemcallnumber AS 'Class No./Book No.', concat_ws(', ₹', concat(' ', ooooo.symbol, oooo.listprice), oooo.rrp_tax_included) AS 'Cost', concat_ws(' , ', oooooo.invoicenumber, oooooo.shipmentdate) AS 'Bill No. & Date', '' AS 'Withdrawn Date', '' AS 'Remarks'
 
FROM biblioitems o
LEFT JOIN items oo ON oo.biblioitemnumber=o.biblioitemnumber
LEFT JOIN biblio ooo ON ooo.biblionumber=o.biblionumber
LEFT JOIN aqorders oooo ON oooo.biblionumber=o.biblionumber
LEFT JOIN currency ooooo ON ooooo.currency=oooo.currency
LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid=oo.booksellerid
LEFT JOIN aqbooksellers ooooooo ON ooooooo.id=oo.booksellerid
 
WHERE cast(oo.barcode AS UNSIGNED) BETWEEN <<Accession Number>> AND <<To Accession Number>>
ORDER BY oo.barcode ASC

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

Late Issues

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Serials
  • Purpose: A list of items that should have arrived by now
  • Status: Complete
SELECT b.title, s.serialseq, s.planneddate 
FROM serial s 
LEFT JOIN biblio b USING (biblionumber) 
WHERE s.planneddate < CURDATE() AND s.STATUS NOT IN ('1','2')

Latest Issues

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Serials
  • Purpose: A list of the latest issue received for each subscription
  • Status: Complete
SELECT b.title, b.biblionumber, MAX(CONCAT(s.publisheddate, ' / ',s.serialseq)) AS 'date and enumeration' 
FROM serial s 
LEFT JOIN biblio b USING (biblionumber) 
WHERE s.STATUS=2 
GROUP BY b.biblionumber 
ORDER BY s.publisheddate DESC


Issues Received in a Range

  • Developer: Nicole C. Engard, ByWater Solutions with MJ Ray
  • Module: Serials
  • Purpose: A list of your serials and the year range you have received
  • Status: Complete
SELECT b.title, i.issn, 
      CONCAT(YEAR(MIN(s.publisheddate)), ' to ', YEAR(MAX(s.publisheddate))) AS 'range' 
FROM serial s
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN biblioitems i USING (biblionumber)
WHERE s.STATUS=2
GROUP BY b.biblionumber
ORDER BY b.title ASC


Year range of serials holdings

  • Developer: Paul Landers
  • Module: Serials
  • Purpose: Year range of all serial subscription titles by holding branch
  • Status: Complete
SELECT s.biblionumber,
c.issn AS ISSN,i.holdingbranch,b.title AS Title,
ExtractValue(c.marcxml,'//datafield[@tag="222"]/subfield[@code>="a"]') AS 'MARC
222 Title',
YEAR(MIN(s.publisheddate)) AS 'begin',
YEAR(MAX(s.publisheddate)) AS 'end' 
FROM serial s,items i,biblio b, biblioitems c
WHERE s.itemnumber=i.itemnumber  
AND i.biblionumber=b.biblionumber 
AND b.biblionumber=c.biblionumber 
AND i.holdingbranch = <<Branch|branches>>
GROUP BY b.biblionumber
ORDER BY b.title

Fix Subscriptions without Subscription Start Dates

  • Developer: Barton Chittenden
  • Module: Serials
  • Purpose: Fix serials that generate the following error message: Software error: Date::Calc::Add_Delta_YM(): not a valid date at /var/lib/koha/aarome/kohaclone/C4/Serials.pm line 2651.
  • Status: Complete
SELECT 
    CONCAT(
        '<a href="http://',
        (SELECT value 
         FROM systempreferences 
         WHERE variable='staffClientBaseURL'),
        '/cgi-bin/koha/serials/subscription-history.pl?subscriptionid=',
        subscriptionid,
        '">', title, '</a>'
    ) AS 'Edit Subscription for',
    subscriptionid,
    startdate,
    histstartdate
FROM
    subscription
    INNER JOIN biblio USING (biblionumber)
    LEFT JOIN subscriptionhistory USING (subscriptionid) 
    LEFT JOIN serial USING (subscriptionid) 
WHERE
    startdate IS NULL
    OR histstartdate IS NULL
GROUP BY subscriptionid

List of Current Periodicals and their holdings

  • Developer: Joseph Alway
  • Module: Serials
  • Purpose: List current subscriptions.
  • Status: Complete
SELECT 
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
    biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) AS ISSN, GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings
FROM
    subscription
    JOIN biblio ON ( subscription.biblionumber = biblio.biblionumber )
    JOIN biblio_metadata ON ( subscription.biblionumber = biblio_metadata.biblionumber )
    LEFT JOIN serial ON ( subscription.subscriptionid = serial.subscriptionid )
WHERE
    (subscription.closed = 0)
GROUP BY biblio.biblionumber
ORDER BY biblio.title

List of Non Current Periodicals and their holdings

  • Developer: Joseph Alway
  • Module: Serials
  • Purpose: List closed subscriptions.
  • Status: Complete
SELECT 
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
    biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) AS ISSN, GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings
FROM
    subscription
    JOIN biblio ON ( subscription.biblionumber = biblio.biblionumber )
    JOIN biblio_metadata ON ( subscription.biblionumber = biblio_metadata.biblionumber )
    LEFT JOIN serial ON ( subscription.subscriptionid = serial.subscriptionid )
WHERE
    (subscription.closed != 0)
GROUP BY biblio.biblionumber
ORDER BY biblio.title

List of Non Current Periodicals and their holdings *Special Case*

  • Developer: Joseph Alway
  • Module: Serials
  • Purpose: List Holdings information from Marc Record and closed subscriptions. Combines two reports into one.
  • Status: Complete
SELECT 
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    NULL AS subscriptionid, biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) AS ISSN,
    ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) AS Holdings, NULL AS 'S. Holdings'
FROM 
    biblio_metadata
    JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE
    (ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]' ) LIKE "CR")
    AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "%See%Subscriptions%for%More%Details%")
    AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "%Current%issues%")
    AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "")
UNION ALL
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
    biblio.title AS title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) AS ISSN, ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) AS Holdings,
    GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS 'S. Holdings'
FROM
    subscription
    JOIN biblio ON (subscription.biblionumber = biblio.biblionumber)
    JOIN biblio_metadata ON (subscription.biblionumber = biblio_metadata.biblionumber)
    LEFT JOIN serial ON (subscription.subscriptionid = serial.subscriptionid)
WHERE
    (subscription.closed != 0)
GROUP BY biblio.biblionumber
ORDER BY title

Troubleshooting Reports

Find data which will trigger bugs or cause unexpected behavior

Call number sorting issues

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Cataloging
  • Purpose: Show cn_sort, cn_source for the purpose of trouble-shooting shelf sort issues.
  • Status: Complete
  • Note:
SELECT
    title,
    itemcallnumber,
    cn_sort,
    cn_source
FROM
    items
    INNER JOIN biblio USING (biblionumber)
WHERE
    itemcallnumber LIKE <<Callnumber (USE % FOR wildcard, e.g. B1%)>>
ORDER BY cn_sort;

Codes differ in case

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Circulation
  • Purpose: Find codes in borrower_attributes and borrower_attribute_types which differ by case.
  • Status: Complete
  • Note: MySQL is case-insensitive by default, but in the case of codes like borrower_attributes.code or borrower_attribute_types.code, the data is used in a case sensitive way inside Koha. The statement COLLATE utf8_bin can be appended after a field name to force case sensitivity in MySQL:
SELECT 
    ba.*,
    bat.code
FROM 
    borrower_attributes ba
    INNER JOIN borrower_attribute_types bat USING (code)
WHERE 
    ba.code COLLATE utf8_bin != bat.code COLLATE utf8_bin

items.onloan does not match issues.date_due

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Circulation
  • Purpose: Find items and issues where items.onloan does not match issues.date_due
  • Status: Complete
  • Note: Koha stores the due date for an item in two locations: items.onloan and issues.date_due. These fields *should* match, but they have been known to get out of sync, which may cause an item to show as both avialable and checked out in search results.
SELECT 
    itemnumber,
    issuedate,
    date_due 
FROM issues JOIN items USING (itemnumber)
WHERE onloan IS NULL

Sort borrower card numbers numerically

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Circulation
  • Purpose: Find borrower card numbers which are out of sequence.
  • Status: Complete
  • Note: When populating borrower card number, Koha will choose the next card number in sequence, numerically. If a card number has been saved to the borrowers table which creates a gap in the borrower numbers, (e.g. 1000, 1001, 1002, 10031003), Koha will populate the next card number based on the highest numerical card number. This query can be used to trouble-shoot that situation.


SELECT cardnumber, 
       borrowernumber 
FROM borrowers 
ORDER BY CAST( cardnumber AS SIGNED INTEGER)

Direct links to systemprefrences

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Administration
  • Purpose: Trouble-shooting sysprefs
  • Status: Complete
  • Note: Sometimes it's nice to have a link to a specific system prefrence. This generates all of them:
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=?variable=', variable, '\">', variable, '</a>' ) AS 'Syspref'
FROM systempreferences


Show last queued message in message_queue by letter code

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Notices
  • Purpose: Lets you see if a particular letter_code stopped queueing -- could tell you if advanced notices or overdue notices is borked.
  • Status: Complete
SELECT
    max(date(time_queued)) AS 'last sent',
    letter_code
FROM message_queue
GROUP BY letter_code
ORDER BY max(time_queued)

Search for saved report by partial report name

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Reports
  • Purpose: Look up report number and name by partial report name
  • Status: Complete
SELECT
    id,
    report_name
FROM
    saved_sql
WHERE
    report_name LIKE CONCAT('%' , <<Part of report name>> , '%')

Search for saved report by part of query

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Reports
  • Purpose: Look up report number and name by part of the sql
  • Status: Complete
SELECT
    id,
    report_name
FROM
    saved_sql
WHERE
    savedsql LIKE CONCAT('%' , <<Part of saved SQL>> , '%')

Misc Reports

Most Popular Searches

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Search
  • Purpose: Find popular search terms
  • Status: Complete
SELECT COUNT(*) AS Count,
       RIGHT(
              query_desc, 
              Length(query_desc) - Instr(query_desc, ':')
       ) AS 'Search string'
FROM   search_history
GROUP  BY RIGHT(query_desc, Length(query_desc) - Instr(query_desc, ':'))
ORDER  BY COUNT(*) DESC

Show number of items available

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Circulation
  • Purpose: Show counts of available items
  • Status: Incomplete
  • Notes: This is roughly the same logic used in search results.
  • is the item in transit? => not available
  • is it lost? => not available
  • is it damaged? => not available
  • is it withdrawn? => not available
  • is it on hold?
    • Is the item waiting?
      • yes => not avaliable
      • no
        • Is the syspref AllowItemsOnHoldCheckout set?
        • no => not available
  • is it set as 'not for loan'?
    • is the syspref AllowNotForLoanOverride set?
      • no => not available

The AllowItemsOnHoldCheckout logic is too complicated to capture in SQL. The AllowNotForLoanOverride should be fairly straightforward, that still needs to be added.

SELECT
    title,
    SUM(
        IF(
            (
                onloan IS NOT NULL
                OR itemlost > 0
                OR damaged > 0
                OR withdrawn > 0
                OR EXISTS (
                    SELECT *
                    FROM branchtransfers
                    WHERE
                        items.itemnumber = branchtransfers.itemnumber
                        AND branchtransfers.datearrived IS NULL
                )
                OR EXISTS (
                    SELECT *
                    FROM reserves
                    WHERE
                        items.itemnumber = reserves.itemnumber
                        AND reserves.found IN ( 'W', 'T' )
                )
            ),
            0,
            1
          )
    ) AS 'Copies available',
    count(*) AS 'Total copies'
FROM
    items
    INNER JOIN biblio USING (biblionumber)
    LEFT JOIN reserves USING (itemnumber)
    LEFT JOIN branchtransfers USING (itemnumber)
GROUP BY biblio.biblionumber

List of Lists

  • Developer: Nicole C. Engard, ByWater Solutions
  • Module: Lists
  • Purpose: Show all lists with their creator
  • Status: Complete
SELECT b.borrowernumber, b.surname, b.firstname, 
       s.shelfname 
FROM virtualshelves s 
LEFT JOIN borrowers b 
ON (b.borrowernumber=s.owner)

List's content

  • Developer: Abdullrahman Hegazy
  • Module: Lists
  • Purpose: Show the content of One list, 'You can find the shelf number in the address bar after viewshelf='NUM'.
  • Status: Complete
SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>') AS Title,author
FROM biblio,virtualshelfcontents
WHERE biblio.biblionumber=virtualshelfcontents.biblionumber
AND shelfnumber=<<Enter Shelf number>>

Backup/Share OPAC Layout from system preferences

  • Developer: Liz Rea, NEKLS
  • Module: Administration
  • Purpose: Dump the contents of all of the OPAC Interface user input customization preferences, for backup or sharing of layout/CSS
  • Status: Complete
SELECT variable, value 
FROM systempreferences 
WHERE variable IN (
     'OPACUserCSS', 'opacuserjs', 'OPACResultsSidebar', 'OPACNoResultsFound',
     'OpacNav', 'opaccredits','opacheader', 'OpacMainUserBlock'
)

OCLC Number Lookup

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging
  • Purpose: Look up OCLC Number by barcode.
  • Status: Complete

This report assumes that OCLC Numbers are stored in MARC 001 field.

SELECT
    biblionumber
  , ExtractValue( marcxml, '//controlfield[@tag=\"001\"]' ) AS 'OCLC Number'
  , title 
FROM biblioitems 
INNER JOIN biblio USING (biblionumber) 
INNER JOIN items USING (biblionumber) 
WHERE barcode=<<barcode>>

Troubleshoot accidental checkout when action logs are disabled

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Notices
  • Purpose: Troubleshoot accidental checkouts
  • Status: Complete

This report takes a date range of notice queue times, and prints CHECKIN, CHECKOUT and RENEWAL notices ordered by borrowernumber and time queued. If you see cclusters of CHECKOUT, RENEWAL and CHECKIN notices within the same minute for the same borrower, this is an indication of accidental check-out.

Circulation action logs are a better way to monitor this, but they are often disabled for performance reasons.

SELECT 
   borrowernumber, 
   letter_code,
   time_queued,
   content 
FROM message_queue 
WHERE
date(time_queued) BETWEEN <<Notice Queued BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
AND letter_code IN ('CHECKIN', 'RENEWAL', 'CHECKOUT') 
ORDER BY borrowernumber, time_queued

Find all character encodings in MARCXML records

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging
  • Purpose: List all character encodings
  • Status: Complete
SELECT 
    count(*),
    CASE Mid( 
        ExtractValue(marcxml, '//leader'), 
        9, 
        1
    ) 
    WHEN ' ' THEN 'MARC8'
    WHEN 'a' THEN 'UTF8'
    ELSE CONCAT( 
        'Unknown encoding type: ', 
        Mid(
            ExtractValue(marcxml, '//leader'),
            9,
            1
        )
    )
    END AS encoding
FROM biblioitems 
WHERE Mid(
                ExtractValue(marcxml, '//leader'),
                9,
                1
        ) IS NOT NULL
GROUP BY Mid(
                ExtractValue(marcxml, '//leader'),
                9,
                1
        )

According to the MARC21 standard, the 'Leader' character position 9 determines character encoding: '#' for MARC8, 'a' for UTF8. Koha uses ' ' instead of '#' for MARC8.


OAI-PMH Test URL

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Show URLs for OAI-PMH testing
  • Status: Complete
SELECT
    concat(
        opacbaseurl.value,
        '/cgi-bin/koha/oai.pl?verb=GetRecord&identifier=',
        archiveID.value,
        ':',
        biblionumber,
        '&metadataPrefix=marcxml'
    ) AS 'OAI-PMH URL',
    biblionumber,
    title
FROM
    biblio, systempreferences AS opacbaseurl, systempreferences AS archiveID
WHERE
    opacbaseurl.variable = 'opacbaseurl'
    AND archiveID.variable = 'OAI-PMH:archiveID'

Titles with changes still pending in zebraqueue

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Cataloging, Circulation
  • Purpose: Show which titles have changes that have not yet been picked up by zebra
  • Status: Complete
SELECT 
    biblio_auth_number AS biblionumber,
    title,
    time AS 'time queued'
FROM
    zebraqueue
    INNER JOIN biblio ON (zebraqueue.biblio_auth_number = biblio.biblionumber)
WHERE
    done=0
    AND server='biblioserver'
    AND operation = CONCAT( 'specialU', 'pdate' )
ORDER BY
    time

In the where clause, operation = CONCAT( 'specialU', 'pdate' ) is necessary because the keyword 'Update' is rejected in reports.

Circulation logs by borrower, select item barcode

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Circulation
  • Purpose: Scan a barcode, and get links to the circulation logs for all borrowers who have checked that item out.
  • Status: Complete
SELECT 
    borrowernumber, 
    cardnumber,
    CONCAT(
        firstname, ' ', surname, 
        ' (', 
        '<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&object=', 
        borrowernumber,
        '\">', 'Circulation log' , '</a>', 
        ')'
    ) AS Patron
FROM
    borrowers
    INNER JOIN statistics USING (borrowernumber)
    INNER JOIN items USING (itemnumber)
WHERE
    barcode = <<Item barcode>>
    AND statistics.type='issue'
GROUP BY
    borrowernumber

Show how borrowers were deleted (select date range)

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Find out who deleted borrowers, and when.
  • Status: Complete
SELECT
    timestamp,
    CONCAT( staff.firstname, ' ', staff.surname ) AS 'Staff', 
    CONCAT( db.firstname, ' ', db.surname ) AS 'Patron', 
    db.cardnumber
FROM
    deletedborrowers db 
    INNER JOIN action_logs ON db.borrowernumber = action_logs.object 
    LEFT JOIN borrowers AS staff ON action_logs.user = staff.borrowernumber
WHERE
    DATE( action_logs.timestamp ) BETWEEN <<patron deleted BETWEEN|date>> AND <<and|date>>
    AND action_logs.module = 'MEMBERS'
    AND action_logs.action = concat( 'DEL', 'ETE' )
ORDER BY action_logs.timestamp

Workaround for Bug 18611

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Find valid barcodes in import records batch
  • Status: Complete
SELECT
    barcode
FROM
    import_batches
    INNER JOIN import_records USING (import_batch_id) 
    INNER JOIN import_items USING (import_record_id) 
    INNER JOIN items USING (itemnumber)
WHERE
    import_batch_id = <<Batch number>>

Show thumbnails of local cover images

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Show thumbnails of local cover images
  • Status: Complete
SELECT 
    b.title AS Title,
        CONCAT(
        '<img src=\"',
        IF(
            CHAR_LENGTH(systempreferences.value),
            CONCAT(systempreferences.value), 
            ''
        ),
        '/cgi-bin/koha/opac-image.pl?thumbnail=1&biblionumber=',
        b.biblionumber, '/">'
    ) AS Image
FROM systempreferences, biblio AS b INNER JOIN biblioimages USING (biblionumber)
WHERE systempreferences.variable='OPACBaseURL'


Show action logs for holds

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Show action logs for holds, select borrower cardnumber
  • Status: Complete
SELECT
    log.timestamp,
    concat( librarian.firstname, ' ', librarian.surname, ' (', librarian.cardnumber, ')' ) AS Librarian,
    log.action,
    log.object,
    log.info,
    log.interface
FROM
    borrowers patron
    INNER JOIN (
        SELECT borrowernumber, reserve_id FROM reserves
    UNION
        SELECT borrowernumber, reserve_id  FROM old_reserves
    ) AS hold USING (borrowernumber)
    INNER JOIN action_logs log ON log.object = hold.reserve_id AND log.module='HOLDS'
    LEFT JOIN borrowers librarian ON log.user = librarian.borrowernumber
WHERE
    patron.cardnumber = <<borrower cardnumber>>
ORDER BY log.timestamp

Dump label creator templates

  • Developer: Barton Chittenden, Bywater Solutions
  • Module: Misc
  • Purpose: Dump label creator templates (ie. page formats)
  • Status: Complete
SELECT
    template_id AS 'Template ID',
    template_code AS 'Template code',
    template_desc AS 'Template description',
    units AS 'Units',
    page_height AS 'Page height',
    page_width AS 'Page width',
    label_width AS 'Label width',
    label_height AS 'Label height',
    top_margin AS 'Top page margin',
    left_margin AS 'Left page margin',
    top_text_margin AS 'Top text margin',
    left_text_margin AS 'Left text margin',
    cols AS 'Number of columns',
    rows AS 'Number of rows',
    col_gap AS 'Gap between columns',
    row_gap AS 'Gap between rows'
FROM
    creator_templates

Library Books Investment by Collection Code

  • Developer: Satisha MV, Govt.Engineering College, Hassan
  • Module: Misc
  • Purpose: To know No. of Titles(Records), No. of Volumes(Items) and Total investment by Collection Code
  • Status: Complete
SELECT  
ccode AS 'Collection Code', 
COUNT(DISTINCT items.biblionumber) AS 'No of Titles',
COUNT(itemnumber) AS 'No of Volumes',
SUM(price) AS 'Investment or Total Expenditure'
FROM items 
GROUP BY ccode

WISHLIST

Requester Module Purpose of request SQL Request Notes
Barton Chittenden Cataloging Item availability I often get requests for reports that show whether an item is available or not. As far as I can tell, this is determined by:
  • is the item in transit? => not available
  • is it lost? => not available
  • is it damaged? => not available
  • is it withdrawn? => not available
  • is it on hold?
    • Is the item waiting?
      • yes => not avaliable
        • no
          • Is the syspref AllowItemsOnHoldCheckout set?
            • no => not available
  • is it set as 'not for loan'?
    • is the syspref AllowNotForLoanOverride set?
      • no => not available
Barton Chittenden Cataloging Get the combined MARCXML data for biblio and item records. Knowing the *size* of the combined record is probably just as important; there are implicit and explicit limits to MARC data size.
Sunil Acquisitions To Eliminate duplicate orders. I want to write a Sql Query to eliminate duplicate orders from selected vender. Please help me in this query.
Sunil Acquisitions List All Late Orders I want to write a Sql Query to List All Late Orders, vendor-wise. Please help me in this query.
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. (Have a look at the report "Biblios with like data in a subfield of a field". Is that what you want?)
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.

Check the following report to see, if that is what you need. Mismatches between 2 fields and 2 subfields: This report displays the Biblionumber, Title, and Author of all Biblios that Do Not Match 2 sets of fields and subfields.

SELECT
    CONCAT(
      '<a href=\"/cgi-bin/koha/catalogue/detail.plbiblionumber=',
      biblio.biblionumber,
      '\">',
      biblio.biblionumber,'</a>'
    ) AS biblionumber,
    biblio.title,
    biblio.author
FROM
    biblioitems
    JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE
    ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 1 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
    AND ExtractValue(marcxml, "//datafield[@tag=<<1st Set Field 2 (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
        NOT LIKE ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 1(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
    AND ExtractValue(marcxml, "//datafield[@tag=<<2nd Set Field 2(XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
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.
SELECT
    barcode, homebranch, title, dateaccessioned, booksellerid
FROM items, biblio
WHERE
    items.biblionumber = biblio.biblionumber
    AND items.dateaccessioned BETWEEN  <<Enter date from>> AND  <<Enter date to>>
    AND booksellerid IS NULL
ORDER BY barcode
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
SELECT COUNT( itemnumber ) 
FROM  issues
Charlotte Stock Catalog List all DVD records with Standard Number at 024 #a Data needed for export I can't find a way to export a list of DVDs showing only their identifier (ie. Standard number at 024 #a). The List tool doesn't allow me to specify the identifier needed, so I presume a report is the way forward?
Personal tools