Hi, I want to convert this checked in sql report into renew sql report. How i can convert this report. guide me. SELECT b.title, b.author, i.itemcallnumber, i.barcode, c.returndate FROM (SELECT iss.returndate, iss.itemnumber FROM issues iss, (SELECT @StartDate:=<<Between (yyyy-mm-dd)|date>>,@EndDate:=<<and (yyyy-mm-dd)|date>>) AS var WHERE date(iss.returndate) BETWEEN @StartDate AND @EndDate UNION ALL SELECT oi.returndate, oi.itemnumber FROM old_issues oi WHERE date(oi.returndate) BETWEEN @StartDate AND @EndDate) AS c LEFT JOIN items i USING (itemnumber) LEFT JOIN biblio b USING (biblionumber) WHERE i.homebranch=<<Item belongs TO|branches>> On Mon, Apr 23, 2018 at 7:14 PM, Jonathan Druart < jonathan.druart@bugs.koha-community.org> wrote:
I'd say you are missing a group by.
On Mon, 23 Apr 2018 at 10:19 Cab Vinton <bibliwho@gmail.com> wrote:
Hi, all--
Another report question...
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Including the statement HAVING COUNT(i.itemnumber) > 1 collapses the results to just one title. (Strangely, the bib returned only has a single item attached.)
I'm missing some basic feature of how SQL operates :-(
SELECT b.biblionumber,i.homebranch,t.isbn,b.title,b.author,i.ccode, i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ORDER BY b.title
Grateful in advance for any assistance!
All best,
Cab Vinton Plaistow Public Library Plaistow, NH _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- *Muhammad Arshad Iqbal* Assistant Librarian at Publishing and Student Affairs Directorate, National University of Science and Technology (NUST) Islamabad. Phone number:051-90851371 Cell no.+923444900809