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

[Cab Vinton]

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