Report for holds count by bib record & branch
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 6 KIMBALL Lilac girls : 142069 2016-04-19 BOOK 6 KIMBALL The woman in the window : 158162 2018-01-03 NEWBOOK 6 KIMBALL Victoria. The complete second season / 158595 2018-01-30 NEWMOVIE 4 SANDOWN Accidental heroes : 157156 2017-10-28 4 PLAISTOW Fall from grace : 156095 2017-09-12 4 KIMBALL The great alone / 156089 2017-09-12 3 SANDOWN I've got my eyes on you : 157150 2017-10-28 3 PLAISTOW Lilac girls : 142069 2016-04-19 3 SANDOWN Sweet vengeance / 157152 2017-10-28 NEWBOOK Can anyone provide a fix? Many thanks in advance, Cab Vinton Plaistow Public Library
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@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
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@hmcpl.org
On Mar 27, 2018, at 12:49 PM, Cab Vinton <bibliwho@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
participants (2)
-
Cab Vinton -
Martha Fuerst