Re: [Koha] Report help needed - lost items

[Cab Vinton]

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 = <>
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  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-r...@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 = < > 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(
> >  ' >  biblionumber,
> >  '\">',
> >  title,
> >  ''
> >  ) AS Title,
> >  biblionumber,
> >  barcode,
> >  ccode,
> >  onloan AS 'Checkout date'
> > FROM
> >  items
> >  JOIN biblio USING (biblionumber)
> > WHERE itemlost != 0
> >AND itype = <>
> >
> > 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(
> > >>  ' > ',
> > >>  bib.biblionumber,
> > >>  '\">',
> > >>  bib.title,
> > >>  ''
> > >>  ) 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(
> > >  ' > ',
> > >  bib.biblionumber,
> > >  '\">',
> > >  bib.title,
> > >   

Re: [Koha] Report help needed - lost items

[Elaine Bradtke]

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-r...@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 = < 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(
>  '  biblionumber,
>  '\">',
>  title,
>  ''
>  ) AS Title,
>  biblionumber,
>  barcode,
>  ccode,
>  onloan AS 'Checkout date'
> FROM
>  items
>  JOIN biblio USING (biblionumber)
> WHERE itemlost != 0
>AND itype = <>
>
> 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(
> >>  ' ',
> >>  bib.biblionumber,
> >>  '\">',
> >>  bib.title,
> >>  ''
> >>  ) 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(
> >  ' ',
> >  bib.biblionumber,
> >  '\">',
> >  bib.title,
> >  ''
> >  ) 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@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-r...@inlibro.com 

Re: [Koha] Report help needed - lost items

[Caroline Cyr-La-Rose]

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 = > 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(
'',
title,
''
) AS Title,
biblionumber,
barcode,
ccode,
onloan AS 'Checkout date'
FROM
items
JOIN biblio USING (biblionumber)
WHERE itemlost != 0
  AND itype = <>

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(
 '',
 bib.title,
 ''
 ) 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(
 '',
 bib.title,
 ''
 ) 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@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-r...@inlibro.com 

INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com 


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - lost items

[Mark Alexander]

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(
> ' bib.biblionumber,
> '\">',
> bib.title,
> ''
> ) 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(
'',
bib.title,
''
) 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@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed - lost items

[Elaine Bradtke]

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(
'',
bib.title,
''
) 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

Thanks!
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
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha