[Koha] In House Use

[Carlos Lopez]

Hi Ma. Victoria

We have a slightly different query that lists author, title, branch, location, collection (ccode), call number, copy, barcode, issue/checkout count, renewal count and local use count - you should be able to adapt it to your needs fairly easily (add limits for date, location and branch). We started from the biblio table rather than from statistics, and added an inner join for items and a left join for statistics (you should be able to use an inner join or a left join (I can never remember which does what) for biblioitems, though on second thought I'm not sure why you might be calling up that table in the first place)

> SELECT b.author AS AUTHOR, b.title AS TITLE, i.homebranch, i.location, i.ccode AS COLLECTION, i.itemcallnumber AS "CALL NUMBER", i.copynumber AS COPY, i.barcode AS BARCODE, 
>	COUNT( IF( s.type = 'issue', s.itemnumber, NULL)) AS "ISSUE COUNT",
>	COUNT( IF( s.type = 'renew', s.itemnumber, NULL)) AS "RENEWAL COUNT",
>	COUNT( IF( s.type = 'localuse', s.itemnumber, NULL)) AS "LOCAL USE COUNT"
> FROM
>	biblio b
> INNER JOIN items i USING (biblionumber)
> LEFT JOIN statistics s USING (itemnumber)
> WHERE s.type IN ('issue', 'renew', 'localuse')
> GROUP BY itemnumber
> ORDER BY i.homebranch, i.location, i.itemcallnumber, i.copynumber

Good luck!

With kind regards from the Dalton McCaughey Library Team

Carlos Lopez

Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library at dml.vic.edu.au | library.dmlibrary.org.au 

-----Original Message-----
From: Koha <koha-bounces at lists.katipo.co.nz> On Behalf Of Ma. Victoria H. Silva-Manuel
Sent: Wednesday, March 29, 2023 2:39 PM
To: koha <koha at lists.katipo.co.nz>
Subject: [Koha] In House Use

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


I am using the SQL report below to generate in house use:

> SELECT count(statistics.type), items.itemcallnumber
> FROM borrowers LEFT JOIN statistics on
> (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items on
> (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems on
> (biblioitems.biblioitemnumber = items.biblioitemnumber)
> WHERE statistics.type='localuse' AND date(statistics.datetime) BETWEEN
> <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>   AND
> statistics.branch=<<Pick your branch|branches>> AND items.location =
> <<Location|loc>>
> GROUP BY items.itemcallnumber
> ORDER BY items.itemcallnumber asc


The original SQL only shows the count, I added the call number and filtered
it by library and location successfully but I cannot add the title. I am
always getting an error.
What should I do?
--
Ma. Victoria H. Silva-Manuel
Registered Librarian, 3892
_______________________________________________

Koha mailing list  https://urldefense.com/v3/__http://koha-community.org__;!!DVrgiXjqvl2yLjg!awLbOIv_hmNvH3eTVS3SOke5WYZEeKLdkF8ZRi7sVzaK0BaPSowsNR68OmmhmlrxePHK4SXrvEyHXgoeatAZH4ry$
Koha at lists.katipo.co.nz
Unsubscribe: https://urldefense.com/v3/__https://lists.katipo.co.nz/mailman/listinfo/koha__;!!DVrgiXjqvl2yLjg!awLbOIv_hmNvH3eTVS3SOke5WYZEeKLdkF8ZRi7sVzaK0BaPSowsNR68OmmhmlrxePHK4SXrvEyHXgoeatnYQZk8$