[Koha] Report help needed - lost items
Cab Vinton
bibliwho at gmail.com
Sat Oct 5 07:47:56 NZDT 2019
Very late to the party, but maybe this could provide a clue.
The following throws an error, but I believe the basic idea of finding
records where the lowest itemlost value isn't zero is on the right
track:
WHERE itype = <<Item type|itemtypes>>
GROUP BY biblionumber
HAVING MIN(itemlost) > 0
I suspect SQL is sensitive to the order of these statements, but there
are probably other issues as well.
Hopefully someone else w/ better report-writing chops than me can take
it from there. Or if you found something that works, Elaine, please
share w/ the list :-)
All best,
Cab Vinton, Director
Plaistow Public Library
Plaistow, NH
On Sat, Aug 24, 2019 at 8:30 PM Elaine Bradtke <eb at efdss.org> wrote:
>
> Mark, that still didn't do the trick, but thanks!
> Caroline, this is much closer to what I need. But still not quite right.
> It's messy because I've altered a report that I found in the report library
> rather inelegantly.
>
> What I really need is a list of titles and biblio numbers of biblios where
> the last item is lost, or missing, or withdrawn (I can change that part of
> the query myself). I have to limit it by format because we have articles
> and components in the catalogue that don't have item records attached.
> What I'm getting now lis a list of biblios with items that are missing but
> it includes biblios that still have one or more items that are on the shelf
> (but at least one item that is missing). Or in other words, I'm looking for
> biblios where the all the items are missing/lost/withdrawn.
>
> Many thanks for your help!
>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel +44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --------------------------------------------------------------------------
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Fri, Aug 23, 2019 at 12:30 PM Caroline Cyr-La-Rose <
> caroline.cyr-la-rose at inlibro.com> wrote:
>
> > Hello Elaine,
> >
> > from what I understand, you want bibliographic information on lost items?
> >
> > I'd need to know exactly the purpose of the report to help you more as
> > there are things in there I don't understand (like why select barcode,
> > when afterwards you group by biblionumber, or why the title is there
> > twice).
> >
> > First, instead of selecting from the biblio table, I would go the other
> > way and select from the items table. That way, you are sure of getting
> > only things that have an item.
> >
> > If you want to limit by itemtype, you can add WHERE itype = <<Item
> > type|itemtypes>> in your query to have the ability to choose your
> > itemtype upon executing your report.
> >
> > I cleaned your query up a little bit, but like I mentioned before, as I
> > don't know the purpose, it might not be what you need.
> >
> > SELECT
> > CONCAT(
> > '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
> > biblionumber,
> > '\">',
> > title,
> > '</a>'
> > ) AS Title,
> > biblionumber,
> > barcode,
> > ccode,
> > onloan AS 'Checkout date'
> > FROM
> > items
> > JOIN biblio USING (biblionumber)
> > WHERE itemlost != 0
> > AND itype = <<Item type|itemtypes>>
> >
> > Let us know if this works.
> > Caroline
> >
> >
> > On 19-08-23 15 h 00, Mark Alexander wrote:
> > > Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:
> > >> I've got the following adapted from one in the reports library. But I
> > need
> > >> to limit it by item type, or not return everything that doesn't have an
> > >> item record attached. We've got lots of components in our catalogue that
> > >> don't have items and it's returning all of them:
> > >>
> > >> SELECT
> > >> CONCAT(
> > >> '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
> > ',
> > >> bib.biblionumber,
> > >> '\">',
> > >> bib.title,
> > >> '</a>'
> > >> ) AS Title,
> > >> bib.title,
> > >> bib.biblionumber,
> > >> lostitems.barcode,
> > >> lostitems.ccode,
> > >> lostitems.onloan AS 'Checkout date'
> > >> FROM
> > >> biblio bib
> > >> LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> > >> items.itemlost = 0)
> > >> LEFT JOIN items AS lostitems ON (bib.biblionumber =
> > >> lostitems.biblionumber AND lostitems.itemlost != 0)
> > >> GROUP BY bib.biblionumber
> > >> HAVING count(items.itemnumber) = 0
> > > While I'm not an SQL expert by any means, I think I was able to get
> > > this to work on my catalog by removing the HAVING clause and
> > > putting "WHERE items.itemnumber IS NULL" before the GROUP clause:
> > >
> > > SELECT
> > > CONCAT(
> > > '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
> > ',
> > > bib.biblionumber,
> > > '\">',
> > > bib.title,
> > > '</a>'
> > > ) AS Title,
> > > bib.title,
> > > bib.biblionumber,
> > > lostitems.barcode,
> > > lostitems.ccode,
> > > lostitems.onloan AS 'Checkout date'
> > > FROM
> > > biblio bib
> > > LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> > > items.itemlost = 0)
> > > LEFT JOIN items AS lostitems ON (bib.biblionumber =
> > > lostitems.biblionumber AND lostitems.itemlost != 0)
> > > WHERE items.itemnumber IS NULL
> > > GROUP BY bib.biblionumber
> > > _______________________________________________
> > > Koha mailing list http://koha-community.org
> > > Koha at lists.katipo.co.nz
> > > https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> > --
> > Caroline Cyr La Rose, M.S.I.
> > Bibliothécaire | Responsable de produit
> >
> > Tél. : 1-833-465-4276, poste 221
> > Caroline.Cyr-La-Rose at inLibro.com <mailto:caroline.cyr-la-rose at inLibro.com>
> >
> > INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com
> > <http://www.inLibro.com>
> > _______________________________________________
> > Koha mailing list http://koha-community.org
> > Koha at lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> _______________________________________________
> Koha mailing list http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
More information about the Koha
mailing list