Report for items held by 2 out of 3 branches
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
you could add count(distinct homebranch) > 1 to your HAVING statement On Fri, Feb 22, 2019 at 9:03 AM Cab Vinton <bibliwho@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
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@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
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@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@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Hi, All -- Think the version below should do what we're looking for. (Thank you, Barton!) All best, Cab Vinton 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 exists( select * from items where items.itemnumber = i.itemnumber AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned ) GROUP BY b.biblionumber HAVING isbn != "" AND count(i.homebranch) > 1 ORDER BY rand() LIMIT 7
participants (2)
-
Barton Chittenden -
Cab Vinton