[Koha] Interesting report request

[Katelyn Browne]

I would start by pulling the set of patrons who checked in today or
yesterday, then pull the books from their account that fit the due date
range. Here's a start; it functions, but I haven't checked to see if it's
pulling all the right information. It should return a list of outstanding
call numbers + titles for each borrower who returned in the date range.

I left the dates for manual entry so that the report would be flexible, but
today/yesterday are also easy to call directly.

SELECT borrowernumber, GROUP_CONCAT(items.itemcallnumber, " ( ",
biblio.title, ") ") AS booklist
FROM (SELECT borrowernumber
FROM statistics
WHERE date(statistics.datetime) BETWEEN <<Returned between (YYYY-MM-DD)>>
AND <<and (YYYY-MM-DD)>>
      AND statistics.type='return'
GROUP BY borrowernumber) whoreturned
LEFT JOIN issues USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
LEFT JOIN biblio USING (biblionumber)
WHERE date(issues.date_due) BETWEEN <<Due between (YYYY-MM-DD)>> AND <<and
(YYYY-MM-DD)>>
GROUP BY borrowernumber

--Katelyn.

Katelyn Browne
Middle/High School Librarian
Capital City Public Charter School
100 Peabody Street NW
Washington, DC 20011
(202) 387-0309 x1745
kbrowne at ccpcs.org
http://www.ccpcs.org/library/


On Mon, Mar 31, 2014 at 11:25 AM, Nicole Engard <nengard at gmail.com> wrote:

> I think this can be done - but I'm not sure how to tackle it.  I got
> this request for a report to find items that probably got missed at
> checkin so the staff can go look to the shelves to find these items.
>
> The question was - could we take the set of patrons who had items due
> during a three day period (today, tomorrow and yesterday?)
>
> And limit that to patrons who we have actual recorded checkins for
> during that time period and then produce a list of other items that
> these patrons had checked out that were due during the same three day
> period and generate this list of items --- in order to do a quick
> check to see if we actually received them but missed checking them in?
>
>
> Ideas?
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>