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

Cab Vinton bibliwho at gmail.com
Sat Feb 23 03:02:07 NZDT 2019


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


More information about the Koha mailing list