[Koha] INVENTORY SQL

[Raymund Delahunty]

Hello James-

Last week I was asked to redesign my normal weeding report to include more information. It was quite a lot of work as my old (more lazy) SQL simply omitted items that didn't meet the criteria I was asked for. But I have something that with some tweaks might give you what you need. I was asked to include information about recent loans as well as old loans to help the decision to weed or not. When we migrated to Koha we also took in older circulation data (unusually I believe), so we have lots of historical data. To see if the report might be any use simply cut out our branch (CSM) or include one of yours, and alter the item type to yours (away from our 1W and 3W. The SQL is probably ridiculously clumsy (maybe I should have used the statistics table to save myself the grief of all the UNIONs), but it seems to work- I compared the counts of lines returned with the counts returned via the Item Search tool and they match, so every item is being reviewed for loan counts.

Ray Delahunty
University of the Arts London

SELECT
     biblionumber AS 'Biblionumber',
     CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
              biblionumber, '\">', biblionumber, '</a>' ) AS 'Link to Biblio',
     itemnumber AS 'Itemnumber',
     CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?','&itemnumber=', itemnumber, '\">',
              itemnumber, '</a>' ) AS 'Link to item',
     itemcallnumber AS 'Dewey', barcode AS 'Barcode', title AS 'Title',
     sum(count1) AS 'Loans since 1 August 2018',
     sum(count2) AS 'Loans before 1 August 2018',
     sum(count1)+sum(count2) AS 'Total loans',
     datelastborrowed AS 'Last borrowed on Koha',
     dateaccessioned AS 'Added to Koha',
     Location AS 'Location', lost AS 'Lost status'
FROM

(SELECT
     biblio.biblionumber AS biblionumber, issues.itemnumber as itemnumber,
     items.itemcallnumber as itemcallnumber, items.barcode as barcode,
     biblio.title as title,  count(issues.issue_id) as count1, 0 AS count2,
     items.datelastborrowed as datelastborrowed, items.dateaccessioned AS dateaccessioned,
     items.location AS Location,
     CASE
         WHEN items.itemlost=2 THEN 'Long overdue (lost)'
         WHEN items.itemlost=3 THEN 'Lost & paid for'
         WHEN items.itemlost=4 THEN 'Missing'
         WHEN items.itemlost=6 THEN 'Credit Control'
         WHEN items.itemlost=7 THEN 'Searching'
         ELSE ' '
     END AS Lost
FROM issues
LEFT JOIN items ON (items.itemnumber=issues.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
WHERE items.itype in ('1W', '3W')
AND items.homebranch='CSM'
AND issues.issuedate >= '2018-08-01'
AND items.itemcallnumber between concat(<<Start of Dewey number>>, '%') AND concat(<<End of Dewey number>>, '%')
GROUP BY issues.itemnumber

UNION

SELECT
     biblio.biblionumber AS biblionumber, old_issues.itemnumber as itemnumber,
     items.itemcallnumber as itemcallnumber, items.barcode as barcode,
     biblio.title as title, count(old_issues.issue_id) as count1, 0 AS count2,
     items.datelastborrowed AS datelastborrowed,  items.dateaccessioned AS dateaccessioned,
     items.location AS Location,
     CASE
         WHEN items.itemlost=2 THEN 'Long overdue (lost)'
         WHEN items.itemlost=3 THEN 'Lost & paid for'
         WHEN items.itemlost=4 THEN 'Missing'
         WHEN items.itemlost=6 THEN 'Credit Control'
         WHEN items.itemlost=7 THEN 'Searching'
         ELSE ' '
     END AS Lost
FROM old_issues
LEFT JOIN items ON (items.itemnumber=old_issues.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
WHERE items.itype in ('1W', '3W')
AND items.homebranch='CSM'
AND old_issues.issuedate >= '2018-08-01'
AND items.itemcallnumber between concat(<<Start of Dewey number>>, '%') AND concat(<<End of Dewey number>>, '%')
GROUP BY old_issues.itemnumber

UNION

SELECT
     biblio.biblionumber AS biblionumber, items.itemnumber as itemnumber, items.itemcallnumber as itemcallnumber, items.barcode as barcode,
     biblio.title as title, 0 AS count1, 0 AS count2, items.datelastborrowed AS datelastborrowed ,
     items.dateaccessioned AS dateaccessioned, items.location AS Location,
     CASE
         WHEN items.itemlost=2 THEN 'Long overdue (lost)'
         WHEN items.itemlost=3 THEN 'Lost & paid for'
         WHEN items.itemlost=4 THEN 'Missing'
         WHEN items.itemlost=6 THEN 'Credit Control'
         WHEN items.itemlost=7 THEN 'Searching'
         ELSE ' '
     END AS Lost

FROM items
LEFT JOIN old_issues ON (items.itemnumber=old_issues.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN issues ON (items.itemnumber=issues.itemnumber)
WHERE items.itype in ('1W', '3W')
AND issues.issuedate IS NULL
AND old_issues.issuedate IS NULL
AND items.homebranch='CSM'
/* AND issues.issuedate between '2018-08-01' and NOW() */
AND items.itemcallnumber between concat(<<Start of Dewey number>>, '%') AND concat(<<End of Dewey number>>, '%')
GROUP BY items.itemnumber

UNION

SELECT
     biblio.biblionumber AS biblionumber, issues.itemnumber as itemnumber,
     items.itemcallnumber as itemcallnumber, items.barcode as barcode,
     biblio.title as title,  0 AS count1, count(issues.issue_id) AS count2,
     items.datelastborrowed as datelastborrowed, items.dateaccessioned AS dateaccessioned,
     items.location AS Location,
     CASE
         WHEN items.itemlost=2 THEN 'Long overdue (lost)'
         WHEN items.itemlost=3 THEN 'Lost & paid for'
         WHEN items.itemlost=4 THEN 'Missing'
         WHEN items.itemlost=6 THEN 'Credit Control'
         WHEN items.itemlost=7 THEN 'Searching'
         ELSE ' '
     END AS Lost

FROM issues
LEFT JOIN items ON (items.itemnumber=issues.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
WHERE items.itype in ('1W', '3W')
AND items.homebranch='CSM'
AND issues.issuedate < '2018-08-01'
AND items.itemcallnumber between concat(<<Start of Dewey number>>, '%') AND concat(<<End of Dewey number>>, '%')
GROUP BY issues.itemnumber

UNION

SELECT
     biblio.biblionumber AS biblionumber, old_issues.itemnumber as itemnumber, items.itemcallnumber as itemcallnumber, items.barcode as barcode,
     biblio.title as title, 0 AS count1, count(old_issues.issue_id) as count2,
     items.datelastborrowed AS datelastborrowed,  items.dateaccessioned AS dateaccessioned,
     items.location AS Location,
     CASE
         WHEN items.itemlost=2 THEN 'Long overdue (lost)'
         WHEN items.itemlost=3 THEN 'Lost & paid for'
         WHEN items.itemlost=4 THEN 'Missing'
         WHEN items.itemlost=6 THEN 'Credit Control'
         WHEN items.itemlost=7 THEN 'Searching'
         ELSE ' '
     END AS Lost
FROM old_issues
LEFT JOIN items ON (items.itemnumber=old_issues.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
WHERE items.itype in ('1W', '3W')
AND items.homebranch='CSM'
AND old_issues.issuedate < '2018-08-01'
AND items.itemcallnumber between concat(<<Start of Dewey number>>, '%') AND concat(<<End of Dewey number>>, '%')
GROUP BY old_issues.itemnumber

UNION

SELECT
     biblio.biblionumber AS biblionumber, items.itemnumber as itemnumber, items.itemcallnumber as itemcallnumber, items.barcode as barcode,
     biblio.title as title, 0 AS count1, 0 AS count2, items.datelastborrowed AS datelastborrowed ,
     items.dateaccessioned AS dateaccessioned, items.location AS Location,
     CASE
         WHEN items.itemlost=2 THEN 'Long overdue (lost)'
         WHEN items.itemlost=3 THEN 'Lost & paid for'
         WHEN items.itemlost=4 THEN 'Missing'
         WHEN items.itemlost=6 THEN 'Credit Control'
         WHEN items.itemlost=7 THEN 'Searching'
         ELSE ' '
     END AS Lost

FROM items
LEFT JOIN old_issues ON (items.itemnumber=old_issues.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN issues ON (items.itemnumber=issues.itemnumber)
WHERE items.itype in ('1W', '3W')
AND issues.issuedate IS NULL
AND old_issues.issuedate IS NULL
AND items.homebranch='CSM'
/* AND issues.issuedate between '2018-08-01' and NOW() */
AND items.itemcallnumber between concat(<<Start of Dewey number>>, '%') AND concat(<<End of Dewey number>>, '%')
GROUP BY items.itemnumber

) As COMBINED
GROUP BY itemnumber
ORDER BY Dewey


-----Original Message-----
From: Koha <koha-bounces at lists.katipo.co.nz> On Behalf Of muiru james
Sent: Friday, July 7, 2023 5:34 AM
To: jcoehoorn at york.edu
Cc: koha <koha at lists.katipo.co.nz>
Subject: Re: [Koha] INVENTORY SQL

Hello Joel

Thank you for your email.

I need books that have been since that date and after (>=) but the date should be picked from a date picker. Then add all books on loan for that particular item type.
So that the total at a particular date shall be all books after that datelastseen plus all books on loan for each itemtype.


Example:

For itemtype 'BK'

Total = All bks with datelastseen>='2023-06-30' AND all books on loan for itemtype 'BK'.

I have itemtype BK AND JBK and wish that one report would be used for both itemtypes.

Thank you for finding time to assist me.

Warm Regards
James

On Fri, 7 Jul 2023, 00:22 Coehoorn, Joel, <jcoehoorn at york.edu> wrote:

> Do you need to match the exact date last seen, books that have been
> seen since that date, or books that haven't been seen since before that date?
>
> Also, the part about combining books on loan is not clear. Can you
> explain that in more detail?
>
> *Joel Coehoorn*
> Director of Information Technology
> *York University*
> Office: 402-363-5603 | jcoehoorn at york.edu | york.edu
>
>
>
> On Thu, Jul 6, 2023 at 2:32 PM muiru james <muirunyeri at gmail.com> wrote:
>
>> Hello team,
>>
>> Im trying to create a sql query that will return a count of books
>> that will have a datelastseen from a date picker and combine/add all
>> books on loan for the particular itemtype.
>>
>> I have 2 different itemtypes and would like a count for each itemtype
>> to include those with a datelastseen and all those onloan for the itemtype.
>>
>> Any ideas/help will really be appreciated.
>>
>> Warm regards
>>
>> James
>> _______________________________________________
>>
>> Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
_______________________________________________

Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
This email and any attachments are intended solely for the addressee and may contain confidential information. If you are not the intended recipient of this email and/or its attachments you must not take any action based upon them and you must not copy or show them to anyone. Please send the email back to us and immediately and permanently delete it and its attachments. Where this email is unrelated to the business of University of the Arts London or of any of its group companies the opinions expressed in it are the opinions of the sender and do not necessarily constitute those of University of the Arts London (or the relevant group company). Where the sender's signature indicates that the email is sent on behalf of UAL Short Courses Limited the following also applies: UAL Short Courses Limited is a company registered in England and Wales under company number 02361261. Registered Office: University of the Arts London, 272 High Holborn, London WC1V 7EY