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 <[email protected]> On Behalf Of muiru james
Sent: Friday, July 7, 2023 5:34 AM
To: [email protected]
Cc: koha <[email protected]>
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, <[email protected]> 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 | [email protected] | york.edu
>
>
>
> On Thu, Jul 6, 2023 at 2:32 PM muiru james <[email protected]> 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 [email protected]
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
_______________________________________________
Koha mailing list http://koha-community.org [email protected]
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
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
