[Koha] Report for holds count by bib record & branch

[Martha Fuerst]

Do you need the item type just so you can tell AV from print, etc? If so, you might be able to get it via an ExtractValue:

ExtractValue(biblioitems.marcxml, '//datafield[@tag=“942"]/subfield[@code=“c"]')) AS “item type”

It would be different for 17.05 and newer, which may require some fiddling.

Marti Fuerst
Systems Librarian
Huntsville-Madison County Public Library
915 Monroe St, Huntsville, AL 35801
http://hmcpl.org/ — mfuerst at hmcpl.org

> On Mar 27, 2018, at 12:49 PM, Cab Vinton <bibliwho at gmail.com> wrote:
> 
> Hi, all --
> 
> Did some more testing & it appears that Item Type information is not
> available when holds are placed for the Next Available item.
> 
> Can't think of an easy work-around as it's entirely possible to have
> items attached to the same bib with different item types.
> 
> Cheers,
> 
> Cab Vinton
> Plaistow Public Library
> 
> 
> On Tue, Mar 27, 2018 at 12:30 PM, Cab Vinton <bibliwho at gmail.com> wrote:
>> Hi, All --
>> 
>> I'm trying to create a report that will give me hold counts broken down by
>> bib record and the patron's branch.
>> 
>> I believe what I have is pretty close, but some of the output omits the item
>> type information.
>> 
>> This is what I have:
>> 
>> SELECT count(borrowers.branchcode) AS Holds, borrowers.branchcode,
>> biblio.title, biblio.biblionumber, biblio.datecreated, items.itype
>> FROM reserves
>> LEFT JOIN borrowers USING (borrowernumber)
>> LEFT JOIN biblio USING (biblionumber)
>> LEFT JOIN items USING (itemnumber)
>> GROUP BY biblio.biblionumber, borrowers.branchcode
>> ORDER BY count(borrowers.branchcode) DESC, biblio.title
>> 
>> And some sample output:
>> 
>> Holdsbranchcodetitlebiblionumberdatecreateditype
>> 6KIMBALLLilac girls :1420692016-04-19BOOK
>> 6KIMBALLThe woman in the window :1581622018-01-03NEWBOOK
>> 6KIMBALLVictoria. The complete second season /1585952018-01-30NEWMOVIE
>> 4SANDOWNAccidental heroes :1571562017-10-28
>> 4PLAISTOWFall from grace :1560952017-09-12
>> 4KIMBALLThe great alone /1560892017-09-12
>> 3SANDOWNI've got my eyes on you :1571502017-10-28
>> 3PLAISTOWLilac girls :1420692016-04-19
>> 3SANDOWNSweet vengeance /1571522017-10-28NEWBOOK
>> 
>> Can anyone provide a fix?
>> 
>> Many thanks in advance,
>> 
>> Cab Vinton
>> Plaistow Public Library
>> 
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha