[Koha] Items held by more than one branch

[Arshad Iqbal]

 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>>, at 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 at 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 at 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 at lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at 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