[Koha] Report for items held by 2 out of 3 branches

[Barton Chittenden]

Good point, I removed that logic.

SELECT
    b.biblionumber,
    SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn,
    b.title,
    i.homebranch,
    i.itemcallnumber
FROM
    items i
    LEFT JOIN biblioitems m USING (biblioitemnumber)
    LEFT JOIN biblio b ON i.biblionumber = b.biblionumber
WHERE
    i.itype = 'NEWBOOK'
    AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned
GROUP BY
    b.biblionumber
HAVING
    isbn != ""
    AND count(items.homebranch) > 1
ORDER BY rand()
LIMIT 7


On Fri, Feb 22, 2019 at 11:23 AM Cab Vinton <bibliwho at gmail.com> wrote:

> Unless I'm missing the thread somehow, I don't think that will work as
> the prior conditions for all 3 branches are still present in the WHERE
> statement.
>
> If we eliminate those, then we still need to include the acquisition
> date condition somehow.
>
> Thanks,
>
> Cab
>
>
> On Fri, Feb 22, 2019 at 11:11 AM Barton Chittenden
> <barton at bywatersolutions.com> wrote:
> >
> > you could add
> >
> > count(distinct homebranch) > 1
> >
> > to your HAVING statement
> >
> > On Fri, Feb 22, 2019 at 9:03 AM Cab Vinton <bibliwho at gmail.com> wrote:
> >>
> >> Hi, All --
> >>
> >> We have a report (see below) that generates a random list of items
> >> held by all 3 of our branches & purchased within the past 30 days.
> >>
> >> To generate more titles we'd like to loosen the criteria by specifying
> >> that the titles should be held by just 2 of the 3 branches.
> >>
> >> I know how I'd approach this outside of Koha's SQL-based reporting
> >> tool, but not sure how to write this without repeating a whole bunch
> >> of code ((A or B) or (A or C) or (B or C)). The code is used to
> >> populate a carousel on our OPAC's home page & it's already a bit
> >> "pokey", so efficient code is an important factor.
> >>
> >> Anyone have suggestions on how best to write this report?
> >>
> >> Many thanks in advance,
> >>
> >> Cab Vinton, Director
> >> Plaistow Public Library
> >> Plaistow, NH
> >> https://catalog.southernnh.bywatersolutions.com
> >>
> >>
> >> SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn,
> >> b.title, i.homebranch, i.itemcallnumber
> >>   FROM items i
> >>   LEFT JOIN biblioitems m USING (biblioitemnumber)
> >>   LEFT JOIN biblio b ON i.biblionumber = b.biblionumber
> >>   WHERE i.itype = 'NEWBOOK'
> >>   AND b.biblionumber IN (SELECT biblionumber FROM items WHERE
> >> DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND
> >> homebranch='KIMBALL')
> >>   AND b.biblionumber IN (SELECT biblionumber FROM items WHERE
> >> DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND
> >> homebranch='PLAISTOW')
> >>   AND b.biblionumber IN (SELECT biblionumber FROM items WHERE
> >> DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND
> >> homebranch='SANDOWN')
> >>   GROUP BY b.biblionumber
> >>   HAVING isbn != ""
> >>   ORDER BY rand()
> >>   LIMIT 7
> >> _______________________________________________
> >> Koha mailing list  http://koha-community.org
> >> Koha at lists.katipo.co.nz
> >> https://lists.katipo.co.nz/mailman/listinfo/koha
>