Koha Test Wiki MW Canasta on Koha Portainer
Test major Koha Wiki changes or bug fixes here without fear of breaking the production wiki.
For the current Koha Wiki, visit https://wiki.koha-community.org .SQL Reports Library
Jump to navigation
Jump to search
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
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.
| 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 items 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
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 != '';
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)>> ]")
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(marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]') AS sub1,
ExtractValue(marcxml,'//datafield[@tag="651"]/subfield[@code>="a"]') AS sub2,
ExtractValue(marcxml,'//datafield[@tag="600"]/subfield[@code>="a"]') AS sub3,
ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]') AS sub4,
ExtractValue(marcxml,'//datafield[@tag="611"]/subfield[@code>="a"]') AS sub5,
ExtractValue(marcxml,'//datafield[@tag="630"]/subfield[@code>="a"]') AS sub6,
ExtractValue(marcxml,'//datafield[@tag="648"]/subfield[@code>="a"]') AS sub7,
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code>="a"]') AS sub8,
ExtractValue(marcxml,'//datafield[@tag="654"]/subfield[@code>="a"]') AS sub9,
ExtractValue(marcxml,'//datafield[@tag="655"]/subfield[@code>="a"]') AS sub10,
ExtractValue(marcxml,'//datafield[@tag="656"]/subfield[@code>="a"]') AS sub11,
ExtractValue(marcxml,'//datafield[@tag="657"]/subfield[@code>="a"]') AS sub12,
ExtractValue(marcxml,'//datafield[@tag="658"]/subfield[@code>="a"]') AS sub13,
ExtractValue(marcxml,'//datafield[@tag="662"]/subfield[@code>="a"]') AS sub14
FROM biblioitems) AS subjects
WHERE sub1 = ""
AND sub2 = ""
AND sub3 = ""
AND sub4 = ""
AND sub5 = ""
AND sub6 = ""
AND sub7 = ""
AND sub8 = ""
AND sub9 =""
AND sub10 = ""
AND sub11 = ""
AND sub12 = ""
AND sub13 = ""
AND sub14 =""
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
- 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, '<', '<'), '>', '>'), '</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
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
Accounting Reports (Fines/Credits/Etc)
Fines with Patron & Item Info
- Developer: Kyle M Hall
- Module: Accounting
- Purpose: List of unpaid fines with patron and item information
- Status: Complete - Updated 6/4/19
SELECT
b.surname, b.firstname, b.email, bib.title, i.barcode,
a.amountoutstanding, ni.issuedate, ni.date_due,
IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate
FROM accountlines a
LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber )
LEFT JOIN items i ON ( a.itemnumber = i.itemnumber )
LEFT JOIN biblio bib ON ( i.biblionumber = bib.biblionumber )
LEFT JOIN ( SELECT * FROM issues UNION SELECT * FROM old_issues ) ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber )
WHERE
a.amountoutstanding > 0
GROUP BY a.accountlines_id
ORDER BY b.surname, b.firstname, ni.timestamp DESC
List patrons detailed information of fine ( including title info for each item )
- Developer: Pankaj Kumar Sharma
- Module: Accounting
- Purpose: Useful for library professionals who want to fetch fine against each line item in detail.
- Status: Completed
- Works/Tested with: Koha Version-18.05.04.000, MySQL Version-14.14
SELECT FORMAT(accountlines.amountoutstanding,2) as fine, borrowers.surname, borrowers.firstname, borrowers.cardnumber,
issues.date_due, (TO_DAYS( date_due)-TO_DAYS(curdate())) AS 'days overdue', items.itype, items.itemcallnumber, items.barcode,
items.homebranch, biblio.title, biblio.author
FROM
borrowers left JOIN accountlines ON (borrowers.borrowernumber=accountlines.borrowernumber)
JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber) LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
where
(TO_DAYS( date_due)-TO_DAYS(curdate())) <=0
order by borrowers.borrowernumber
Patrons with Fines
- Developer: Katrin Fischer
- Module: Accounting
- Purpose: List patrons with their fine amounts
- Status: Complete
SELECT
(SELECT CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ', b.firstname,'</a>')
FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron,
format(sum(amountoutstanding),2) AS 'Outstanding',
(SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'
FROM
accountlines a, borrowers b
WHERE
(SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber) > '0.00'
AND a.borrowernumber = b.borrowernumber
GROUP BY
a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC
Patrons with Fines at Branch
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Accounting
- Purpose: List patrons with their fine amounts limited by branch
- Status: Complete
select p.surname, p.firstname, p.borrowernumber, p.cardnumber,
format(sum(a.amountoutstanding),2) as owes
from borrowers p
left join accountlines a using (borrowernumber)
where a.amountoutstanding > 0 and p.branchcode=<<Branch|branches>>
group by a.borrowernumber
Patrons with More Than an Amount in Fines
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Accounting
- Purpose: List patrons with who owe greater than or equal to an amount entered when the report is run.
- Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, p.address, p.address2, p.city, p.state, p.phone, p.branchcode as 'patron branch',
p.debarred , p.debarredcomment, p.dateexpiry, format(sum(a.amountoutstanding),2) as 'amount owed'
FROM borrowers p
left join accountlines a using (borrowernumber)
GROUP BY a.borrowernumber
HAVING sum(a.amountoutstanding) >= <<Owe more than>>
ORDER BY p.surname, p.firstname
Patrons with credits
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose:
- Status: Complete
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, address, city, zipcode, round(Sum(accountlines.amountoutstanding),2) AS 'total owed'
FROM accountlines LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) WHERE amountoutstanding != 0
GROUP BY accountlines.borrowernumber having sum(accountlines.amountoutstanding) < 0
ORDER BY borrowers.surname, borrowers.firstname
Collections Report for Unique Management
- Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS
- Module: Accounting
- Purpose: Outputs patrons with fines in certain categories, with more than $X in fines, and no fine payments in the last 60 days.
- Status: Completed
- Note: Updated by Jared Camins-Esakov, C & P Bibliography Services on 16 May 2012. If you were using a previous version of this report, please update the report and contact Unique immediately to tell them to disregard previous reports. The logic was reversed.
- Note: This report, with the NOT IN line, eliminates any patron who has *ever* paid on their account - it is too broad and we've removed it from our reports we use at NEKLS now (see the next report for an updated specific version).
SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname,
borrowers.address, borrowers.city, borrowers.zipcode, borrowers.email, borrowers.phone,
borrowers.dateofbirth, borrowers.debarred, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due
FROM borrowers, accountlines
WHERE borrowers.categorycode IN ('BONN-CITY', 'OTT-CITY')
AND borrowers.borrowernumber
NOT IN (SELECT distinct borrowernumber FROM accountlines WHERE accountlines.date < DATE_SUB(CURDATE(),INTERVAL 60 DAY) AND (accountlines.accounttype IN ('PAY', 'C') ) )
AND borrowers.borrowernumber = accountlines.borrowernumber
GROUP BY borrowers.borrowernumber
HAVING SUM(accountlines.amountoutstanding) >= 25.00
ORDER BY borrowers.surname ASC;
- Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
- Module: Accounting
- Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
- Status: Completed
- Note: New Delinquent Report - first report sent to Unique, sent weekly after that
- Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT borrowers.cardnumber,
borrowers.borrowernumber,
borrowers.surname,
borrowers.firstname,
borrowers.address,
borrowers.city,
borrowers.zipcode,
borrowers.phone,
borrowers.mobile,
borrowers.phonepro AS "Alt Ph 1",
borrowers.B_phone AS "Alt Ph 2",
borrowers.branchcode,
categories.category_type AS "Adult or Child",
borrowers.dateofbirth, MAX(accountlines.date) AS "Most recent charge",
FORMAT(SUM(accountlines.amountoutstanding),2) AS Due
FROM accountlines
LEFT JOIN borrowers USING(borrowernumber)
LEFT JOIN categories USING(categorycode)
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') AND
( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) AND
accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) AND
accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day)
GROUP BY borrowers.borrowernumber
HAVING Due >=25.00
ORDER BY borrowers.surname ASC
- Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
- Module: Accounting
- Purpose: Produces report of patrons in particular branch codes with their sort1 field set to "yes" and fines of more than $25.
- Status: Completed
- Note: Update report - weekly report sent to Unique
SELECT borrowers.borrowernumber, borrowers.surname, borrowers.firstname, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due
FROM accountlines
LEFT JOIN borrowers USING(borrowernumber)
LEFT JOIN categories USING(categorycode)
WHERE borrowers.categorycode IN (BRANCHCODES SEPARATED BY COMMAS)
AND borrowers.sort1 = 'yes'
GROUP BY borrowers.borrowernumber
ORDER BY borrowers.surname ASC
- Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
- Module: Accounting
- Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
- Status: Completed
- Note: New Delinquent Report Linked - used to easily add the $10 fee and set the sort1 field to "yes" for new delinquent accounts
- Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT CONCAT('<a href=\"/cgi-bin/koha/members/maninvoice.pl?borrowernumber=', borrowers.borrowernumber, '\" target="_blank">', borrowers.cardnumber, '</a>') AS "Link to Fines",
borrowers.borrowernumber,
borrowers.surname,
borrowers.firstname,
borrowers.address,
borrowers.city,
borrowers.zipcode,
borrowers.phone,
borrowers.mobile,
borrowers.phonepro AS "Alt Ph 1",
borrowers.B_phone AS "Alt Ph 2",
borrowers.branchcode,
categories.category_type AS "Adult or Child",
borrowers.dateofbirth,
MAX(accountlines.date) AS "Most recent charge",
FORMAT(SUM(accountlines.amountoutstanding),2) AS Due
FROM accountlines
LEFT JOIN borrowers USING(borrowernumber)
LEFT JOIN categories USING(categorycode)
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') AND
( borrowers.sort1 != 'yes' OR borrowers.sort1 IS NULL ) AND
accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) AND
accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day)
GROUP BY borrowers.borrowernumber
HAVING Due >=25.00
ORDER BY borrowers.surname ASC
No checkouts since accruing a fine
- Developer: Ian Bays (PTFS Europe) on behalf of BASE Library
- Module: Accounting
- Purpose: This report can help to show what effect fines have and whether readers return after accruing.
- Status: One Example
SELECT
CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',borrowers.borrowernumber,'\" target="_blank">', borrowers.borrowernumber, '</a>') AS borrowernumber,
(SELECT
MAX(issuedate)
FROM issues
WHERE borrowernumber = borrowers.borrowernumber)
'latest issue',
(SELECT
MAX(issuedate)
FROM old_issues
WHERE borrowernumber = borrowers.borrowernumber)
'latest old iss',
(SELECT
MAX(date)
FROM accountlines
WHERE (accounttype = 'F'
OR accounttype = 'FU'
OR accounttype = 'O')
AND borrowernumber = borrowers.borrowernumber)
'latest fine'
FROM borrowers
WHERE (SELECT
MAX(issuedate)
FROM issues
WHERE borrowernumber = borrowers.borrowernumber)
< (SELECT
MAX(date)
FROM accountlines
WHERE (accounttype = 'F'
OR accounttype = 'FU'
OR accounttype = 'O')
AND borrowernumber = borrowers.borrowernumber)
AND (SELECT
MAX(issuedate)
FROM old_issues
WHERE borrowernumber = borrowers.borrowernumber)
< (SELECT
MAX(date)
FROM accountlines
WHERE (accounttype = 'F'
OR accounttype = 'FU'
OR accounttype = 'O')
AND borrowernumber = borrowers.borrowernumber)
Incremental Fines with Patron & Item Info
- Developer: Ramprasad Joshi
- Module: Accounting
- Purpose: List of unpaid fines with patron and item information, with an incremental charge: $1 per day the first fortnight overdue, $2 for the next, $5 daily after that; it can be tailored by patron category.
- Status: One Example
SELECT
borrowers.cardnumber,borrowers.categorycode,borrowers.surname,issues.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS daysoverdue,
items.barcode AS 'Accession Number',
biblio.title,biblio.author,
IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=15,(TO_DAYS(curdate())-TO_DAYS( date_due)),
IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=30,2*(TO_DAYS(curdate())-TO_DAYS( date_due))-15,5*(TO_DAYS(curdate())-TO_DAYS( date_due))-105))
AS fine
FROM borrowers
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (borrowers.categorycode=<<Patron Category|categorycode>>) AND (TO_DAYS(curdate())-TO_DAYS(date_due)) > '0'
ORDER BY borrowers.cardnumber ASC
Total Forgiven Fines Today
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Accounting
- Purpose: Total amount forgiven in fines today
- Status: Complete
SELECT SUM(amount)
FROM accountlines
WHERE DATE(timestamp)=CURDATE() AND (accounttype='FOR' OR accounttype='W')
Total Fines Paid (Date Range)
- Developer: Dr Vimal Kumar V., Mahatma Gandhi University Library
- Module: Accounting
- Purpose: Generate list of overdue paid in a period. Applicable to Koha version since 19.11.
- Status: Complete
SELECT b.cardnumber AS 'Card Number',b.surname AS 'Name',FORMAT(ABS(a.amount), 2) AS 'Amount'
FROM borrowers b
JOIN accountlines a
WHERE b.borrowernumber = a.borrowernumber AND a.credit_type_code = 'payment' AND a.date
BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD |date>>
AND b.branchcode=<<Enter patrons library|branches>> AND categorycode LIKE <<Enter Category borrowers|categorycode>>
Total Fines Paid Today
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Accounting
- Purpose: Total amount paid in fines today
- Status: Complete
SELECT SUM(amount)
FROM accountlines
WHERE DATE(timestamp)=CURDATE() AND (accounttype='PAY' OR accounttype='C')
Yesterday's Fines by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: Fines charged yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'F' or accounttype = 'FU' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's Fines
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: Fines charged yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines WHERE (accounttype = 'F' or accounttype = 'FU' ) and date = (now() - interval 1 day)
Yesterday's Lost Item Charges by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: lost items charged yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's Lost Item Charges
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: lost items charged yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
Yesterday's Account Management Fees by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: acct mgt charged yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's Account Management Fees
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: acct mgt fees charged yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
Yesterday's Forgiven Charges by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: forgiven charges yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's Forgiven Charges (entire system)
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: forgiven charges yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
Yesterday's Sundry Fees by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: sundry fees yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'M') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's Sundry Fees (entire system)
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: sundry fees charged yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines WHERE (accounttype = 'M') and date = (now() - interval 1 day)
Yesterday's Credits by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: credits yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'C') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's Credits (entire system)
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: credits yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines WHERE (accounttype = 'C') and date = (now() - interval 1 day)
Yesterday's New Card Fees by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: new card fees yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'N') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's New Card Fees (entire system)
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: new card fees yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines WHERE (accounttype = 'N') and date = (now() - interval 1 day)
Yesterday's Payments by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: payments yesterday for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'PAY') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
Yesterday's Payments (entire system)
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: payments yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines WHERE (accounttype = 'PAY') and date = (now() - interval 1 day)
Year to Date Fines by branch
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: year to date fines charged for a particular branch (edit branchcode as needed)
- Status: Complete
SELECT
round(Sum(accountlines.amount),2) AS 'Fines Charged YTD'
FROM accountlines
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber)
WHERE (accounttype = 'F' or accounttype = 'FU' ) and YEAR(date) = YEAR(NOW()) and borrowers.branchcode = 'LIB'
Year to Date Fines (entire system)
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: year to date fines charged (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged YTD'
FROM accountlines
WHERE (accounttype = 'F' or accounttype = 'FU' ) and YEAR(date) = YEAR(NOW())
Total Fines Owed
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: total amount of fines owed (entire system)
- Status: Complete
SELECT FORMAT(Sum(accountlines.amountoutstanding),2) FROM accountlines
Writeoff fine (Date Range wise)
- Developer: Nikunj Tyagi, DPL
- Module: Accounting
- Purpose: writeoff Amount (Date range wise) with patron details (entire system)
- Status: Complete
SELECT borrowers.borrowernumber,
borrowers.cardnumber,
accountlines.amount,
accountlines.date
FROM accountlines, borrowers
WHERE borrowers.borrowernumber = accountlines.borrowernumber AND
accounttype = 'W'
AND date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>
Write-off Fines in Date Range (with Circulation Note, Payment Notes, Timestamps, and Manager Name)
- Developer: Alex Chen, Butte County Library
- Module: Accounting
- Purpose: Provide a list of write-off fines in a date range with Circulation Note, Timestamps, Payment Notes, and staff who performed the actions for Accounting and Auditing purposes.
- Status: Complete
SELECT
CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=', patrons.borrowernumber, '\" target=/"_blank\">', patrons.cardnumber, '</a>' ) AS 'Card Number',
CONCAT(patrons.firstname, ' ', patrons.surname) AS 'Patron Name',
patrons.borrowernotes AS 'Circulation Note',
patrons.opacnote AS 'OPAC Note',
patrons.categorycode AS 'Patron Category',
accountlines.amount AS 'Amount',
accountlines.timestamp AS 'Transaction Timestamp',
accountlines.description AS 'Description',
accountlines.note AS 'Payment Notes',
CONCAT(managers.firstname, ' ', managers.surname) AS 'Manager Name'
FROM accountlines
LEFT JOIN borrowers patrons ON (accountlines.borrowernumber = patrons.borrowernumber)
LEFT JOIN borrowers managers ON (accountlines.manager_id = managers.borrowernumber)
WHERE
(accountlines.date BETWEEN <<Fine Waived Between |date>> AND <<and |date>>)
AND accountlines.accounttype = 'W'
AND patrons.categorycode = <<Patron Category |categorycode>>
Sum of Total Write-off Amount by Patron Category in a Date Range
- Developer: Alex Chen, Butte County Library
- Module: Accounting
- Purpose: Provide a sum of total write-off amount by patron categorycode in a date range
- Status: Complete
SELECT
patrons.categorycode AS 'Patron Category',
SUM(ABS(accountlines.amount)) AS 'Total Write-Off Amount'
FROM accountlines
LEFT JOIN borrowers patrons ON (accountlines.borrowernumber = patrons.borrowernumber)
WHERE
(accountlines.date BETWEEN <<Fine Waived Between |date>> AND <<and |date>>)
AND accountlines.accounttype = 'W'
GROUP BY patrons.categorycode
Payment (fine) detail (Date Range)
- Developer: Nikunj Tyagi, DPL
- Module: Accounting
- Purpose: Payment (Date range wise) with patron details (entire system)
- Status: Complete
SELECT borrowers.borrowernumber,
borrowers.cardnumber,
accountlines.amount,
accountlines.date
FROM accountlines, borrowers
WHERE borrowers.borrowernumber = accountlines.borrowernumber AND
accounttype = 'pay' AND
date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>
Yesterday's Amount Collected (entire system)
- Developer: Jane Wagner, PTFS
- Module: Accounting
- Purpose: amount actually collected yesterday (entire system)
- Status: Complete
SELECT round(Sum(accountlines.amount),2) AS 'Paid Yesterday'
FROM accountlines
WHERE (accounttype = 'PAY' ) and date = (now() - interval 1 day)
Amount Collected in specific Date Range (entire system)
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Accounting
- Purpose: Asks you to enter the date range for which you would like to see all of the money collected at all branches.
- Status: Complete
select FORMAT(abs(sum(amount)),2) as 'Total Collected'
from accountlines
where (accounttype='C' or accounttype='PAY') and
timestamp between <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
Accounting for date range
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Accounting
- Purpose: List of all accounting details in date range
- Status: Complete
SELECT
CASE accounttype
WHEN 'A' THEN 'Account management fee'
WHEN 'C' THEN 'Credit'
WHEN 'F' THEN 'Overdue Fine'
WHEN 'FOR' THEN 'Forgiven'
WHEN 'FU' THEN 'Overdue Fine Still Accruing'
WHEN 'L' THEN 'Lost Item'
WHEN 'LR' THEN 'Lost and Returned'
WHEN 'M' THEN 'Sundry'
WHEN 'N' THEN 'New Card'
WHEN 'PAY' THEN 'Payment'
WHEN 'W' THEN 'Writeoff'
ELSE accounttype END
AS transaction, SUM(amount)
FROM accountlines
WHERE DATE(timestamp) between <<Collected between (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>
GROUP BY accounttype
Payments collected at a branch in a date range
- Developer: Nicole C. Baratta, ByWater Solutions
- Module: Accounting
- Purpose: Fines collected at a branch in a date range (uses the logged in user's branch)
- Status: Complete
SELECT FORMAT(abs(sum(a.amount)),2) AS 'Total Collected'
FROM accountlines a
LEFT JOIN borrowers p on (a.manager_id=p.borrowernumber)
WHERE a.accounttype in ('C','PAY') AND
a.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> and
p.branchcode=<<Branch|branches>>
Amount due on lost items (deleted and current)
- Developer: Barton Chittenden, ByWater Solutions
- Module: Accounting
- Purpose: Amount due on lost items (deleted and current)
- Status: Complete
SELECT
FORMAT( SUM( amountoutstanding ), 2) as 'Amount Due'
FROM
items i
left join deleteditems di using ( itemnumber )
left join accountlines a on ( a.itemnumber = COALESCE (i.itemnumber, di.itemnumber) )
WHERE
COALESCE ( i.itemlost, di.itemlost ) != 0
Forgiven fines for items checked in during a date range (Fines amnesty week check)
- Developer: Nick Clemens, ByWater Solutions
- Module: Accounting
- Purpose: Fines forgiven on books checked in during a date range
- Status: Complete
SELECT surname, firstname, cardnumber, description, amountoutstanding, itemcallnumber, holdingbranch,barcode, datetime AS CheckInDate, a.timestamp AS FineDate FROM statistics
LEFT JOIN accountlines a USING (borrowernumber,itemnumber)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
WHERE type='return' AND accounttype='FFOR' AND datetime BETWEEN <<Start date|date>> AND <<End date|date>>
Fines outstanding for items checked in during a date range (Fines amnesty week check)
- Developer: Nick Clemens, ByWater Solutions
- Module: Accounting
- Purpose: Fines outstanding on books checked in during a date range
- Status: Complete
SELECT surname, firstname, cardnumber, description, amountoutstanding, itemcallnumber, holdingbranch,barcode, datetime AS CheckInDate, a.timestamp AS FineDate FROM statistics
LEFT JOIN accountlines a USING (borrowernumber,itemnumber)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
WHERE type='return' AND amountoutstanding > 0 AND datetime BETWEEN <<Start date|date>> AND <<End date|date>>
All accounts offsets for a given borrrower
- Developer: Andrew Fuerste-Henry, ByWater Solutions
- Module: Accounting
- Purpose: Show history of credits and debits for a borrower using the account_offsets table added in 17.11
- Status: Complete
select o.id, o.credit_id, o.debit_id, o.type, o.amount, o.created_on, a.borrowernumber, a.description
from account_offsets o left join accountlines a on (o.debit_id=a.accountlines_id)
where a.borrowernumber=<<Borrower Number>>
order by o.created_on desc
PayPal payments collected in a date range
- Developer: Myka Kennedy Stephens, Lancaster Theological Seminary
- Module: Accounting
- Purpose: For Koha libraries utilizing the PayPal integration; Displays fine/fee transactions for a date range with note = "PayPal"
- Status: Complete
SELECT
CONCAT(
'<a target="_blank" href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',
borrowers.borrowernumber,
'\">',
borrowers.borrowernumber,
'</a>'
) AS Link,
borrowers.surname,
borrowers.firstname,
accountlines.date,
accountlines.amount,
accountlines.note
FROM
accountlines,
borrowers
WHERE
borrowers.borrowernumber = accountlines.borrowernumber AND
accounttype = 'pay' AND
accountlines.note = 'PayPal' AND
date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-dd)|date>>
ORDER BY
date,
borrowers.surname
Duplicate Fines
- Developer: Ian Bays, PTFS Europe wrote this report for BASE Library
- Module: Accounting
- Purpose: Shows patrons that have been erroneously fined more than once on the same day for the same issue id
- Status: Complete
select concat(date,', ',issue_id) iss_dt,
(select branchcode from borrowers where borrowernumber = accountlines.borrowernumber) branch,
CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',accountlines.borrowernumber,'\" target="_blank">', accountlines.borrowernumber, '</a>') AS borrowernumber, count(*) cnt,
group_concat(timestamp separator ', ') stamps from accountlines
where accounttype = 'F' and issue_id is not NULL group by iss_dt having cnt >1
order by branch, stamps DESC
PayPal Transactions Sorted by Branch
- Developer: Lisette Scheer, Latah County Library District/Valnet Consortium
- Module: Accounting
- Purpose: Shows paypal payments in the last month, sorted by owning library.
- Status: Complete
SELECT
Format(ABS(credits.amount), 2) AS AMT_COLLECTED,
Format(ABS(account_offsets.amount), 2) AS FEE_AMOUNT,
If(
Format((debits.amount + account_offsets.amount), 2) = 0,
"-", Format((debits.amount + account_offsets.amount), 2)
) AS PREVIOUS_PAYMENTS,
Format(debits.amountoutstanding, 2) AS FEE_STILL_OWED,
borrowerinfo.cardnumber AS FROM_PATRON,
credits.note AS PAYMENT_NOTES,
credits.date AS PAYMENT_DATE,
If(items.barcode IS NULL, "DELETED", Upper(items.barcode)) AS FOR_ITEM,
If(items.homebranch IS NOT NULL, items.homebranch, manualinvoiceinfo.branchcode) AS OWNED_BY,
debits.date AS FEE_DATE,
debits.description AS FEE_DESCRIPTION,
debits.note AS FEE_NOTE
FROM
accountlines credits
JOIN account_offsets ON account_offsets.credit_id = credits.accountlines_id
JOIN accountlines debits ON account_offsets.debit_id = debits.accountlines_id
LEFT JOIN old_issues ON debits.issue_id = old_issues.issue_id
LEFT JOIN items ON debits.itemnumber = items.itemnumber
JOIN borrowers borrowerinfo ON borrowerinfo.borrowernumber = credits.borrowernumber
LEFT JOIN borrowers manualinvoiceinfo ON manualinvoiceinfo.borrowernumber = debits.manager_id
WHERE
Month(credits.date) = Month(Now() - INTERVAL 1 MONTH) AND
Year(credits.date) = Year(Now() - INTERVAL 1 MONTH) AND
credits.note='Paypal'
GROUP BY
account_offsets.id
ORDER BY OWNED_BY
Reserves reports
Customizable Holds Queue
- Developer: Christopher Brannon, Couer d'Alene Public Library/Cooperative Information Network
- Module: Circulation
- Purpose: Recreate Holds Queue results that can be customized to your needs. This example adds statuses and report date, as well as tweaks the CART location. This serves as an example to how the report can be beefed up.
- Status: Complete
SELECT CONCAT_WS(' ',avl.lib,avd.lib,IF(i.location="CART","Recently Returned","")) AS 'Status',r.reservenotes AS Notes,concat(b.title, ' ',ExtractValue(bi.metadata, '//datafield[@tag="245"]/subfield[@code="b"]')) AS 'Title',b.author AS 'Author',(SELECT lib FROM authorised_values WHERE category="LOC" AND authorised_value=i.permanent_location) AS 'Location',i.itemcallnumber AS 'Call Number',i.barcode AS 'Barcode',CONCAT_WS(', ',p.surname,p.firstname) AS 'Patron',r.branchcode AS 'Send To',r.reservedate AS 'Date',if(r.itemnumber IS NULL,'Next Available','Item Level') AS 'Type',Date(Now()) AS 'Report Date'
FROM reserves r
LEFT JOIN hold_fill_targets hft ON r.biblionumber = hft.biblionumber AND r.borrowernumber = hft.borrowernumber
LEFT JOIN items i ON i.itemnumber = hft.itemnumber
LEFT JOIN authorised_values avl ON i.itemlost = avl.authorised_value
LEFT JOIN authorised_values avd ON i.damaged = avd.authorised_value
LEFT JOIN biblio b ON b.biblionumber = i.biblionumber
LEFT JOIN biblio_metadata bi ON b.biblionumber = bi.biblionumber
LEFT JOIN borrowers p ON p.borrowernumber = r.borrowernumber
WHERE i.holdingbranch = <<Library|branches>> AND hft.itemnumber IS NOT NULL AND avl.category = "LOST" AND avd.category = "DAMAGED"
GROUP BY Barcode, Patron
ORDER BY Location, i.itemcallnumber, i.enumchron, b.author, b.title ASC
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
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>>
Serial reports
Shows the total serial received during the month
- Developer: Nikunj Tyagi, DPL
- Module: Serial
- Purpose: Shows the total serials received with Title, Frequency, latest issue detail
- Status: Complete
SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate,
IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19)))
AS FREQUENCY FROM serial, biblio,biblioitems
WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(planneddate) = 03 AND YEAR(planneddate)= 2011 AND (status)=2
ORDER BY serial.subscriptionid ASC
missing/late/claimed serial during the month
- Developer: Nikunj Tyagi, DPL
- Module: Serial
- Purpose: Shows the total serials missing/late/claimed with Title, Frequency, latest issue detail status 3=late,4=missing,5=claimed
- Status: Complete
SELECT
serial.subscriptionid,
serial.biblionumber,
serial.serialid,
biblio.title,
serial.serialseq,
serial.planneddate,
serial.publisheddate,
IF(LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0
OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0
OR LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) >
LOCATE('</datafield>', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)),
'',
SUBSTRING( biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19,
LOCATE('</subfield>', biblioitems.marcxml,
LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -
(LOCATE('<subfield code="a">', biblioitems.marcxml,
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19))
) AS FREQUENCY,
serial.status
FROM
serial,
biblio,
biblioitems
WHERE
serial.biblionumber = biblio.biblionumber AND
serial.biblionumber = biblioitems.biblionumber AND
MONTH(planneddate) = XX AND
YEAR(planneddate) = XXXX AND
(status) BETWEEN '3' AND '5'
ORDER BY
serial.subscriptionid ASC
Late Issues
- Developer: Nicole C. 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(c.marcxml,'//datafield[@tag="222"]/subfield[@code>="a"]') AS 'MARC
222 Title',
YEAR(MIN(s.publisheddate)) AS 'begin',
YEAR(MAX(s.publisheddate)) AS 'end'
FROM serial s,items i,biblio b, biblioitems c
WHERE s.itemnumber=i.itemnumber
AND i.biblionumber=b.biblionumber
AND b.biblionumber=c.biblionumber
AND i.holdingbranch = <<Branch|branches>>
GROUP BY b.biblionumber
ORDER BY b.title
Fix Subscriptions without Subscription Start Dates
- Developer: Barton Chittenden
- Module: Serials
- Purpose: Fix serials that generate the following error message: Software error: Date::Calc::Add_Delta_YM(): not a valid date at /var/lib/koha/aarome/kohaclone/C4/Serials.pm line 2651.
- Status: Complete
select
CONCAT(
'<a href="http://',
(SELECT value
FROM systempreferences
WHERE variable='staffClientBaseURL'),
'/cgi-bin/koha/serials/subscription-history.pl?subscriptionid=',
subscriptionid,
'">', title, '</a>'
) as 'Edit Subscription for',
subscriptionid,
startdate,
histstartdate
from
subscription
inner join biblio using (biblionumber)
left join subscriptionhistory using (subscriptionid)
left join serial using (subscriptionid)
where
startdate is NULL
or histstartdate is NULL
group by subscriptionid
List of Current Periodicals and their holdings
- Developer: Joseph Alway
- Module: Serials
- Purpose: List current subscriptions.
- Status: Complete
SELECT
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN, GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings
FROM
subscription
JOIN biblio ON ( subscription.biblionumber = biblio.biblionumber )
JOIN biblio_metadata ON ( subscription.biblionumber = biblio_metadata.biblionumber )
LEFT JOIN serial ON ( subscription.subscriptionid = serial.subscriptionid )
WHERE
(subscription.closed = 0)
GROUP BY biblio.biblionumber
ORDER BY biblio.title
List of Non Current Periodicals and their holdings
- Developer: Joseph Alway
- Module: Serials
- Purpose: List closed subscriptions.
- Status: Complete
SELECT
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN, GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings
FROM
subscription
JOIN biblio ON ( subscription.biblionumber = biblio.biblionumber )
JOIN biblio_metadata ON ( subscription.biblionumber = biblio_metadata.biblionumber )
LEFT JOIN serial ON ( subscription.subscriptionid = serial.subscriptionid )
WHERE
(subscription.closed != 0)
GROUP BY biblio.biblionumber
ORDER BY biblio.title
List of Non Current Periodicals and their holdings *Special Case*
- Developer: Joseph Alway
- Module: Serials
- Purpose: List Holdings information from Marc Record and closed subscriptions. Combines two reports into one.
- Status: Complete
SELECT
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
NULL AS subscriptionid, biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN,
ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) as Holdings, NULL AS 'S. Holdings'
FROM
biblio_metadata
JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE
(ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]' ) LIKE "CR")
AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "%See%Subscriptions%for%More%Details%")
AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "%Current%issues%")
AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "")
UNION ALL
SELECT
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
biblio.title as title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN, ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) as Holdings,
GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS 'S. Holdings'
FROM
subscription
JOIN biblio ON (subscription.biblionumber = biblio.biblionumber)
JOIN biblio_metadata ON (subscription.biblionumber = biblio_metadata.biblionumber)
LEFT JOIN serial ON (subscription.subscriptionid = serial.subscriptionid)
WHERE
(subscription.closed != 0)
GROUP BY biblio.biblionumber
ORDER BY title
Troubleshooting Reports
Find data which will trigger bugs or cause unexpected behavior
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 the item waiting?
- is it set as 'not for loan'?
- is the syspref AllowNotForLoanOverride set?
- no => not available
- is the syspref AllowNotForLoanOverride set?
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>>
- 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 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
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:
| |
| 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(,' ', '\',\''))
|