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?
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@ccpcs.org http://www.ccpcs.org/library/ On Mon, Mar 31, 2014 at 11:25 AM, Nicole Engard <nengard@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
On Mon, Mar 31, 2014 at 11:33 AM, Katelyn Browne <kbrowne@ccpcs.org> wrote:
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
Thanks!! I'll give it a whirl.
participants (2)
-
Katelyn Browne -
Nicole Engard