SQL Reports Library

STOP: Have you heard of mana-kb? it allows you to search and and submit to a shared library of SQL reports directly from Koha!? - Mana docs

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.

SQL Reports Holds | SQL Reports Patrons | SQL Reports Circulation | SQL Reports Accounting

SQL Reports

Tips

Documentation on SQL tables and fields

Here is the Koha 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

-- Patron information screen
CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,

-- Patron notices
CONCAT('<a href=\"/cgi-bin/koha/members/notices.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,

-- Patron files
CONCAT('<a href=\"/cgi-bin/koha/members/files.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,

-- Circulation
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowernumber,'\">', borrowernumber, '</a>') as borrowernumber,

-- Patron accounts and fines
CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',borrowernumber,'\">', borrowernumber, '</a>') as borrowernumber,

-- Patron invoice
CONCAT('<a href=\"/cgi-bin/koha/members/maninvoice.pl?borrowernumber=',borrowernumber,'\" target="_blank">',borrowernumber,'</a>') AS borrowernumber,

-- Patron logs
CONCAT('<a href=\"/cgi-bin/koha/tools/viewlog.pl?do_it=1&modules=CIRCULATION&object=', borrowernumber, '\">', 'Circulation log' , '</a>' ) as borrowernumber,

-- Call patron's phone number
CONCAT('<a href=\"tel:',phone,'\">',phone,'</a>') as phone,

-- Renew patron
CONCAT('<a href=\"/cgi-bin/koha/members/setstatus.pl?borrowernumber=', b.borrowernumber, '&destination=&reregistration=y\" target=\"_blank\">', 'renew', '</a>' ) AS renewal_link,

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

-- Edit item
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">', itemnumber, '</a>' ) AS itemnumber,

-- Edit item, 20.11 and later:
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&itemnumber=', itemnumber, '#edititem', '\">', itemnumber, '</a>' ) AS itemnumber,

-- Item detail
CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?', 'biblionumber=', biblionumber, '&itemnumber=', itemnumber, '\">', itemnumber, '</a>' ) AS itemnumber,

-- Item detail, 20.11 and later:
CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?','&itemnumber=', itemnumber, '\">', itemnumber, '</a>' ) AS itemnumber,

-- Circulation log
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',

Links by report ID

--- view the report
CONCAT('<a href=\"/cgi-bin/koha/reports/guided_reports.pl?reports=' , id , '&phase=Show+SQL', '\">',id,'</a>') AS 'Report number',

--- edit the report
CONCAT('<a href=\"/cgi-bin/koha/reports/guided_reports.pl?reports=', id, '&phase=Edit%20SQL">', id, '</a>' ) AS id

Links by Fund Code

CONCAT('<a href=\"/cgi-bin/koha/admin/aqbudgets.pl?op=add_form&budget_id=', aqbudgets.budget_id, '&budget_period_id=', aqbudgetperiods.budget_period_id, '\">', aqbudgets.budget_id, '</a>' ) AS 'Fund Code',

Links by Accountlines_id

-- Details for fee
CONCAT('<a href=\"/cgi-bin/koha/members/accountline-details.pl?accountlines_id=',accountlines_id,'\">', accountlines_id, '</a>') AS accountlines_id, 
-- Pay a fee
CONCAT('<a href=\"/cgi-bin/koha/members/paycollect.pl?borrowernumber=',borrowernumber,'&pay_individual=1&debit_type_code=',debit_type_code,'&amount=',amount,'&amountoutstanding=',amountoutstanding,'&description=',description,'&title=&itemnumber=&accountlines_id=',accountlines_id,'&payment_note=&remote_user=change_given=','\">',accountlines_id,'</a>') AS accountlines_id,
-- Write off a fee
CONCAT('<a href=\"/cgi-bin/koha/members/paycollect.pl?writeoff_individual=1&borrowernumber=',borrowernumber,'&debit_type_code=LOST&amount=',amount,'&amountoutstanding=',amountoutstanding,'&description=',description,'&itemnumber=&accountlines_id=',accountlines_id,'&payment_note=','\">',accountlines_id,'</a>') AS accountlines_id,

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 metadata
    FROM biblio_metadata
    WHERE
      biblionumber=14),
      '//datafield[@tag="952"]/subfield[@code>="a"]') AS ITEM;

or the equivalent

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

return the entire 952 data for all 952 fields for biblionumber 14 (without delimiting).

 
SELECT 
  ExtractValue((
    SELECT metadata 
    FROM biblio_metadata 
    WHERE biblionumber=14),
      '//datafield[@tag="260"]/subfield[@code="b"]') AS PUBLISHER;

returns the 260$b data for biblionumber 14.

 
SELECT biblionumber, ExtractValue(metadata, 'count(//datafield[@tag="505"])') AS count505 
FROM biblio_metadata
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(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE', 
       title 
FROM biblio_metadata 
INNER JOIN biblio USING (biblionumber) 
WHERE biblionumber = 14

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

Note that ExtractValue concatenates multiple tags into a single field. These can be individually addressed using an array index after [@tag="856"]:

ExtractValue( metadata, '//datafield[@tag="856"][1]/subfield[@code="u"]' ) as '856$u'

The index is 1-based, [@tag="856"][1] is the first tag, [@tag="856"][2] is the second, etc.

Here's a bit of shell script that will generate the first 10 values of 650$a:

for i in {1..10}; do
echo "ExtractValue(metadata,"\
"'//datafield[@tag=\"650\"][$i]"\
"/subfield[@code=\"a\"]') AS 'subjects $i',"
done

Here's the output:

ExtractValue(metadata,'//datafield[@tag="650"][1]/subfield[@code="a"]') AS 'subjects 1',
ExtractValue(metadata,'//datafield[@tag="650"][2]/subfield[@code="a"]') AS 'subjects 2',
ExtractValue(metadata,'//datafield[@tag="650"][3]/subfield[@code="a"]') AS 'subjects 3',
ExtractValue(metadata,'//datafield[@tag="650"][4]/subfield[@code="a"]') AS 'subjects 4',
ExtractValue(metadata,'//datafield[@tag="650"][5]/subfield[@code="a"]') AS 'subjects 5',
ExtractValue(metadata,'//datafield[@tag="650"][6]/subfield[@code="a"]') AS 'subjects 6',
ExtractValue(metadata,'//datafield[@tag="650"][7]/subfield[@code="a"]') AS 'subjects 7',
ExtractValue(metadata,'//datafield[@tag="650"][8]/subfield[@code="a"]') AS 'subjects 8',
ExtractValue(metadata,'//datafield[@tag="650"][9]/subfield[@code="a"]') AS 'subjects 9',
ExtractValue(metadata,'//datafield[@tag="650"][10]/subfield[@code="a"]') AS 'subjects 10',

If there happen to be 11 subjects rather than 10, you're out of luck. Also, note that there's a trailing comma after "AS 'subjects 10',", which will cause a syntax error if you have a WHERE clause directly after this list.

If you want to find all records with 7XX tags, you can do this:

SELECT
    biblionumber,
    ExtractValue(metadata,'//datafield/@tag') AS tags
FROM
    biblio_metadata
WHERE
    ExtractValue(metadata,'//datafield/@tag') REGEXP '7[0-9][0-9]';

Getting records based on their subfield values

Here is how to get the records that have a subfield of 041 containing "spa"

SELECT biblionumber
  FROM biblio_metadata
 WHERE ExtractValue(metadata,'//datafield[@tag="041"]/*') LIKE '%spa%';

Here is how to get the records whose 041$a is "spa"

SELECT biblionumber
  FROM biblio_metadata
 WHERE ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="a"]') = 'spa';

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

Using font-awesome fonts

Green checks and red Xs can be added using Font-awesome fonts and unicode characters:

select
   IF(
     biblionumber % 2 = 0,
    '<i style= "color:green" class="fa fa-check">',
    '<i style="color:red">✖</i>'
    ) as even,
    biblionumber
FROM
    biblio

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 with 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	CREATE, CANCEL, DELETE, MODIFY	reserve_id	Data dumper of reserves?
LetterLog	?	?	?	?
CronjobLog	CRONJOBS	RUN	NULL	cron script name with full path.

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.

Syntax

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

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.
If you are using a predefined runtime parameter the "Question to ask" portion of the parameter is required.

Runtime parameter options
Parameter	Syntax	Usage
Custom input	<<Enter any text string>>	Show an input field labeled with your custom text
[authorized value category], e.g. "CCODE"	CCODE>>	Select any authorized value in the selected category
biblio_framework	biblio_framework>>	Select a bibliographic framework
branches	branches>>	Select a library
categorycode	categorycode>>	Select a patron category
cn_source	cn_source>>	Select a source of classification or shelving scheme
date	date>>	Creates a date input field with a datepicker widget
itemtypes	itemtypes>>	Select an item type
list	list>>	Allows input of a list of values to be passed to an "IN" clause in the SQL

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

Working with Headers

Sometimes you want your report to include headers. Simple example:

select 'borrower number', 'card number', 'last name'
union all
select borrowernumber, cardnumber, surname from borrowers;

However, if you have the report setup to recur via a cron job this will cause reports to always have data so you will get a report emailed to you even if there is nothing in the data query. You can address this by making the headers conditional. To do this you need to do two things.

1) You have to give it a single row source. If you don't you could end up with as many headers as rows in the source.

2) You need to set a condition to test under which it will supply the headers. In most cases that probably means taking the entirety of the second query and making a subquery of it to test so that it only supplies the headers if the data query would supply results.

Example:

select 'borrower number', 'card number', 'last name' from (select count(*) as c from borrowers) b where c > 0
union all
select borrowernumber, cardnumber, surname from borrowers;

If you are on MariaDB 10.2 or above you can simplify it with a CTE:

with patrons as (select * from borrowers),
patron_count as (select count(*) as c from patrons)
select 'borrower number', 'card number', 'last name' from patron_count where c > 0
union all
select borrowernumber, cardnumber, surname from patrons;

Adding Row Numbers to Output

Sometimes you want your report to include row numbers. To do so, add the following to the SELECT statement:

SELECT ROW_NUMBER() OVER () AS num, [...]

Including a Random Sample in Output

Sometimes you want your report to include a random sample from a results set. To do so, add the following to the bottom of your query:

ORDER BY RAND()
LIMIT 50

Note for single branch libraries

Many of the reports below have a branch picker, which looks something like this:

...
WHERE
    homebranch = <<Pick your branch|branches>>
...

If your library only has one branch, this is unnecessary -- every patron, item, hold, checkout (issue), etc. will always be in the same branch, so there's no point in forcing the person running the query to choose.

So

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

can be changed to

SELECT * 
FROM items 
WHERE
    barcode like CONCAT( '%', <<Partial barcode value here>>, '%')

(Note that statements in the WHERE clause must be separated by AND or OR -- when removing the first statement, in a WHERE clause, you need to remove the following AND on the next line)

Runtime Variables

Note: in 18.05 forward, duplicated run time parameters will only have a single prompt; you don't have to use the '@VARIABLE:=' syntax.

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(m.metadata,'//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 biblio_metadata m 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.

Custom Sort Order

The following approach uses a runtime parameter to set the sort order for a report.

Note that users will have to be prompted with the specific sort order options available to them. Better would be if the user could select from a list of options (as with authorized values), but that does not appear to be possible.

There may also be a way to move the sort order selection out of the SELECT statement so that it isn't repeated in the report output.

The ELSE clause sets the default sort order.

SELECT b.biblionumber, b.title, b.author, i.itemcallnumber, i.price, @SortOrder := << Sort ORDER (bib,title,author) >> AS c1
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
ORDER BY 
        CASE 
           WHEN @SortOrder= 'bib' THEN b.biblionumber
           WHEN @SortOrder= 'title' THEN b.title
           WHEN @SortOrder= 'author' THEN b.author
           ELSE i.itemcallnumber
        END

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)

Course Reserves Reports

All course reserve items

Developer: Nicole C. Baratta, 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)

Course reserve items with circulation counts by term

Developer: Joe Sikowitz, Fenway Library Organizatoin
Module: Course Reserves
Purpose: To show items on course reserve during a given term and how often they have circulated.
Status: Completed
Works with: 18.05

SELECT
  i.barcode AS 'Barcode',
  b.title AS 'Title',
  i.itemcallnumber AS 'Call #',
  i.itype AS 'Item Type',
  c.term AS 'Term',
  c.department AS 'Department',
  c.course_name AS 'Course Name',
  c.course_number AS 'Course Number',
  bo.surname AS 'Instructor',
  COUNT(st.datetime) AS 'Circ Total'
FROM course_items ci
  LEFT JOIN (
    SELECT
      s.itemnumber,
      s.datetime
    FROM statistics s
    WHERE
      s.datetime BETWEEN <<Circ start date|date>> AND
      <<Circ end date|date>> AND s.type='issue') AS st ON (st.itemnumber = ci.itemnumber)
  LEFT JOIN items i ON (i.itemnumber = ci.itemnumber)
  LEFT JOIN biblio b ON (i.biblionumber = b.biblionumber)
  LEFT JOIN course_reserves cr ON (cr.ci_id = ci.ci_id)
  LEFT JOIN courses c ON (c.course_id = cr.course_id)
  LEFT JOIN course_instructors ins ON ( ins.course_id = c.course_id)
  LEFT JOIN borrowers bo ON (bo.borrowernumber = ins.borrowernumber)
WHERE
  i.homebranch LIKE <<Select school|library_group>> AND c.term LIKE <<Select term|term>>
GROUP BY
  ci.itemnumber
ORDER BY
  c.department,
  c.term,
  bo.surname,
  c.course_number

Catalog/Bibliographic Reports

Catalog Size

Record and Item Counts

Count of all items

Developer: Michael Hafen
Module: Catalog
Purpose: Count of all items with barcodes
Status: Complete

  SELECT COUNT(barcode) AS Count FROM items WHERE barcode <> '' AND barcode IS NOT NULL

Count of all titles

Developer: Michael Hafen
Module: Catalog
Purpose: Count of all titles
Status: Complete

  SELECT COUNT(biblio.title) AS Count FROM biblio

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

Total Collection Size by Date

Developer: Nicole C. Baratta, 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

Count of items in collection, select date range

Developer: Barton Chittenden, ByWater Solutions
Module: Catalog
Purpose: Show collection by item type. Includes deleted bibs and items.
Status: Complete

SELECT
    COALESCE( i.itype, di.itype ) as itype,
    COUNT(*)
FROM
    (select biblionumber from biblio union select biblionumber from deletedbiblio ) b
    left join items i using (biblionumber)
    left join deleteditems di using (biblionumber)
WHERE
    ( i.timestamp is not NULL OR  date(di.timestamp) >= <<item present between | date>> )
    AND date( COALESCE( i.dateaccessioned, di.dateaccessioned ) ) <= <<and | date>>
GROUP BY
    itype
ORDER BY
    itype

By Age

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

Average Age by Collection Code

Developer: Nicole C. Baratta, 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>>

Average age of collection by item type

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Average age of collection by item type uses the 008 date field
Status: Complete

SELECT itype, avg(mid(ExtractValue(metadata,'//controlfield[@tag="008"]'),8,4))
FROM biblio_metadata
LEFT JOIN items USING (biblionumber)
WHERE SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'),8,4) REGEXP '[0-9]{4}'
GROUP BY itype

By Location

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

Count of all Bibs and Items per Branch

Developer: Nicole C. Baratta, 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

Statistical Count of total number of items held by each branch

Developer: Nicole C. Baratta, 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

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 items in a location

Developer: Nicole C. Baratta, 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

Other Breakdowns

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

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

Count of biblios on each OAI set

Developer: Pablo Bianchi
Module: Catalog
Status: Complete

SELECT
  oai_sets.name AS "Set",
  COUNT(oai_sets_biblios.biblionumber) AS "Count"
FROM oai_sets_biblios
LEFT JOIN oai_sets ON (oai_sets.id=oai_sets_biblios.set_id) 
GROUP BY oai_sets.name
ORDER BY oai_sets.name 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

Percentage of collection by collection code

Developer: Nicole C. Baratta, 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

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

List of All Unique Items at a Branch

Developer: Christofer Zorn
Module: Catalog
Purpose: A listing of all titles where only one item exists in the system and at the specified branch, nowhere else in the system. Items unique to the chosen branch (excludes withdrawn and lost items).
Status: Complete

SELECT
  biblio.title AS Title,
  biblio.author AS Author,
  items.barcode AS Barcode,
  items.dateaccessioned AS DateAccessioned,
  items.datelastborrowed AS DateLastBorrowed,
  items.homebranch AS HomeBranch,
  items.itype AS IType,
  items.ccode AS CollectionCode,
  items.location AS Location
FROM biblio
INNER JOIN items USING (biblionumber)
WHERE
  items.withdrawn = 0
  AND items.itemlost = 0
GROUP BY biblio.biblionumber
HAVING 
  COUNT(1) = 1 
  AND items.homebranch = <<Branch|branches>>

Inventory/ Shelflists

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

Updated 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
Updated: Feb 13, 2021

SELECT 
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio_metadata.biblionumber,'\">',biblio_metadata.biblionumber,'</a>') 
AS biblionumber,
items.barcode as Barcode,
items.dateaccessioned as AccDate, 
items.itemcallnumber as CallNo, 

ExtractValue(metadata,'//datafield[@tag="020"]/subfield[@code="a"]') as ISBN,
ExtractValue(metadata,'//datafield[@tag="100"]/subfield[@code="a"]') as Author,
ExtractValue(metadata,'//datafield[@tag="700"]/subfield[@code="a"]') as OtherAuthors,
ExtractValue(metadata,'//datafield[@tag="710"]/subfield[@code="a"]') as CorporateAuthor,
ExtractValue(metadata,'//datafield[@tag="245"]/*') as Title,
ExtractValue(metadata,'//datafield[@tag="250"]/subfield[@code="a"]') as Ed,
ExtractValue(metadata,'//datafield[@tag="260"]/subfield[@code="c"]') as Year,
ExtractValue(metadata,'//datafield[@tag="260"]/subfield[@code="a"]') as Place,
ExtractValue(metadata,'//datafield[@tag="260"]/subfield[@code="b"]') as Pub,
ExtractValue(metadata,'//datafield[@tag="300"]/*') as PhysicalDesc,
ExtractValue(metadata,'//datafield[@tag="650"]/*') as Subject


FROM biblio_metadata
LEFT JOIN items ON (biblio_metadata.biblionumber=items.biblionumber)
WHERE items.homebranch =<<Branch|branches>> 
ORDER BY LPAD(items.barcode,40,' '), biblio_metadata.biblionumber 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>>

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

Complete Shelflist

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

Inventory Report

Developer: Sher Afzal Khan (Kohapakistan@gmail.com)
Module: Catalog
Purpose: Subject wise list of books
Status: Complete

SELECT  items.itemnumber,items.biblionumber,items.barcode,items.price,items.holdingbranch,items.ccode,items.itype,
biblioitems.biblionumber,biblioitems.isbn,biblio.author,items.stack,items.location,items.
permanent_location,items.ccode,biblio.title,biblio.author
FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)

Inventory Report

Developer: Nicole C. Baratta, 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. Baratta, 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

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

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)

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

Catalog without withdrawn items

Developer: Asif Nawab
Module: Catalog
Purpose: Accession Register without withdraw items.
Status: Completed

SELECT items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,
biblioitems.publishercode,biblioitems.place,biblio.copyrightdate,items.itype,items.ccode,items.price,items.withdrawn
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.withdrawn != 1
ORDER BY items.itype ASC

Accession Register Report by Branch

Developer: Mahesh Palamuttath
Module: Catalog
Purpose: Getting details of all books under a specific branch library.
Status: Completed

SELECT  items.barcode,items.itemcallnumber,items.itype,items.ccode,items.location,biblioitems.isbn,biblio.author,biblio.title,biblio.subtitle,biblioitems.editionstatement,biblioitems.place,biblioitems.publishercode,biblio.copyrightdate,biblioitems.pages,items.price,items.enumchron,items.dateaccessioned FROM items 
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) where items.homebranch =<<Choose library|branches>>

New Arrivals by Branch

Developer: Mahesh Palamuttath
Module: Catalog
Purpose: Get the details of newly added books under a specific branch library (can also be used for OPAC Coverflow)
Status: Completed

SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS 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 != ''
AND i.homebranch = <<Branch|branches>>
GROUP BY biblionumber
HAVING isbn != ""
ORDER BY rand()
LIMIT 30

Changes to the Catalog

New Records and Items

Bibs Marked On Order

Developer: Nicole C. Baratta, 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

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

Developer: Katrin Fischer and Nicole C. Baratta, 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

Items Added in Date Range

Developer: Nicole C. Baratta, 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

Items Deleted in Date Range at Branch

Developer: Nicole C. Baratta, 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>>

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

List of bibliographic record numbers of bibs with items On Order

Developer: Heather Hernandez
Module: Catalog
Purpose: List bib numbers of on order bib records to facilitate export of bib records
Status: Complete

   SELECT b.biblionumber
   FROM biblio b 
   LEFT JOIN items i using (biblionumber)where i.notforloan = '-1'
   ORDER BY b.biblionumber

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

List of biblio by item type

Developer: Jameela P; NRL Panini
Module: Catalog
Purpose: This report lists the biblio items by its item type. (includes bibliographic info)
Status: Complete

  SELECT
    items.barcode, items.dateaccessioned, items.booksellerid, items.homebranch, items.price, items.replacementprice,  items.itemcallnumber,  items.holdingbranch,  biblio.title, biblio.author,  biblioitems.isbn

FROM items 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 

WHERE items.itype=<<Itemtype|itemtypes>>GROUP BY items.barcode ORDER BY items.barcode asc

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 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 Catalogue records Modified/Added/Deleted by Staff

Developer: Vinod Kumar Mishra
Module: Catalog
Purpose: It gives a list of records modified/added/deleted in a data range by library staff.
Status: Complete
Note: Catalog Log should be activated in Koha System preferences

SELECT concat(p.firstname, ' ', p.surname) 'Staff Name',p.cardnumber 'Staff Card Number',a.action'Action',a.timestamp'Time',a.object 'Record No',a.info 'Description'

FROM action_logs a
LEFT JOIN borrowers p ON (a.user=p.borrowernumber)
LEFT JOIN items c ON (a.info=c.itemnumber)
LEFT JOIN biblio d ON (c.biblionumber=d.biblionumber)
LEFT JOIN borrowers k ON (a.object=k.borrowernumber)
where a.timestamp BETWEEN <<Date Between (dd/mm/yyyy)|date>> AND <<and (dd/mm/yyyy)|date>> AND a.module='CATALOGUING'

Another new items report

Developer: Nicole C. Baratta, 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)

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' if you just want 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(bm.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",
    ExtractValue(bm.metadata, '//datafield[@tag="245"]/subfield[@code="n"]') AS "Part name",
    ExtractValue(bm.metadata, '//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)
LEFT JOIN biblio_metadata bm ON (al.object = bm.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

New Bib Records between dates

Developer: Nicole C. Baratta, 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)

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

Developer: Jane Wagner, PTFS
Module: Catalog
Purpose: Previous Month Items Deleted
Status: Complete

SELECT count(deleteditems.itemnumber) as ItemsDeleted 
FROM deleteditems 
WHERE deleteditems.timestamp like concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%'))

Previous Month Items 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

Titles added in date range using 005

Developer: Nicole C. Baratta, 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
left 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')

7 Random new titles for coverflow plugin

Developer: Ed Veal and Nicole C. Baratta, 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

New items added since selected date with cover images

Developer: Jason Robb
Module: Catalog
Purpose: Gathers items since a specific accession date, normalizes the ISBN and tries to render images from Amazon within the report results
Status: Complete
Version: 21.05

SELECT
    items.dateaccessioned,
    items.barcode,
    items.itemcallnumber,
    biblio.author,
    concat(biblio.title, ' ', ExtractValue((
        SELECT metadata 
        FROM biblio_metadata
        WHERE biblio.biblionumber = biblio_metadata.biblionumber),
         '//datafield[@tag="245"]/subfield[@code="b"]')) AS FullTitle,
    biblioitems.isbn,
    biblio.copyrightdate,
    ExtractValue( biblio_metadata.metadata, '//datafield[@tag="650"]/subfield[@code="a"]' ) AS 'lcsh',
    COALESCE(CONCAT('<img src="https://images-na.ssl-images-amazon.com/images/P/',
  IF
  (LEFT(TRIM(biblioitems.isbn), 3) = '978',
    CONCAT(SUBSTR(TRIM(biblioitems.isbn), 4, 9),
    REPLACE(MOD(11 - MOD
        (CONVERT(SUBSTR(TRIM(biblioitems.isbn), 4, 1), UNSIGNED INTEGER)*10 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 5, 1), UNSIGNED INTEGER)*9 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 6, 1), UNSIGNED INTEGER)*8 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 7, 1), UNSIGNED INTEGER)*7 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 8, 1), UNSIGNED INTEGER)*6 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 9, 1), UNSIGNED INTEGER)*5 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 10, 1), UNSIGNED INTEGER)*4 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 11, 1), UNSIGNED INTEGER)*3 +
         CONVERT(SUBSTR(TRIM(biblioitems.isbn), 12, 1), UNSIGNED INTEGER)*2, 11
	  ), 11), '10', 'X')
	),
    LEFT(TRIM(biblioitems.isbn), 10)
  ),
     '.01.MZZZZZZZZZ.jpg">')) AS Render  
FROM biblio, items, biblioitems, biblio_metadata
WHERE biblio.biblionumber = items.biblionumber 
     AND biblio.biblionumber = biblio_metadata.biblionumber
     AND biblioitems.biblionumber = biblio.biblionumber
     AND items.dateaccessioned  >= <<Items added on or after (yyyy-mm-dd)|date>>
     AND items.homebranch LIKE <<Choose Library|branches>>
     AND biblio.copyrightdate >= <<Titles published on or after (YYYY), or a % symbol for no limit>>
GROUP BY FullTitle
ORDER BY items.itemcallnumber, biblio.copyrightdate DESC, items.itemcallnumber
LIMIT 10000

Deletions, Withdrawals, and Lost Items

All bibs where last item deleted

Developer: Nicole C. Baratta, 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. Baratta, 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>>)

All bibs where last item withdrawn in time frame

Developer: Fridolin Somers, Biblibre
Module: Catalog
Purpose: All bibs where the last item was withdrawn in a specific timeframe (consider deleting the record)
Status: Complete

SELECT
  CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'">Notice</a>') AS 'Notice',
  b.title AS 'Titre',
  b.author AS 'Auteur',
  MIN(i.withdrawn_on) AS 'Pilon depuis',
  COUNT(i.itemnumber) AS 'Nb exemplaires'
FROM items i JOIN biblio b ON(i.biblionumber = b.biblionumber)
WHERE b.biblionumber IN (
  SELECT biblionumber FROM items GROUP BY biblionumber
  HAVING COUNT(itemnumber) > 0 AND COUNT(IF(withdrawn = 1,1,NULL)) = COUNT(itemnumber)
)
AND i.homebranch LIKE <<Site|branches:all>>
AND IF(i.withdrawn_on,DATE(i.withdrawn_on),'2000-01-01') BETWEEN <<Piloné entre|date>> AND <<et|date>>
GROUP BY i.biblionumber
ORDER BY i.withdrawn_on DESC

Bib records added/deleted in time frame

Developer: Nicole C. Baratta, 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

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

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

Deleted Titles List to Send to OCLC

Developer: Nicole C. Baratta, 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(bm.metadata, '//controlfield[@tag="001"]') AS 'OCLC Number 001'
FROM biblio b
LEFT JOIN deleteditems i USING (biblionumber)
LEFT JOIN biblioitems m USING (biblionumber)
INNER JOIN biblio_metadata bm on bm.biblionumber = b.biblionumber and bm.format = 'marcxml'
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>>

Item records added/deleted in time frame

Developer: Nicole C. Baratta, 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

Deleted biblio data

Developer: Alex Buckley, Catalyst IT for Toi Ohomai Institute of Technology, New Zealand
Module: Catalog
Purpose: Given a biblio title this report will list information the biblio if it has been deleted.
Status: Completed

SELECT
  deletedbiblio.title,
  deletedbiblio.biblionumber,
  deletedbiblio.author,
  deletedbiblio.frameworkcode,
  deletedbiblio.unititle,
  deletedbiblio.notes,
  deletedbiblio.seriestitle,
  deletedbiblio.copyrightdate,
  deletedbiblio.timestamp,
  deletedbiblio.datecreated,
  deletedbiblio.abstract,
  deletedbiblio_metadata.format,
  deletedbiblio_metadata.metadata
FROM
  deletedbiblio
  LEFT JOIN deletedbiblio_metadata ON deletedbiblio.biblionumber = deletedbiblio_metadata.biblionumber
WHERE
  deletedbiblio.title = <<Enter a title>>

List of items associated with a deleted biblio

Developer: Alex Buckley, Catalyst IT for Toi Ohomai Institute of Technology, New Zealand
Module: Catalog
Purpose: Finds all the item(s) associated with a deleted biblio.
Status: Completed

SELECT
  deletedbiblioitems.biblioitemnumber AS itemnumber,
  deleteditems.barcode,
  deletedbiblioitems.volume,
  deletedbiblioitems.number,
  deletedbiblioitems.isbn,
  deletedbiblioitems.issn,
  deletedbiblioitems.ean,
  deletedbiblioitems.publicationyear,
  deletedbiblioitems.publishercode,
  deletedbiblioitems.volumedate,
  deletedbiblioitems.volumedesc,
  deletedbiblioitems.collectiontitle,
  deletedbiblioitems.collectionvolume,
  deletedbiblioitems.editionstatement,
  deletedbiblioitems.editionresponsibility,
  deletedbiblioitems.illus,
  deletedbiblioitems.pages,
  deletedbiblioitems.notes,
  deletedbiblioitems.size,
  deletedbiblioitems.place,
  deletedbiblioitems.lccn,
  deletedbiblioitems.url,
  deletedbiblioitems.cn_source,
  deletedbiblioitems.cn_class,
  deletedbiblioitems.cn_item,
  deletedbiblioitems.cn_suffix,
  deletedbiblioitems.cn_sort,
  deletedbiblioitems.agerestriction,
  deletedbiblioitems.totalissues,
  deleteditems.dateaccessioned,
  deleteditems.booksellerid,
  deleteditems.homebranch,
  deleteditems.price,
  deleteditems.replacementprice,
  deleteditems.replacementpricedate,
  deleteditems.datelastborrowed,
  deleteditems.datelastseen,
  deleteditems.stack,
  deleteditems.notforloan,
  deleteditems.damaged,
  deleteditems.itemlost,
  deleteditems.itemlost_on,
  deleteditems.withdrawn,
  deleteditems.withdrawn_on,
  deleteditems.itemcallnumber,
  deleteditems.coded_location_qualifier,
  deleteditems.issues,
  deleteditems.renewals,
  deleteditems.reserves,
  deleteditems.restricted,
  deleteditems.itemnotes,
  deleteditems.itemnotes_nonpublic,
  deleteditems.holdingbranch,
  deleteditems.paidfor,
  deleteditems.location,
  deleteditems.permanent_location,
  deleteditems.onloan,
  deleteditems.ccode AS Collection,
  deleteditems.materials,
  deleteditems.uri,
  deleteditems.more_subfields_xml,
  deleteditems.enumchron,
  deleteditems.copynumber,
  deleteditems.stocknumber,
  deleteditems.new_status
FROM
  deletedbiblio
  LEFT JOIN deletedbiblio_metadata ON deletedbiblio.biblionumber = deletedbiblio_metadata.biblionumber
  LEFT JOIN deleteditems ON deletedbiblio_metadata.biblionumber = deleteditems.biblionumber
  LEFT JOIN deletedbiblioitems ON deletedbiblio_metadata.biblionumber = deletedbiblioitems.biblionumber
WHERE
  deletedbiblio.title = <<Enter a title>>

List of Items Marked Lost/Missing

Developer: Nicole C. Baratta, 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 with Hold Info

Developer: Nicole C. Baratta, 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 with 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>>

Lost Items & Who Lost Them

Developer: Nicole C. Baratta, 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'

Withdrawn Items (with 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. Baratta, 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. Baratta, 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

Withdrawn Titles List to Send to OCLC

Developer: Nicole C. Baratta, 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

Lists of Records and Items

Records with Specific Attributes

Bibs with a specific MARC field

Developer: Cab Vinton
Module: Catalog
Purpose: This report lists all biblionumbers for records containing a specific MARC field
Status: Complete

SELECT
    biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' ) AS link
FROM
    biblio_metadata
WHERE
    ExtractValue(metadata,'//datafield/@tag') REGEXP <<MARC field>>

Bibs with a specific MARC field II

Developer: Pablo López Liotti - UNMDP - (based in previous Cab Vinton's work)
Module: Catalog
Purpose: Lists biblionumbers with links to records containing a specific MARC field given and field content (first ocurrence and all subfields) extracted from XML Marc.
Status: Complete
Version: 17.xx to 20.xx

SELECT
    CONCAT('<a target="_blank" title="Show record with THIS biblionumber" 
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
biblionumber, '\">', biblionumber, '</a>' ) AS Record,
    ExtractValue(metadata, "//datafield[@tag=<<MARC Field>>]/*") AS Field_Content
FROM
    biblio_metadata
WHERE
    ExtractValue(metadata,'//datafield/@tag') REGEXP <<MARC Field>>

All titles with 008 for Continuing Resource

Developer: Nicole C. Baratta, 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')

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author, ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AS 'Μεταβλητή'
FROM biblio_metadata
JOIN biblio USING (biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

Biblios Leader position 06 and 942 subfield c

Developer: Elaine Bradtke
Module: Catalog
Purpose: This report displays the Biblionumber and contents of leader 06, selected by item type from 942$c, useful for finding mismatch in Leader and item type.
Status: Complete

SELECT biblio.biblionumber, SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]' ) = <<Item Type|itemtypes>>

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 biblio_metadata
  JOIN biblio ON ( biblio_metadata.biblionumber = biblio.biblionumber )
  WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="n"]' )
  IN ('Y', '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)

Bibs with specific keyword in subjects

Developer: Chris Cormack & Nicole C. Baratta, 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>>, '%' )

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
    biblio_metadata
where
    hex(metadata) like '%EFBFBD%'

Find biblionumbers of marc records containing non-ascii characters

Developer: Barton Chittenden, ByWater Solutions
Module: Catalog
Purpose: Find marc records containing non-ascii characters.
Status: Complete
Notes: This will return biblionumbers for records that use the copyright symbol, so might not be as useful as it might otherwise be.

SELECT
    biblionumber
FROM
    biblio_metadata
WHERE
    metadata <> CONVERT(metadata USING ASCII)

Language Material Bibs

Developer: Chris Cormack, Catalyst and Nicole C. Baratta, 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>>

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

List of all normalized ISBNs

Developer: Nicole C. Baratta, 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 != ''

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

List of URL's from 856

Developer: LibLime provided to David Schuster
Module: Catalog
Purpose: List of URL's from 856
Status: Complete
Notes: Updated by Barton Chittenden (BWS).

SELECT biblio.biblionumber, SUBSTRING(biblio_metadata.metadata, LOCATE('<subfield code="u">',
       biblio_metadata.metadata, LOCATE('<datafield tag="856"', biblio_metadata.metadata)+19),
       LOCATE('</subfield>', biblio_metadata.metadata, LOCATE('<subfield code="u">',
       biblio_metadata.metadata, LOCATE('<datafield tag="856"',
       biblio_metadata.metadata)+19)) - LOCATE('<subfield code="u">', biblio_metadata.metadata,
       LOCATE('<datafield tag="856"', biblio_metadata.metadata)+19)) AS url
FROM biblioitems, biblio, biblio_metadata
WHERE
       biblioitems.biblionumber = biblio.biblionumber
       AND biblioitems.biblionumber = biblio_metadata.biblionumber
       AND url IS NOT NULL

I *think* that this query is addressing the fact that ExtractValue concatenates multiple tags into a single fileld. These can be individually addressed using an array index after [@tag=856]:

ExtractValue( metadata, '//datafield[@tag=856][1]/subfield[@code="u"]' ) as '856$u'

The index is 1-based, [@tag=856][1] is the first tag, [@tag=856][2] is the second, etc. It's probably possible to do something tricky here with a MySQL variable. Because I can't *exactly* tell what the original query is doing, I'm a bit loathe to dive in too deep, but I'd love to see this particular report get re-written more clearly.

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

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(metadata,'count(//datafield[@tag="856"])') AS count856,
     ExtractValue(metadata,'//datafield[@tag="856"]/*') AS link 
FROM biblio_metadata
HAVING count856 > 0

Biblio records with 856 (url)

Developer: Caroline Cyr La Rose, inLibro
Module: Catalog
Purpose: list of all biblionumbers of records that have a url in 856$u, to be used in match modification; Note: 856$u must be mapped to biblioitems.url in Administration > Koha to MARC mapping for this report to work
Status: Complete

SELECT
    biblionumber
FROM
    biblioitems
WHERE
    url IS NOT NULL

Materials based on item type from fixed fields

Developer: Nicole C. Baratta, 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

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(metadata, '//datafield[@tag="245"]/subfield[@code="h"]') AS GMD, 
       count(*) AS COUNT
       From biblio_metadata 
       GROUP BY GMD ORDER BY COUNT DESC

List of Language Codes in 008 Positions 35-37 With Count of Bibs per Language

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Catalog
Purpose: Shows 008 positions 35-37 for use in determining Advanced Search languages
Status: Completed

SELECT Substring(ExtractValue(metadata,'//controlfield[@tag=008]'),36,3) as LANG, count(biblionumber) as BIBS
FROM biblio_metadata
GROUP BY lang
ORDER BY bibs DESC

Items with Specific Attributes

SuperWeeder 008

Developer: Barton Chittenden
Module: Catalog
Purpose: A variation on SuperWeeder which uses the publication date in 008.
Status: Complete

SELECT
    CONCAT( 
        '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',
        biblio.biblionumber, '\">', 
        items.barcode, '</a>' 
    ) AS 'Barcode',
    items.itemcallnumber,
    biblio.title,
    items.barcode,
    datelastseen, 
    SUBSTR(
        ExtractValue( 
            biblio_metadata.metadata,
            '//controlfield[@tag="008"]'
        ),
        8,4
    )  AS 008pubdate,
    items.dateaccessioned AS 'Accessioned',
    items.itype, 
    items.issues,
    (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 USING (biblionumber) 
    LEFT JOIN biblio  USING (biblionumber) 
    LEFT JOIN biblio_metadata  USING (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

Items without proper itype, ccode or location

Developer: Barton Chittenden
Module: Catalog
Purpose: find count of items without corresponding entry in the itemtypes table or authorized values for location or ccode.
Status: Complete

SELECT 'itype' AS type, count(*), itype AS code 
FROM items LEFT JOIN itemtypes on (items.itype = itemtypes.itemtype) 
WHERE itemtypes.itemtype IS NULL 
GROUP BY code

    UNION

SELECT 'ccode' AS type, count(*), ccode AS code 
FROM items LEFT JOIN authorised_values av on ( av.authorised_value = items.ccode AND av.category = 'CCODE') 
WHERE av.authorised_value IS NULL 
GROUP BY code

    UNION

SELECT 'location' AS type , count(*), location AS code 
FROM items LEFT JOIN authorised_values av on ( av.authorised_value = items.location AND av.category = 'LOC') 
WHERE av.authorised_value IS NULL 
GROUP BY code

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

Titles on a particular branch and shelving location

Developer: Nicole C. Baratta, 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(metadata, '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, i.itemcallnumber 
FROM biblio b 
LEFT JOIN items i using (biblionumber)
left join biblio_metadata m using (biblionumber)
WHERE i.homebranch=<<homebranch|branches>> AND i.location=<<Shelving Location|LOC>>

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

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

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

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

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

Collection Evaluation Report

Developer: Nicole C. Baratta, 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. Baratta 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)

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

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

Items in a location with lists

Developer: Nicole C. Baratta, 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

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 <> '0'
ORDER BY b.title

List of Not for Loan Magazine items

Developer: Alex Buckley, Catalyst IT for Waitaki District Library NZ
Module: Catalog
Purpose: List magazine items (items with collection code of "MAG") which are set Not for Loan
Status: Complete

SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber, '\">', b.title, '</a>' ) AS Title,
    ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",
    ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="n"]') AS "Part name",
    ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="p"]') AS "Part",
    b.author AS 'Author',
    b.copyrightdate AS 'Copyright 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 biblio_metadata bi USING ( biblionumber ) 
WHERE i.notforloan = '1' AND i.ccode = "MAG" 
ORDER BY b.title

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

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 notes

Developer: Nicole C. Baratta, 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

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

List items which have data in the copy number field (952$t)

Developer: Heather Hernandez
Module: Catalog
Purpose: Finds all items with data in the copy number field, 952$t, retrieving that field plus location, call number, barcode, and item number, sorted by location and call number
Status: Complete

SELECT i.location, i.itemcallnumber, i.copynumber, i.barcode, i.itemnumber
FROM items i
WHERE i.copynumber IS NOT NULL
ORDER BY i.location, i.itemcallnumber

List of items by bibnumber with particular string in item type, with item location, call number, title and author

Developer: Heather Hernandez
Module: Catalog
Purpose: Finds all items with particular string in item type, with item location, call number, title and author, retrieving a hyperlinked bib number (sorted by this field), location, item call number, title and author. Replace 'music' with whatever string you want as the search string.
Status: Complete

SELECT concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">', biblio.biblionumber, '</a>') as 'bib',items.location,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.itype LIKE CONCAT( '%', 'music', '%' )
ORDER BY biblio.biblionumber ASC

Serial Enumeration Chronology containing c.2

Developer: Heather Hernandez
Module: Catalog
Purpose: Finds all items with c.2 in the serial enumeration chronology field 952$h (it should be in 952$t), retrieving that field plus location, call number, barcode, and item number, sorted by location and call number
Status: Complete

SELECT i.location, i.itemcallnumber, i.copynumber, i.barcode, i.itemnumber, i.enumchron
FROM items i
WHERE i.enumchron = 'C.2'
ORDER BY i.location, i.itemcallnumber

Quality Control

Authorities

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
Version: Will not work after upgrade to 17.05 or later

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

Authors not in the Authorities, with Biblio numbers

Developer: E. Bradtke
Module: Catalog
Purpose: List of author, corporate author and meeting names that are not linked to authority records. Lists Biblio numbers next to names.
Status: Completed

SELECT DISTINCT biblionumber, heading FROM (
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="9"]')) = 0
) AS heads
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 )

Searching 667 notes for particular authorities

Developer: By Mark Miller, ByWater Solutions for support ticket (contributed to Wiki by Heather Hernandez)
Module: Catalog
Purpose: List of hyperlinked authority record number, 1XX field (all subfields), and text in 667 note, retrieved by searching for text string in 667
Status: Completed

SELECT CONCAT('<a href=\"/cgi-bin/koha/authorities/detail.pl?authid=',a.authid,'\">',a.authid,'</a>') AS auth_record,
	ExtractValue(a.marcxml, '//datafield[contains(concat(" ",@tag)," 1")]/*') AS '1XX',
    ExtractValue(a.marcxml, '//datafield[@tag="667"]/*') AS '667'
FROM auth_header a
WHERE ExtractValue(a.marcxml, '//datafield[@tag="667"]/*') LIKE CONCAT('%',<<Enter search term>>,'%')

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(metadata,'//datafield[@tag="650"]/subfield[@code="a"]') AS heading
FROM biblio_metadata
WHERE length(ExtractValue(metadata, '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(metadata, '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
ORDER BY heading

Duplicates

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

Duplicate bibs using the 001

Developer: Katrin Fischer and Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Show records with duplicate 001 fields
Status: Completed
Version: Will not work after upgrade to 17.05 or later

SELECT
  GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers,
  ExtractValue(marcxml,'//controlfield[@tag="001"]') AS id
FROM biblioitems
GROUP BY id
HAVING count(id) > 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 ISBNs in Time Frame

Developer: Nicole C. Baratta, 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 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++ with Links to Bib Records and link to batch edit and combine

Developer: Pablo López Liotti, UNMDP
Module: Catalog
Purpose: Show records with duplicate ISBNs; has links to individual bib records and link to duplicate records group to batch edit and combine.

Based on ISBN report written by Zachary Spalding, SENYLRC.

Status: Completed
Version: All
Notes: Click biblionumber -> show individual bib record. Click ISBN ->search and list all records with same ISBN for edit/combine them.

SELECT  
        GROUP_CONCAT('<a target="_blank" title="Show record with THIS biblionumber" 
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
        biblionumber,'\">',biblionumber,'</a>') AS biblionumbers, 
        CONCAT('<a target="_blank" title="List ALL records with same ISBN to edit/combine" 
href=\"/cgi-bin/koha/catalogue/search.pl?q=', isbn,'\">',isbn,'</a>') AS ISBN 
FROM biblioitems 
GROUP BY isbn, itemtype 
HAVING COUNT(isbn)>1

Duplicate EAN/UPC (024$a)

Developer: Owen Leonard
Module: Catalog
Purpose: Show records with duplicate EAN
Status: Completed
Version: All
Notes:

SELECT 
    GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, 
    ExtractValue(metadata,'//datafield[@tag="024"]/subfield[@code="a"]') AS `EAN` 
FROM 
    biblio_metadata 
WHERE 
    (ExtractValue(metadata,'//datafield[@tag="024"]/subfield[@code="a"]') != '' 
    AND ExtractValue(metadata,'//datafield[@tag="024"]/subfield[@code="a"]') IS NOT NULL ) 
GROUP BY `EAN` 
HAVING COUNT(`EAN`)>1;

Duplicate titles (using author and title)

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 (using title and ISBN)

Developer: Nicole C. Baratta, 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 titles (using title and ISBN), multi-branch version

Developer: Barton Chittenden, ByWater Solutions / Cab Vinton
Module: Catalog
Purpose: Show records with duplicate titles (using the first 9 characters) and duplicate ISBNs, where at least one item is owned by a particular branch
Status: Completed
Works With: 17.11

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)
WHERE i.isbn IS NOT NULL AND i.isbn <> ' ' AND
     EXISTS( SELECT * from items where b.biblionumber = items.biblionumber and items.homebranch = <<Library|branches>> )
GROUP BY CONCAT(substr(b.title,0,9),"/",i.isbn) 
HAVING COUNT(*) > 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 titles having both DVD items and others

Developer: Barton Chittenden
Module: Catalog
Purpose: Find pairs of titles, regardless of item type, having at least item of itype DVD and one item that does *not* have itype DVD. Used to exclude Kanopy downloads where the library already has a DVD of the title.
Status: Completed

SELECT
    title,
    GROUP_CONCAT(
        distinct CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>' )
        SEPARATOR ', ' 
    ) as biblionumbers,
    GROUP_CONCAT(distinct description SEPARATOR ', ' ) AS itemtypes    
FROM
    biblio
    INNER JOIN items USING (biblionumber)
    LEFT JOIN itemtypes on (itype = itemtype)
GROUP BY
    title
HAVING
    SUM(IF(itype = 'DVD', 1, 0 )) > 0
    AND SUM(IF(itype != 'DVD' OR itype IS NULL, 1, 0 )) > 0

Duplicate authorities

Developer: Sarah Cornell
Module: Catalog
Purpose: Finds multiple occurrences of main headings. Useful for manual cleanup after migrating from a system that stores separate name and topic authority files.
Status: Completed
Works with: 17.11

SELECT 
  GROUP_CONCAT(authid SEPARATOR ', ') AS "AuthIDs",
  CONCAT('<a href=\"/cgi-bin/koha/authorities/merge.pl?authid=',MAX(authid),'&authid=',MIN(authid),'\" target="_blank">Merge</a>') AS "Merge",
  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
    ) AS "Heading",
  GROUP_CONCAT(Extractvalue(marcxml,'//datafield[@tag="035"]/subfield[@code="a"]') SEPARATOR ', ') AS "ControlNumbers",
  authid, 
  datecreated, 
  modification_time, 
  authtrees, 
  marc
FROM auth_header
GROUP BY heading
HAVING count(authid) > 1
ORDER BY heading

Duplicate 001 fields with OCLC prefixes removed

Developer: Sarah Cornell
Module: Catalog
Purpose: Removes OCLC prefixes from 001 field and then returns duplicates. Useful if OCLC records have been imported using a variety of rules. Includes tool for merging and normalized OCLC record number lookup. Added TRIM to remove leading zeroes.
Status: Completed
Works with: 20.05

SELECT 
GROUP_CONCAT(DISTINCT biblio.biblionumber SEPARATOR ', ') AS biblionumbers,
normOCLC,
CONCAT('<a href=\"/cgi-bin/koha/catalogue/search.pl?idx=kw&q=',normOCLC,'\"target="_blank">oclc lookup</a>') AS 'lookup by oclc',
COUNT(DISTINCT biblio.biblionumber) AS count,
GROUP_CONCAT(DISTINCT biblio.title SEPARATOR ' NEXT TITLE:') AS titles,
CONCAT('<a href=\"/cgi-bin/koha/cataloguing/merge.pl?biblionumber=',MIN(biblio.biblionumber),'&biblionumber=',MAX(biblio.biblionumber),'\"target="_blank">Merge</a>') AS FastMerge,
GROUP_CONCAT(DISTINCT biblioitems.itemtype SEPARATOR '::') AS TypeDiscrepCheck

FROM (

	SELECT 
	biblionumber,
	TRIM(LEADING '0' FROM REGEXP_REPLACE(ExtractValue( metadata, '//controlfield[@tag=\"001\"]' ), '[ocmn ]', '')) AS normOCLC
		FROM biblio_metadata 
		WHERE ExtractValue( metadata, '//controlfield[@tag=\"001\"]' )  !=''

) AS OCLC

	LEFT JOIN biblio ON (OCLC.biblionumber=biblio.biblionumber) 
	LEFT JOIN biblioitems ON (OCLC.biblionumber=biblioitems.biblionumber)
	LEFT JOIN biblio_metadata ON (OCLC.biblionumber=biblio_metadata.biblionumber)
GROUP BY normOCLC
HAVING COUNT(DISTINCT biblio.biblionumber) >1
LIMIT 100

Duplicate title and author combinations within an item type

Developer: Andrew Fuerste-Henry
Module: Catalog
Purpose: Finds bib records that share a title, author, and item type. Includes a link to a cataloging search to facilitate merging.
Status: Completed
Works with: 19.11

SELECT title,
    author, 
    itemtype,
    count(DISTINCT biblionumber) as count_of_bibs, 
    group_concat(ifnull(bib_info,concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>')) SEPARATOR '<br>') as bib_numbers, 
    concat('<a href=\"
/cgi-bin/koha/cataloguing/addbooks.pl?q=sn%3A', group_concat(biblionumber SEPARATOR '+OR+sn%3A'), '\">','Link to merge', '</a>') as merge_link
FROM biblio
	left join biblioitems USING (biblionumber)
    LEFT JOIN (SELECT biblionumber, concat(concat('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">', biblionumber, '</a>'),' - items at:',
    				group_concat(homebranch SEPARATOR ', ')) as bib_info
                FROM items
                GROUP BY biblionumber) i USING (biblionumber)
WHERE title is not null and author is not null
GROUP BY title, author, itemtype
HAVING count_of_bibs > 1

Mismatches

Bibs with diff item types attached

Developer: Nicole C. Baratta, 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 Different Item Types

Developer: Nicole C. Baratta, 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

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 != '';

Identify records with mismatched 008 vs 260c/264c

Developer: Jason Robb, Southeast Kansas Library System
Module: Catalog
Purpose: Compares year from the 008 to the first year in the 260$c and/or 264$c and lists mismatched info.
Status: Complete

SELECT b.biblionumber,
       b.title,
       b.copyrightdate,
       SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'),8,4) AS '008date',
       ExtractValue(metadata, '//datafield[@tag="260"]/subfield[@code="c"]') as '260c',
       REGEXP_SUBSTR(ExtractValue(metadata, '//datafield[@tag="260"]/subfield[@code="c"]'), "[0-9]{4}") AS '260cstrip',
       ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') as '264c',
       REGEXP_SUBSTR(ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]'), "[0-9]{4}") AS '264cstrip'
FROM biblio b
LEFT JOIN biblio_metadata b2 USING (biblionumber)
GROUP BY b.biblionumber
HAVING ((260c <> '' AND 008date <> 260cstrip) OR (264c <> '' AND 008date <> 264cstrip))
LIMIT 5000

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

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

Mismatches between 2 collection codes

Developer: Jason Robb (SEKLS)
Module: Catalog
Purpose: Compare two collection codes for mismatches on a single record
Status: Complete

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',i.biblionumber,'\" target="_blank">'"view"'</a>') AS "view",
i.biblionumber,
SUM(CASE WHEN i.ccode= <<Collection code 1|ccode>> THEN 1 ELSE 0 END) AS Count1,
SUM(CASE WHEN i.ccode= <<Collection code 2|ccode>> THEN 1 ELSE 0 END) AS Count2,
GROUP_CONCAT(DISTINCT(i.homebranch))
FROM items i
GROUP BY i.biblionumber
HAVING Count1 >= 1 AND Count2 >= 1

Null Island

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"]' ) = ""

Versions 17.05 and later:

SELECT biblio.biblionumber, biblio.title, biblio.author
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]' ) = ""

Bibs without subjects

Developer: Nicole C. Baratta, 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(metadata,'//datafield[@tag="650"]/subfield[@code>="a"]') AS sub1, 
    ExtractValue(metadata,'//datafield[@tag="651"]/subfield[@code>="a"]') AS sub2, 
    ExtractValue(metadata,'//datafield[@tag="600"]/subfield[@code>="a"]') AS sub3, 
    ExtractValue(metadata,'//datafield[@tag="610"]/subfield[@code>="a"]') AS sub4, 
    ExtractValue(metadata,'//datafield[@tag="611"]/subfield[@code>="a"]') AS sub5, 
    ExtractValue(metadata,'//datafield[@tag="630"]/subfield[@code>="a"]') AS sub6, 
    ExtractValue(metadata,'//datafield[@tag="648"]/subfield[@code>="a"]') AS sub7, 
    ExtractValue(metadata,'//datafield[@tag="653"]/subfield[@code>="a"]') AS sub8, 
    ExtractValue(metadata,'//datafield[@tag="654"]/subfield[@code>="a"]') AS sub9, 
    ExtractValue(metadata,'//datafield[@tag="655"]/subfield[@code>="a"]') AS sub10, 
    ExtractValue(metadata,'//datafield[@tag="656"]/subfield[@code>="a"]') AS sub11, 
    ExtractValue(metadata,'//datafield[@tag="657"]/subfield[@code>="a"]') AS sub12, 
    ExtractValue(metadata,'//datafield[@tag="658"]/subfield[@code>="a"]') AS sub13, 
    ExtractValue(metadata,'//datafield[@tag="662"]/subfield[@code>="a"]') AS sub14 
  FROM biblio_metadata) 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 = ""

Items without 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 = '')

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

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

Alternate from Fridolin Somers, using JOIN :

SELECT items.itemcallnumber, biblioitems.isbn, concat('<a href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'>',biblio.title,'</a>') AS Title
FROM biblio
JOIN biblioitems ON( biblio.biblionumber = biblioitems.biblionumber)
JOIN items ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
WHERE (biblioitems.isbn IS NULL OR biblioitems.isbn ='')
GROUP BY items.biblionumber

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

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

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: Bib records without items
Status: Complete

SELECT biblionumber, title 
FROM biblio 
WHERE biblionumber NOT IN (SELECT biblionumber FROM items)

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)

Validation

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

Missing barcode in a range

Developer: Josef Moravec
Module: Catalog
Purpose: If you want to fill the gaps in your barcodes row for items
Status: Complete
Notes: Inspiration here: https://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql

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

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

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

Items with orphaned Locations

Developer: Barton Chittenden
Module: Catalog
Purpose: Find items whose location does not match any locations in authorized_values with category 'LOC'.
Status: Complete

SELECT
    CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">', title, '</a>' ) AS title,
    barcode,
    location
FROM
    items
    INNER JOIN biblio using (biblionumber)
WHERE
    NOT EXISTS( 
        SELECT * from authorised_values  
        WHERE items.location = authorised_values.authorised_value 
          AND authorised_values.category = 'LOC' 
    )

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

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)

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

Catalogers Gone Wild

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

Authorities records added/deleted in time frame

Developer: Nicole C. Baratta, 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

Bibs marked as RDA

Developer: Barton Chittenden, ByWater Solutions
Module: Catalog
Purpose: A report to find bibs marked as RDA per Leader or 040$a
Status: Complete

SELECT
    biblionumber,
    title,
    SUBSTRING(ExtractValue(metadata,'//leader'),18,1) != 'i' as 'Descriptive Cataloging Form',
    ExtractValue( metadata, '//datafield[@tag=040]/subfield[@code="e"]' ) as 'Description conventions'
FROM
    biblio_metadata
WHERE
    SUBSTRING(ExtractValue(metadata,'//leader'),18,1) != 'i'
    AND  ExtractValue( metadata, '//datafield[@tag=040]/subfield[@code="e"]' ) != 'rda'

Bibs without RDA specific fields

Developer: Nicole C. Baratta, ByWater Solutions
Module: Catalog
Purpose: A report to find bibs that have RDA fields (336-339)
Status: Complete - Updated 2/21/19

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>')
AS bibnumber
FROM 
(SELECT biblionumber, ExtractValue(metadata,'//datafield[@tag="336"]/subfield[@code>="a"]') AS rda1, 
ExtractValue(metadata,'//datafield[@tag="337"]/subfield[@code>="a"]') AS rda2, 
ExtractValue(metadata,'//datafield[@tag="338"]/subfield[@code>="a"]') AS rda3, 
ExtractValue(metadata,'//datafield[@tag="339"]/subfield[@code>="a"]') AS rda4 
FROM biblio_metadata) AS rda 
WHERE rda1 != "" 
OR rda2 != "" 
OR rda3 != "" 
OR rda4 != ""

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

Count of items added by cataloger

Developer: Nicole C. Baratta, 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. Baratta, 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)

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

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

Records Cataloged with a Specific Framework

Developer: Nicole C. Baratta, 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>>

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

List Item Types, Collection Codes and Locations

Developer: Barton Chittenden, ByWater Solutions
Module: Catalog
Purpose: Show Item Types, Collection Codes and Locations by code and description.
Status: Complete

SELECT
    '<b>item types</b>' as code,
    '' as description
UNION
SELECT
    itemtype as code,
    description
FROM itemtypes
UNION
SELECT
    '<b>Collection Codes</b>' as code,
    '' as description
UNION
SELECT
    authorised_value as code,
    lib as description
FROM
    authorised_values
WHERE
    category = 'CCODE'
UNION
SELECT
    '<b>Location</b>' as code,
    '' as description
UNION
SELECT
    authorised_value as code,
    lib as description
FROM
    authorised_values
WHERE
    category = 'LOC'

Find records with excessive whitespace in the call number

Developer: George H. Williams (Next Search Catalog / Northeast Kansas Library System)
Module: Catalog
Purpose: Find records with excessive whitespace in the call number. Shows whitespace with pipes.
Status: Complete

SELECT
  items.itemnumber,
  Replace(items.itemcallnumber, ' ', '|') AS CALL_NUMBER_W_BREAKS,
  items.barcode AS ITEM_BARCODE,
  home_branches.branchname AS HOME_BRANCH,
  holding_branches.branchname AS HOLDING_BRANCH,
  If(
    perm_loc.lib = loc.lib, 
    perm_loc.lib, 
    Concat(perm_loc.lib, ' (', loc.lib, ')')
  ) AS LOCATION,
  itypes.description AS ITEM_TYPE,
  ccode.lib AS CCODE,
  If(
    items.copynumber IS NULL, 
    items.itemcallnumber,
    Concat(items.itemcallnumber, ' // Copy number: ', items.copynumber)
  ) AS CALL_NUMBER,
  biblio.author,
  biblio.title,
  cnlines.length AS SPINE_LABEL_LINES
FROM
  items LEFT JOIN
  biblio ON biblio.biblionumber = items.biblionumber LEFT JOIN
  biblioitems ON biblioitems.biblioitemnumber = items.biblionumber LEFT JOIN
  (
    SELECT
      branches.branchcode,
      branches.branchname
    FROM
      branches
    ) home_branches 
    ON home_branches.branchcode = items.homebranch
    LEFT JOIN
  (
    SELECT
      branches.branchcode,
      branches.branchname
    FROM
      branches) holding_branches 
      ON holding_branches.branchcode = items.holdingbranch 
      LEFT JOIN
  (
    SELECT
      authorised_values.category,
      authorised_values.authorised_value,
      authorised_values.lib,
      authorised_values.lib_opac
    FROM
      authorised_values
    WHERE
      authorised_values.category = 'LOC'
    ) perm_loc 
    ON perm_loc.authorised_value = items.permanent_location 
    LEFT JOIN
  (
    SELECT
      authorised_values.category,
      authorised_values.authorised_value,
      authorised_values.lib,
      authorised_values.lib_opac
    FROM
      authorised_values
    WHERE
      authorised_values.category = 'LOC'
    ) loc ON loc.authorised_value = items.location 
    LEFT JOIN
  (
    SELECT
      itemtypes.itemtype,
      itemtypes.description
    FROM
      itemtypes
    ) itypes ON itypes.itemtype = items.itype 
    LEFT JOIN
  (
    SELECT
      authorised_values.category,
      authorised_values.authorised_value,
      authorised_values.lib,
      authorised_values.lib_opac
    FROM
      authorised_values
    WHERE
      authorised_values.category = 'CCODE'
    ) ccode 
    ON ccode.authorised_value = items.ccode JOIN
  (
    SELECT
      items.itemnumber,
      items.barcode,
      (Length(items.itemcallnumber) - Length(Replace(items.itemcallnumber, ' ',
      '')) + 1) AS length,
      items.homebranch
    FROM
      items
    WHERE
      items.homebranch Like <<Choose your library|branches:all>> AND
      (Length(Replace(items.itemcallnumber, ' ', '-')) - Length(Replace(items.itemcallnumber, ' ', '')) + 1) > <<Greater than X lines on the spine label>>
    ) cnlines 
    ON cnlines.itemnumber = items.itemnumber AND
      cnlines.homebranch = items.homebranch
WHERE
  items.homebranch Like <<Choose your library|branches:all>>
GROUP BY
  items.itemnumber,
  cnlines.length,
  items.itemcallnumber
ORDER BY
  HOME_BRANCH,
  LOCATION,
  ITEM_TYPE,
  CCODE,
  CALL_NUMBER,
  biblio.author,
  biblio.title,
  items.itemnumber

Bibs with more items than an entered amount

Developer: Jason Robb (SEKLS)
Module: Catalog
Purpose: Find records with large number of items.
Status: Complete

SELECT biblio.biblionumber, title, author, count(*)
FROM items, biblio
WHERE biblio.biblionumber=items.biblionumber
GROUP BY biblio.biblionumber having count(*) > <<# of items/bib>>
ORDER BY count(*) DESC

Collection Development

Turnover Rate by Collection in Date Range

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Lists turnover ratios by collection in a given date range
Status: Complete

SELECT av.lib as Collection, COUNT(stats.datetime) AS Issues, COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
(COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))) AS Turnover
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') AS stats
RIGHT JOIN items i ON (i.itemnumber = stats.itemnumber)
LEFT JOIN (select * from authorised_values where category='ccode') av on (i.ccode=av.authorised_value)
WHERE i.dateaccessioned < <<End Date|date>> AND i.itype != 'ILL' #remove interlibrary loans
GROUP BY i.ccode, av.lib
ORDER BY av.lib

Turnover Rate for Call Number Range in Collection by Date Range

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Lists turnover ratios for a call number range within a collection and in a given date range
Status: Complete

SELECT COUNT(stats.datetime) AS Issues, COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
(COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))) AS Turnover
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> 
    AND ccode = <<Collection|ccode>> AND type='issue') AS stats
RIGHT JOIN items i ON (i.itemnumber = stats.itemnumber)
WHERE i.itemcallnumber BETWEEN <<Starting Call>> AND <<Ending Call (Exclusive)>> 
AND i.dateaccessioned < <<End Date|date>> AND i.ccode=<<Collection|ccode>>

Relative Use by Collection with Turnover

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Shows Relative Use (% of Issues/% of Total Collection) by Collection. Ideally relative use should be a 1:1 ratio.
Status: Complete

SELECT av.lib AS Collection, 
COUNT(stats.datetime) AS Issues, 
COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
totalissues,
totalcount,
(COUNT(stats.datetime)*100/totalissues) as 'Percentage of Issues',
((COUNT(DISTINCT(i.itemnumber))*100)/totalcount) as 'Percentage of Total Collection', 
FORMAT((COUNT(stats.datetime)/totalissues)/(COUNT(DISTINCT(i.itemnumber))/totalcount),4) as 'Relative Use',
FORMAT((COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))),4) AS Turnover
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') AS stats
RIGHT JOIN items i ON (i.itemnumber = stats.itemnumber)
LEFT JOIN (SELECT count(*) as totalcount from items) tct on (totalcount IS NOT NULL) 
LEFT JOIN (SELECT count(datetime) as totalissues from statistics WHERE date(datetime) 
BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') ti on (totalissues IS NOT NULL)
LEFT JOIN (SELECT * FROM authorised_values WHERE category='ccode') av ON (i.ccode=av.authorised_value)
WHERE i.dateaccessioned < <<End Date|date>> AND i.itype != 'ILL' #remove interlibrary loans
GROUP BY i.ccode
ORDER by av.lib

Relative Use For Dewey Based Collections in Call Num Range with Turnover

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Shows Relative Use (% of Issues/% of Total Collection) within a Dewey based collection. Allows for fine grain examination of collection area. Ideally relative use should be a 1:1 ratio.
Status: Complete

SELECT CASE
	/*When the total number of digits is < 3 add zeros to pad */
	WHEN <<Max # Dewey Digits>>=1 THEN CONCAT(LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>),'00')
    WHEN <<Max # Dewey Digits>>=2 THEN CONCAT(LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>),'0')
    WHEN <<Max # Dewey Digits>>=3 THEN LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>)
    /*If the total number of digits is > 3 then the decimal might be the 4th character if so add one more character*/
    WHEN <<Max # Dewey Digits>>=4 THEN LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>+1)
    /*Else if total number of digits is > 4 then check for ending character as decimal and adjust for skipping decimal, if the ending character isn't a decimal
    Then just adjust for skipping the decimal*/
    ELSE IF(RIGHT(LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>),1)='.',
	LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>+2),
	LEFT(REGEXP_SUBSTR(itemcallnumber,'[0-9]{3}[^a-zA-Z]*'),<<Max # Dewey Digits>>+1))
    END AS DeweyNum,
COUNT(stats.datetime) AS Issues, 
COUNT(DISTINCT(i.itemnumber)) AS NumItems, 
totalissues as 'Collection Issues',
totalcount 'Collection NumItems',
ROUND(avg(copyrightdate),0) as 'AVG Pubdate',
(COUNT(stats.datetime)*100/totalissues) as 'Percentage of Issues',
((COUNT(DISTINCT(i.itemnumber))*100)/totalcount) as 'Percentage of Total Collection', 
FORMAT((COUNT(stats.datetime)/totalissues)/(COUNT(DISTINCT(i.itemnumber))/totalcount),4) as 'Relative Use',
FORMAT((COUNT(stats.datetime)/COUNT(DISTINCT(i.itemnumber))),4) AS Turnover,
FORMAT((totalissues/totalcount),4) AS 'Collection Turnover'
FROM (
    SELECT datetime, itemnumber, ccode
    FROM statistics
    WHERE date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') AS stats
RIGHT JOIN (SELECT * FROM items ) i ON (i.itemnumber = stats.itemnumber)
LEFT JOIN biblio b on (b.biblionumber=i.biblionumber)
LEFT JOIN (SELECT count(*) as totalcount from items WHERE ccode=<<Dewey Based Collection|ccode>>) tct ON (totalcount IS NOT NULL) 
LEFT JOIN (SELECT count(datetime) as totalissues from statistics WHERE ccode=<<Dewey Based Collection|ccode>> AND
date(datetime) BETWEEN <<Start Date|date>> AND <<End Date|date>> AND type='issue') ti on (totalissues IS NOT NULL)
WHERE i.dateaccessioned < <<End Date|date>> AND i.ccode=<<Dewey Based Collection|ccode>>
 AND i.itemcallnumber BETWEEN <<Starting Call>> AND <<Ending Call (Exclusive)>>
GROUP BY DeweyNum
ORDER by DeweyNum

CREW Friendly Weeding

Developer: Ronald Houk, Ottumwa Public Library
Module: Collection Development
Purpose: Generates list candidates for weeding based upon number of years since last cko and publication date within a call number range in a collection
Status: Complete

SELECT  
CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', b.biblionumber, '\">View Details</a>' ) AS 'View Details',
i.itemcallnumber,b.copyrightdate,b.author,b.title,i.ccode,i.itemnumber,i.barcode,b.biblionumber,i.dateaccessioned,
i.datelastborrowed,i.issues,i.itemnotes_nonpublic,i.itemnotes
FROM items i
LEFT JOIN biblioitems bi on (i.biblioitemnumber=bi.biblioitemnumber)
LEFT JOIN biblio b on (bi.biblionumber=b.biblionumber)  
WHERE i.itemcallnumber BETWEEN <<'Starting Call'>> AND <<'Ending Call (Exclusive)'>> AND i.ccode=<<Collection|ccode>> 
AND i.onloan IS NULL AND i.damaged=0 AND i.itemlost=0 AND i.withdrawn=0 AND
CASE
  /*if the item has not circulated then see if it was added before num years since last cko
  if it was then use copyright date as criteria*/
  WHEN (datelastborrowed IS NULL AND dateaccessioned < <<Last CKO Date|date>>)
    THEN b.copyrightdate < <<Published Before (YYYY)>>
    /*as long as the item has circulated at least once then use combination of datelastborrowed and copyright date
    as criteria*/
  ELSE (i.datelastborrowed < <<Last CKO Date|date>> OR b.copyrightdate < <<Published Before (YYYY)>> )
END

UNION ALL

SELECT '','zzz# Proposed to Weed: ',FOUND_ROWS(),'# in Range: ',count(itemnumber), 'Percent Proposed to Weed:',
CONCAT(FORMAT(IF(count(itemnumber)=0,0,(FOUND_ROWS()*100.0/count(itemnumber))),2),'%'),'Generated on: ',CURDATE(),'','','','',''
FROM items i
WHERE i.withdrawn=0 and i.damaged=0 AND i.itemcallnumber BETWEEN <<'Starting Call'>> 
AND <<'Ending Call (Exclusive)'>> AND i.ccode=<<Collection|ccode>>
ORDER BY itemcallnumber,author,title

A particular Title total number of times issued count in date range

Developer: Vinod Kumar Mishra
Module: Collection Development
Purpose: Report to generate total number of times a particular book/title is issued. It will be helpful in collection development/procurement.
Status: Complete

SELECT b.biblionumber 'Record No.', b.title 'Title',b.author 'Author',ExtractValue(bm.metadata, '//datafield[@tag="260"]/subfield[@code="b"]') 'Publisher', b.copyrightdate 'Year',SUM(i.issues)'Total Issued',count(i.biblioitemnumber)'Total Copy'

FROM biblio b

LEFT JOIN items i on (b.biblionumber = i.biblionumber)
LEFT JOIN biblio_metadata bm on (b.biblionumber = bm.biblionumber)

GROUP BY b.biblionumber
HAVING SUM(i.issues) > <<Issued More Than>>
ORDER BY SUM(i.issues) DESC

Statistical reports

Shows the total number of items circulated from a branch other than the owning branch

Developer: Nicole C. Baratta, 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. Baratta, 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

Shows details of statistics for a given shelving location in a date range

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Statistical (Circulation)
Purpose: Shows details of statistics for a given shelving location in a date range
Status: Complete

SELECT i.barcode, b.title, s.type, s.location, s.datetime
FROM statistics s left join items i using (itemnumber) left join biblio b using (biblionumber)
WHERE s.location=<<Shelving Location|loc>> and s.datetime between <<Start Date|date>> and <<End Date|date>>
ORDER BY s.datetime desc

List count of 'genderwise statistical data' of patrons who are enrolled to library

Developer: Pankaj Kumar Sharma
Module: Statistical (Patron)
Purpose: List count of 'genderwise statistical data' of patrons who are enrolled to library
Status: Completed
Works/Tesed with: Koha Version-18.05.04.000, MySQL Version-14.14

Select count(borrowernumber) as 'Genderwise Enrollment',sex as Gender from borrowers
where borrowernumber!=1 
group by Gender

List count of 'citywise gender statistical data' of patrons who are enrolled to library

Developer: Pankaj Kumar Sharma
Module: Statistical (Patron)
Purpose: List count of 'citywise gender statistical data' of patrons who are enrolled to library
Status: Completed
Works/Tesed with: Koha Version-18.05.04.000, MySQL Version-14.14

Select count(borrowernumber) as 'Genderwise Enrollment',city,sex as Gender from borrowers
where borrowernumber!=1 
group by city,Gender order by city

Notices Reports

Overdue Notices

Notices Sent

Developer: Nicole C. Baratta, 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

Daily message count by "Sent to" domain

Developer: George H. Williams, Northeast Kansas Library System
Module: Notices
Purpose: Returns a count of messages sent to a specific domain on a specific date during the date range you set
Status: Complete.

SELECT
  Date_Format(message_queue.updated_on, '%Y-%m-%d') AS DATE_SENT,
  Coalesce(SubString_Index(message_queue.to_address, '@', -1), '') AS SENT_TO,
  Count(DISTINCT message_queue.message_id) AS COUNT_OF_MESSAGES_SENT
FROM
  message_queue
WHERE
  message_queue.updated_on BETWEEN <<Messages sent between the beginning of the day on date1|date>> AND (<<and the end of the day on date2|date>> + INTERVAL 1
  DAY)
GROUP BY
  Date_Format(message_queue.updated_on, '%Y-%m-%d'),
  Coalesce(SubString_Index(message_queue.to_address, '@', -1), '')
ORDER BY
  DATE_SENT DESC,
  SENT_TO

Daily message count by "Sent to" specific domains

Developer: Christopher Brannon, Coeur d'Alene Public Library / Cooperative Information Network
Module: Notices
Purpose: Returns a count of messages sent to specific domains (gmail/yahoo) on a specific date during the date range you set
Status: Complete.

SELECT DATE(mq.time_queued) Date,IF(mq.to_address LIKE '%gmail.com','gmail.com','yahoo.com') Carrier, COUNT(*) Notices
FROM message_queue mq
WHERE DATE(mq.time_queued) BETWEEN <<Date in question from|date>> AND <<to|date>> AND mq.status="sent" AND (mq.to_address LIKE '%gmail.com' OR mq.to_address LIKE '%yahoo.com')
GROUP BY Date, Carrier

Primary email domain usage in borrower accounts

Developer: Christopher Brannon, Coeur d'Alene Public Library / Cooperative Information Network
Module: Notices
Purpose: Returns a count of uses of each unique domain in borrower primary emails. Use this to scan through results for anomalies.
Status: Complete.

SELECT SUBSTRING_INDEX(b.email, '@', -1) domain, count(*)
FROM borrowers b
WHERE b.email IS NOT NULL AND b.email != ''
GROUP BY domain

Find a user with a specific partial domain

Developer: Christopher Brannon, Coeur d'Alene Public Library / Cooperative Information Network
Module: Notices
Purpose: Great tool to track down those pesky partial or incomplete domains without having to sift through all the other email addresses that have that partial domain as well.
Status: Complete.

SELECT b.cardnumber,SUBSTRING_INDEX(b.email, '@', -1) domain
FROM borrowers b
WHERE SUBSTRING_INDEX(b.email, '@', -1) = <<domain>>

Acquisition Reports

Orders in Date Range

Developer: Nicole C. Baratta, 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. Baratta, 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. Baratta, 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. Baratta, 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. Baratta, 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. Baratta, 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

List of Biblio Numbers on an Invoice

Developer: Joseph Alway
Module: Acquisitions
Purpose: This report will give a list of the biblionumbers on an invoice. Which we can then use to export the records from Koha using the export data tool.
Status: Complete

SELECT biblionumber AS 'Biblio Number'
FROM aqorders
LEFT JOIN aqinvoices USING (invoiceid)
WHERE invoicenumber=<<Invoice Number>>

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. Baratta, 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*o.quantity),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
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
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 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 , Andrew Fuerste-Henry, ByWater Solutions
Module: Acquisitions
Purpose: Provide list of all active Vendors in Koha along with contact details and Koha ID.
Status: Updated to work on 20.05

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.booksellerid=r.id)
 
WHERE r.active=1

Items list by accession number range and sorted by call number

Developer: Vinod Kumar Mishra
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
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
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.11

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>>
GROUP BY oo.barcode
ORDER BY oo.barcode ASC

All Invoice details received in particular financial/budget year

Developer: Vinod Kumar Mishra
Module: Acquisitions
Purpose: This report will help Libraries to print all Invoice details received in a particular financial/budget year. It provides number of unique titles as well as total items received along with other invoice details.

Status: Complete tested on Koha 16.11.07

SELECT rrr.name 'Vendor/Firm Name', r.basketname 'Basket/Order Number',rrrr.invoicenumber 'Invoice Number',rrrr.billingdate 'Bill Date',
 round(SUM(case when rrrr.shipmentcost IS NOT NULL THEN rr.unitprice_tax_included*rr.quantityreceived+rrrr.shipmentcost
 ELSE rr.unitprice_tax_included*rr.quantityreceived END),2)'Total Invoice Amount',rrrr.closedate 'Invoice Close Date',
 COUNT(DISTINCT rr.biblionumber)'Unique Title',SUM(rr.quantityreceived) 'Quantity Received'

FROM aqbasket r

left join aqorders rr on rr.basketno=r.basketno
left join aqbooksellers rrr on rrr.id=r.booksellerid
left join aqinvoices rrrr on rrrr.invoiceid=rr.invoiceid
left join aqbudgets rrrrr on rrrrr.budget_id=rr.budget_id


where rr.orderstatus LIKE 'complete' AND rrrrr.budget_period_id=<<Koha Budget Period ID>>

group by rr.basketno

order by rrr.name ASC

Summary of invoices paid on [date] to [vendor]

Developer: Sarah Cornell and Bywater staff
Module: Acquisitions
Purpose: Summarizes invoice activity on a date. Enter % for all vendors.
Status: Completed
Works with: 17.11

SELECT 
CONCAT('<a href="/cgi-bin/koha/acqui/invoice.pl?invoiceid=',invoiceid,'">view</a>') AS 'view invoice',
aqinvoices.invoicenumber, 
sub2.*,
CAST((sub2.items_total+sub2.shipment_cost) AS DECIMAL(18,2)) AS 'invoice total'

FROM
(
SELECT  invoiceid,
     sub1.vendor, 
     sub1.invoice_date,
     sub1.date_paid,
     CAST(SUM(sub1.ordertotal_hide) AS DECIMAL(18,2)) AS 'items_total',
     sub1.shipment_cost

FROM 
(
SELECT  invoiceid, 
     aqbooksellers.name AS 'vendor',  
     aqinvoices.billingdate AS 'invoice_date',
     aqinvoices.closedate AS 'date_paid',
     (unitprice*quantityreceived) AS 'ordertotal_hide',
     CAST(IFNULL(aqinvoices.shipmentcost,0) AS DECIMAL(18,2)) AS 'shipment_cost'
FROM aqorders
LEFT JOIN aqinvoices USING (invoiceid)
LEFT JOIN aqbooksellers ON booksellerid=aqbooksellers.id
) AS sub1

GROUP BY invoiceid
) AS sub2

LEFT JOIN aqinvoices USING (invoiceid)
WHERE DATE(aqinvoices.closedate)=<<Date closed|date>>
AND sub2.vendor LIKE CONCAT( '%', <<Enter part of vendor name>>, '%')

List of invoices in date range in a fund

Developer: Caroline Cyr La Rose, inLibro
Module: Acquisitions
Purpose: Gives a list of all invoices with total for a particular fund
Status: Completed
Works with: developped on 19.05

SELECT
    budget_name AS "Fund",
    invoicenumber AS "Invoice number",
    aqbooksellers.name AS "Vendor",
    ROUND(SUM((unitprice*quantityreceived)), 2) AS "Total"
FROM
    aqinvoices
    LEFT JOIN aqbooksellers ON (aqinvoices.booksellerid = aqbooksellers.id)
    LEFT JOIN aqorders USING (invoiceid)
    LEFT JOIN aqbudgets USING (budget_id)
WHERE budget_code = <<Fund code>>
    AND datereceived IS NOT NULL
    AND billingdate BETWEEN <<Between|date>> AND <<and|date>>
GROUP BY invoiceid

Avg days from receipt to available

Developer: Bywater Solutions
Module: Acquisitions
Purpose: Specify the time period the item was first available.Accession date variant
Status: Completed
Works with: 19.05

SELECT 'Average',SUM(days_from_receipt)/COUNT(*) AS Average_days,"" AS "First activity","" AS "Accessioned"
FROM (
SELECT DATEDIFF(date(MIN(datetime)),dateaccessioned)  AS days_from_receipt
FROM items 
JOIN statistics USING (itemnumber) 
WHERE dateaccessioned IS NOT NULL AND statistics.type IN ( 'return','localuse','issue')   AND dateaccessioned > '2016-04-04'
GROUP BY itemnumber
HAVING date(min(datetime)) BETWEEN <<Start date|date>> AND <<End date|date>>
) foo
UNION ALL
SELECT itemnumber,DATEDIFF(date(MIN(datetime)),dateaccessioned), min(date(datetime)), dateaccessioned AS days_from_receipt
FROM items 
JOIN statistics USING (itemnumber) 
WHERE dateaccessioned IS NOT NULL AND statistics.type IN ( 'return','localuse','issue')   AND dateaccessioned > '2016-04-04'
GROUP BY itemnumber
HAVING date(min(datetime)) BETWEEN <<Start date|date>> AND <<End date|date>>

Suggestion Report with selection by Status

Developer: Joseph Alway
Module: Acquisitions
Purpose: Displays the Title, Author, ©/Pub Date, Fund, Suggested By, Date Requested, and Status of suggestions based on the custom authorized values in CUSTOM_REPORT_SUGGESTIONS_STATUS.
Status: Complete
Works with: 22.11
Note: Must Create Custom Authorized Values in Custom Authorized Values Category to function properly.

==================
START OF SQL CODE:
==================
SELECT sug.title AS 'Title',
       sug.author AS 'Author',
       sug.copyrightdate AS '©/Pub Date',
       aqb.budget_code AS 'Fund',
       CONCAT(bo.firstname,' ',bo.surname) AS 'Suggested By',
       sug.suggesteddate AS 'Date Requested',
       REPLACE(CONCAT(UCASE(LEFT(sug.STATUS, 1)), LCASE(SUBSTRING(sug.STATUS, 2))), 'Asked', 'Pending') AS 'Status'
FROM suggestions AS sug
LEFT JOIN borrowers AS bo ON (sug.suggestedby=bo.borrowernumber)
LEFT JOIN aqbudgets AS aqb ON (sug.budgetid=aqb.budget_id)
WHERE sug.STATUS LIKE <<Status|CUSTOM_REPORT_SUGGESTIONS_STATUS>>
==================
END OF SQL CODE
==================

===============================
Authorized Value Category Name:
===============================
CUSTOM_REPORT_SUGGESTIONS_STATUS

==========================
Authorized Values Details: (These are entered under the CUSTOM_REPORT_SUGGESTIONS_STATUS authorized value category.)
==============================================
Authorized Value,Description,Description(OPAC)
==============================================
%,1| All,1| All
Accepted,3| Accepted,3| Accepted
ASKED,2| Pending,2| Pending
Checked,4| Checked,4| Checked
Ordered,5| Ordered,5| Ordered
Rejected,6| Rejected,6| Rejected

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
  s.subscriptionid,
  s.biblionumber,
  s.serialid,
  b.title,
  s.serialseq,
  s.planneddate,
  s.publisheddate,
  IF(LOCATE('<datafield tag="310"', bm.metadata) = 0 
    OR LOCATE('<subfield code="a">', bm.metadata,
      LOCATE('<datafield tag="310"', bm.metadata)) = 0 
    OR LOCATE('<subfield code="a">', bm.metadata,
      LOCATE('<datafield tag="310"', bm.metadata)) >
        LOCATE('</datafield>', bm.metadata,
          LOCATE('<datafield tag="310"', bm.metadata)),
    '',
    SUBSTRING( bm.metadata,
      LOCATE('<subfield code="a">', bm.metadata,
        LOCATE('<datafield tag="310"', bm.metadata)) + 19,
      LOCATE('</subfield>', bm.metadata,
        LOCATE('<subfield code="a">', bm.metadata,
          LOCATE('<datafield tag="310"', bm.metadata)) + 19) -
      (LOCATE('<subfield code="a">', bm.metadata,
        LOCATE('<datafield tag="310"', bm.metadata)) + 19))
  ) AS FREQUENCY,
  s.status 
FROM
  serial s,
  biblio b,
  biblioitems bi,
  biblio_metadata bm
WHERE
  s.biblionumber = b.biblionumber AND
  s.biblionumber = bi.biblionumber AND
  bm.biblionumber = b.biblionumber AND 
  MONTH(planneddate) =  10  AND
  YEAR(planneddate) = 2023 AND
  (status) BETWEEN '3' AND '5' 
ORDER BY
  s.subscriptionid ASC

Late Issues

Developer: Nicole C. Baratta, 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. Baratta, 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. Baratta, 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(m.metadata,'//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, biblio_metadata m, serialitems si
WHERE si.itemnumber=i.itemnumber  
AND i.biblionumber=b.biblionumber 
AND b.biblionumber=c.biblionumber 
AND i.biblionumber=m.biblionumber
AND si.serialid = s.serialid
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

Barcodes with leading spaces

Developer: Barton Chittenden, ByWater Solutions
Module: Circulation
Purpose: Show items where barcodes have leading spaces -- these will give 'Barcode not found' message when scanned.
Status: Complete
Note:

SELECT
    biblionumber,
    title,
    CONCAT(
        '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?',
        'op=edititem&biblionumber=', 
        biblionumber, '&itemnumber=', itemnumber, 
        '#edititem', '\">', itemnumber, '</a>' 
    ) AS itemnumber,
    concat( "'", barcode, "'" ) AS barcode
FROM
    items
    INNER JOIN biblio USING (biblionumber)
WHERE
    barcode like ' %'

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

Plugin configuration page links

Developer: Barton Chittenden, ByWater Solutions
Module: Koha Administration
Purpose: Quick links to plugin configuration pages.
Status: Complete

SELECT
    CONCAT(
        '<a href=\"/cgi-bin/koha/plugins/run.pl?class=', plugin_class, '&method=configure' '\">',
        SUBSTRING_INDEX( plugin_data.plugin_class, ':', -1 ), '</a>'
    ) AS plugin
FROM
    plugin_data

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

Most Popular Searches II

Developer: Steffi (with minor help)
Module: Search
Purpose: Find popular search terms
Status: Complete

SELECT search_history.sessionid,
       search_history.query_desc,
       search_history.type,
       search_history.total,
       search_history.time,
       borrowers.branchcode,
       borrowers.categorycode
FROM search_history
JOIN borrowers ON (search_history.userid=borrowers.borrowernumber)
JOIN branches ON (borrowers.branchcode=branches.branchcode)
WHERE borrowers.branchcode=<<
  SELECT Library|branches>>
  AND search_history.time BETWEEN <<SEARCH history TIMESTAMP BETWEEN (yyyy-mm-dd)|date>> AND <<AND (yyyy-mm-dd)|date>>

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

Circulation statistics about serial items based on the title of biblio record

Developer: Alex Buckley, Catalyst IT for Toi Ohomai Institute of Technology, New Zealand
Module: Circulation
Purpose: List circulation statistics for all items of a serial biblio record. This report accepts up to 10 biblio serial titles at a time.
Status: Complete

SELECT biblio.title AS Title, 
  items.holdingbranch AS Current_Location, 
  items.location AS Location, 
  items.ccode AS Collection, 
  items.itemnumber, 
  items.enumchron AS Item_Info, 
  (SELECT count(*)
    FROM statistics
    WHERE type="issue" AND
    statistics.itemnumber=items.itemnumber) AS Number_of_issues
FROM biblio 
LEFT JOIN items ON biblio.biblionumber=items.biblionumber
WHERE items.itype="SERIAL" AND
  biblio.title IN (<<Enter first title>>,
    <<Enter second title>>,
    <<Enter third title>>,
    <<Enter fourth title>>,
    <<Enter fifth title>>,
    <<Enter sixth title>>,
    <<Enter seventh title>>,
    <<Enter eigth title>>,
    <<Enter ninth title>>,
    <<Enter tenth title>>)  
ORDER BY title ASC,
  Current_Location ASC

List of Lists

Developer: Nicole C. Baratta, 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>>

Get list of OCLC numbers in catalog

Developer: Aleisha Amohia, Catalyst IT
Module: Cataloging
Purpose: Get list of OCLC numbers to upload holdings. Note: this query excludes items that have been checked out in the past or have a reference itemtype.
Status: Complete

SELECT
DISTINCT(
SUBSTRING_INDEX(
SUBSTRING_INDEX(ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]'), '(OCoLC)', -1)
,' ', 1)
) 'OCLC Number'
FROM biblio b  
LEFT JOIN items i USING (biblionumber)  
LEFT JOIN biblioitems m USING (biblionumber)
WHERE (i.issues IS NOT NULL
OR i.itype = 'WG_REF')
AND ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') LIKE '%(OCoLC)%';

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 thumbnails of all titles with local cover images

Developer: Andrew Fuerste-Henry, Dubuque County Library District
Module: Misc
Purpose: Show thumbnails of local cover images
Status: Complete

SELECT title, 
concat('<img src="/cgi-bin/koha/catalogue/image.pl?thumbnail=',c.imagenumber,'&amp;imagenumber=',c.imagenumber,'"') as cover
FROM biblio b 
LEFT JOIN cover_images c USING (biblionumber)
WHERE c.imagenumber is not null

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

Items Added/Accessioned between date range and its investment by Collection Code

Developer: Satisha MV, Govt.Engineering College, Hassan
Module: Misc
Purpose: To know No. of Titles(Records), No. of Volumes(Items) added/accessioned between date range and its investment by Collection Code. Here collection code refers to

departments or Subjects or Branches or Programmes

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
WHERE date(dateaccessioned) BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> 
GROUP BY ccode

Zero Results Searches

Developer: Spencer Smith
Module: Search
Purpose: This returns a list of search queries in a selected date range that returned ZERO results at the time they were run. This can help us spot blind spots in our collection development practices.
Status: Completed

SELECT COUNT(*) AS Count,
       RIGHT(
              query_desc, 
              Length(query_desc) - Instr(query_desc, ':')
       ) AS 'Search string', total
FROM   search_history
WHERE Total= 0
AND time BETWEEN <<Deleted BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP  BY RIGHT(query_desc, Length(query_desc) - Instr(query_desc, ':'))
ORDER  BY COUNT(*) DESC

PayPal Transactions (revised for 19.11)

Developer: Kelly McElligott, ByWater Solutions
Module: Circulation
Purpose: Tracking Paypal accounts
Status: Completed
Works with: 19.11

SELECT
CONCAT(
'<a target="_blank" href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',
borrowers.borrowernumber,
'\">',
borrowers.borrowernumber,
'</a>'
) AS Link,
borrowers.surname,
borrowers.firstname,
borrowers.cardnumber,
accountlines.credit_type_code, accountlines.status,
accountlines.date,
accountlines.amount,
accountlines.note
FROM
accountlines
LEFT JOIN borrowers using (borrowernumber)
WHERE accountlines.credit_type_code is not null AND
accountlines.note = 'PayPal' AND
date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-dd)|date>>
ORDER BY
date,
borrowers.surname

Closures in the Next Week

Developer: Andrew Fuerste-Henry, Dubuque County Library District
Module: Tools
Purpose: Produce a list of all closures from the Koha calendar in the next week
Status: Completed
Works with: 21.11

SELECT str_to_date(date,'%Y-%m-%d') as date, title, group_concat(branchcode separator ', ')
FROM
(
SELECT date, title, branchcode
FROM (
		SELECT concat(year,'-',month,'-',day) as date, title, branchcode
		FROM special_holidays
        WHERE isexception=0
     ) tinkywinky

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate()),'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
	 ) dipsy

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate())+1,'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
	 ) lala

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 1 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 6 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 5 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 4 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 3 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 2 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 1 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
) tubbies
WHERE date between curdate() and date_add(curdate(),interval 1 week)
GROUP BY date
ORDER BY date

Closures in the Next 4 Weeks for Specific Library

Developer: Andrew Fuerste-Henry, Dubuque County Library District / Modified by Christopher Brannon
Module: Tools
Purpose: Produce a list of all closures from the Koha calendar in the next 4 weeks
Status: Completed
Works with: 21.11

SELECT date, title
FROM
(
SELECT date, title, branchcode
FROM (
		SELECT concat(year,'-',month,'-',day) as date, title, branchcode
		FROM special_holidays
        WHERE isexception=0 AND branchcode = <<Library|branches>>
     ) tinkywinky

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate()),'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
         AND branchcode = <<Library|branches>>
	 ) dipsy

UNION ALL

SELECT date, title, branchcode
FROM (
		SELECT concat(year(curdate())+1,'-',month,'-',day) as date, title, branchcode
        FROM repeatable_holidays
        WHERE concat(year(curdate()),'-',month,'-',day,branchcode) not in
        	(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
         AND branchcode = <<Library|branches>>
	 ) lala

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 1 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 6 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 5 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 4 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 3 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 2 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 1 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_1
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 2 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 13 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 12 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 11 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 10 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 9 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 8 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_2
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 3 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 20 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 19 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 18 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 17 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 16 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 15 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_3
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>

UNION ALL

SELECT date, title, branchcode
FROM (
	SELECT CASE 
	WHEN dayofweek(curdate()) = weekday+1 THEN date_add(curdate(), interval 4 week)
    WHEN dayofweek(curdate()) = (weekday + 2) THEN date_add(curdate(), interval 27 day)
    WHEN dayofweek(curdate()) = (weekday + 3) THEN date_add(curdate(), interval 26 day)
    WHEN dayofweek(curdate()) = (weekday + 4) THEN date_add(curdate(), interval 25 day)
    WHEN dayofweek(curdate()) = (weekday + 5) THEN date_add(curdate(), interval 24 day)
    WHEN dayofweek(curdate()) = (weekday + 6) THEN date_add(curdate(), interval 23 day)
    WHEN dayofweek(curdate()) = (weekday + 7) THEN date_add(curdate(), interval 22 day)
    END as date, title, branchcode
FROM repeatable_holidays
	WHERE weekday is not null
    ) po_4
WHERE date not in         	
			(
            SELECT concat(year,'-',month,'-',day,branchcode)
			FROM special_holidays
       		WHERE isexception=1
            )
AND branchcode = <<Library|branches>>
) tubbies
WHERE date between curdate() and date_add(curdate(),interval 4 week)
GROUP BY date

Get list of deleted items from background job

Developer: Jason Robb (SEKLS)
Module: Admin/Tools
Purpose: Pulls some info about deleted items within a batch deletion job
Status: Completed
Works with: 22.05
Note: This will only give as many lines as the dummy joined indexes -- I have mine set to 1000 which is my batch delete cap

SELECT
  idx,
  json_extract(data,CONCAT('$.report.deleted_itemnumbers[', idx, ']')) AS del_inum,
  barcode,
  COALESCE(b.title, db.title) AS Title,
  COALESCE(b.author, db.author) AS Author,
  a1.lib AS Loc,
  a2.lib AS Ccode
FROM background_jobs
JOIN (
SELECT 0 AS idx UNION 
SELECT 1 AS idx UNION
SELECT 2 AS idx UNION
SELECT 3 AS idx UNION
SELECT 4 AS idx UNION
/* can add more lines as needed */
SELECT 5 AS idx
  ) AS indexes
LEFT JOIN deleteditems di ON (json_extract(data,CONCAT('$.report.deleted_itemnumbers[', idx, ']')) = di.itemnumber)
LEFT JOIN biblio b ON (di.biblionumber=b.biblionumber)
LEFT JOIN deletedbiblio db ON (di.biblionumber=db.biblionumber)
LEFT JOIN authorised_values a1 ON (a1.authorised_value = di.permanent_location AND a1.category = 'LOC')
LEFT JOIN authorised_values a2 ON (a2.authorised_value = di.ccode AND a2.category = 'CCODE')
WHERE background_jobs.id=<<Deleted item job ID>> AND barcode <> ''
LIMIT 1000

Example of dynamic email content in report

Developer: Christopher Brannon (Coeur d'Alene Public Library / Cooperative Information Network)
Purpose: Demonstrate how to incorporate links in reports that create dynamic emails with mailto, subject, and body information.
Status: Completed
Works with: 23.05

SELECT CONCAT('<div style="border:1px solid black;"><b>Due: </b>',DATE(i.date_due),'<br/><b>Barcode: </b><a href="/cgi-bin/koha/catalogue/moredetail.pl?type=&itemnumber=',i2.itemnumber,'" target="_blank">',i2.barcode,'</a><br/><b>Title: </b>',b.title,'<br/><b>Call #: </b>',i2.itemcallnumber,'<br/><b>Loaned to: </b>#',b2.cardnumber,' - ',b2.firstname,' ',b2.surname,' (',IF(b2.email!='',CONCAT('<a href="mailto:',b2.email,'?subject=',b.title,' Overdue&body=',b2.firstname,',%0D%0AWe are writing to let you know that ',b.title,' is overdue.  Please return it as soon as possible so others may also enjoy using it.%0D%0A%0D%0AIf you have already returned this item, you may disregard this notice.%0D%0A%0D%0ASincerely,%0D%0A',b3.branchname,'">',b2.email,'</a>'),IF(b2.phone,b2.phone,'NO CONTACT INFO')),') [',b2.categorycode,']</div>') AS 'OVERDUE REPORT'
FROM issues i
LEFT JOIN items i2 USING (itemnumber)
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN borrowers b2 USING (borrowernumber)
LEFT JOIN branches b3 ON (i2.homebranch = b3.branchcode)
WHERE i.date_due < NOW() - INTERVAL 2 DAY AND i2.damaged='0'

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?
Séverine Queune	Acquisitions	Informations about orders linked to a suggestion		I'd like have informations about an order linked to a suggestion, especially the different dates existing in the different tables (suggestions - aqorders - aqbasket) to calculate the DATEDIFF between suggestions.suggesteddate, aqorders.entrydate, aqbasket.closedate and aqorders.datereceived but I can't found out how to link these datas ! All I found is to link them using biblionumber but it's not reliable when there are several orders. Is there someone to help me ?
Séverine Queune - 2019-10-11	Saved reports	Allow resqust on a list of barcode		I'd like to open my "where" condition to allow people to request on several barcodes, so they won't have to edit the request itself and save the list in it. I tried several things, like WHERE barcode IN (REPLACE(,' ', '\',\'')) for a liste of barcode separated by a space but none of them worked. Does someone have an idea if I can do it ?