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

[Barton Chittenden]

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
>