Generating a barcode list of records created by a particular user in a given time frame
Hi I am interested in generating a barcode list of records created by a given user ( as a function of borrower number) in a given time frame. I will appreciate an SQL statement that will enable me to achieve this. Kind Regards, Admire Mutsikiwa (Mr) ICT Manager Libraries University of Zimbabwe Tel:+263-4-303276 Mob:+263771117777 "It is easy to dodge our responsibilities, but we cannot dodge the consequences of dodging our responsibilities." Josiah Charles Stamp <http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp>
I think that the simplest query for this would be a modification of https://wiki.koha-community.org/wiki/SQL_Reports_Library#Track_In_House_Use Replace count(*) with barcode, and modify the WHERE clause to search for 'isssue' instead of 'localuse'. SELECT count(*) FROM statistics WHERE type='localuse' AND datetime BETWEEN <<Local USE BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> You'll have to do either a left join or an inner join on items to get the barcode field. SELECT barcode FROM statistics INNER JOIN items USING (itemnumber) WHERE type='issue' AND datetime BETWEEN <<Check outs between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> On Tue, Jun 7, 2016 at 5:36 AM, Admire Mutsikiwa <amutsikiwa@uzlib.uz.ac.zw> wrote:
Hi
I am interested in generating a barcode list of records created by a given user ( as a function of borrower number) in a given time frame. I will appreciate an SQL statement that will enable me to achieve this.
Kind Regards,
Admire Mutsikiwa (Mr)
ICT Manager
Libraries
University of Zimbabwe
Tel:+263-4-303276
Mob:+263771117777
"It is easy to dodge our responsibilities, but we cannot dodge the consequences of dodging our responsibilities." Josiah Charles Stamp <http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp>
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Admire, Is your interest in knowing who has cataloged a particular item? Barton's SQL would appear to show who has checked out a particular item, not who created it. If you want to know who added an item you would need to have the Cataloguing Log turned on and query that table (action_logs). This query will get you there. select a.timestamp,a.user,i.barcode from action_logs a LEFT OUTER JOIN items i on a.object=i.itemnumber where a.module='CATALOGUING' and a.action='ADD' and a.info="item"; joy On Tue, Jun 7, 2016 at 7:53 AM, Barton Chittenden < barton@bywatersolutions.com> wrote:
I think that the simplest query for this would be a modification of https://wiki.koha-community.org/wiki/SQL_Reports_Library#Track_In_House_Use
Replace count(*) with barcode, and modify the WHERE clause to search for 'isssue' instead of 'localuse'.
SELECT count(*) FROM statistics WHERE type='localuse' AND datetime BETWEEN <<Local USE BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
You'll have to do either a left join or an inner join on items to get the barcode field.
SELECT barcode FROM statistics INNER JOIN items USING (itemnumber) WHERE type='issue' AND datetime BETWEEN <<Check outs between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
On Tue, Jun 7, 2016 at 5:36 AM, Admire Mutsikiwa < amutsikiwa@uzlib.uz.ac.zw> wrote:
Hi
I am interested in generating a barcode list of records created by a given user ( as a function of borrower number) in a given time frame. I will appreciate an SQL statement that will enable me to achieve this.
Kind Regards,
Admire Mutsikiwa (Mr)
ICT Manager
Libraries
University of Zimbabwe
Tel:+263-4-303276
Mob:+263771117777
"It is easy to dodge our responsibilities, but we cannot dodge the consequences of dodging our responsibilities." Josiah Charles Stamp <http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp>
_______________________________________________ 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
-- Joy Nelson Director of Migrations ByWater Solutions <http://bywatersolutions.com> Support and Consulting for Open Source Software Office: Fort Worth, TX Phone/Fax (888)900-8944 What is Koha? <http://bywatersolutions.com/what-is-koha/>
Hi I have written two scripts to print spine labels and due date slips based on the batchMod.pl which takes a list of barcodes to print the spine labels and the date slips. What I want is for each cataloguer to be able to print the spine labels and date slips of books that they would have catalogued during a given period. The SQL you have given me enables me to do that with a slight modification. Here is my SQL producing the desired results: SELECT i.barcode FROM action_logs a LEFT OUTER JOIN items i ON a.object = i.itemnumber WHERE a.module = 'CATALOGUING' AND user=<<Borrower number>> AND a.action = 'ADD' AND a.info = "item" and a.timestamp BETWEEN <<Between (yyyy-mmd-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND ( i.barcode IS NOT NULL or i.barcode !='') Thank you Kind Regards, Admire Mutsikiwa (Mr) ICT Manager Libraries University of Zimbabwe <tel:+263-4-303276> Tel:+263-4-303276 Mob:+263771117777 “It is easy to dodge our responsibilities, but we cannot dodge the consequences of dodging our responsibilities.” <http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp> Josiah Charles Stamp From: Joy Nelson [mailto:joy@bywatersolutions.com] Sent: Tuesday, 7 June 2016 6:51 PM To: Barton Chittenden <barton@bywatersolutions.com> Cc: Admire Mutsikiwa <amutsikiwa@uzlib.uz.ac.zw>; koha <koha@lists.katipo.co.nz> Subject: Re: [Koha] Generating a barcode list of records created by a particular user in a given time frame Admire, Is your interest in knowing who has cataloged a particular item? Barton's SQL would appear to show who has checked out a particular item, not who created it. If you want to know who added an item you would need to have the Cataloguing Log turned on and query that table (action_logs). This query will get you there. select a.timestamp,a.user,i.barcode from action_logs a LEFT OUTER JOIN items i on a.object=i.itemnumber where a.module='CATALOGUING' and a.action='ADD' and a.info <http://a.info> ="item"; joy On Tue, Jun 7, 2016 at 7:53 AM, Barton Chittenden <barton@bywatersolutions.com <mailto:barton@bywatersolutions.com> > wrote: I think that the simplest query for this would be a modification of https://wiki.koha-community.org/wiki/SQL_Reports_Library#Track_In_House_Use Replace count(*) with barcode, and modify the WHERE clause to search for 'isssue' instead of 'localuse'. SELECT count(*) FROM statistics WHERE type='localuse' AND datetime BETWEEN <<Local USE BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> You'll have to do either a left join or an inner join on items to get the barcode field. SELECT barcode FROM statistics INNER JOIN items USING (itemnumber) WHERE type='issue' AND datetime BETWEEN <<Check outs between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> On Tue, Jun 7, 2016 at 5:36 AM, Admire Mutsikiwa <amutsikiwa@uzlib.uz.ac.zw <mailto:amutsikiwa@uzlib.uz.ac.zw> > wrote:
Hi
I am interested in generating a barcode list of records created by a given user ( as a function of borrower number) in a given time frame. I will appreciate an SQL statement that will enable me to achieve this.
Kind Regards,
Admire Mutsikiwa (Mr)
ICT Manager
Libraries
University of Zimbabwe
Tel:+263-4-303276
Mob:+263771117777
"It is easy to dodge our responsibilities, but we cannot dodge the consequences of dodging our responsibilities." Josiah Charles Stamp <http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp>
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz <mailto: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 <mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha -- Joy Nelson Director of Migrations <http://bywatersolutions.com> ByWater Solutions Support and Consulting for Open Source Software Office: Fort Worth, TX Phone/Fax (888)900-8944 What is Koha? <http://bywatersolutions.com/what-is-koha/>
Hi I have written two scripts to print spine labels and due date slips based on the batchMod.pl which takes a list of barcodes to print the spine labels and the date slips. What I want is for each cataloguer to be able to print the spine labels and date slips of books that they would have catalogued during a given period. The SQL you have given me enables me to do that with a slight modification. Here is my SQL producing the desired results: SELECT i.barcode FROM action_logs a LEFT OUTER JOIN items i ON a.object = i.itemnumber WHERE a.module = 'CATALOGUING' AND user=<<Borrower number>> AND a.action = 'ADD' AND a.info = "item" and a.timestamp BETWEEN <<Between (yyyy-mmd-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND ( i.barcode IS NOT NULL or i.barcode !='') Thank you Kind Regards, Admire Mutsikiwa (Mr) ICT Manager Libraries University of Zimbabwe <tel:+263-4-303276> Tel:+263-4-303276 Mob:+263771117777 “It is easy to dodge our responsibilities, but we cannot dodge the consequences of dodging our responsibilities.” <http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp> Josiah Charles Stamp From: Joy Nelson [mailto:joy@bywatersolutions.com] Sent: Tuesday, 7 June 2016 6:51 PM To: Barton Chittenden <barton@bywatersolutions.com> Cc: Admire Mutsikiwa <amutsikiwa@uzlib.uz.ac.zw>; koha <koha@lists.katipo.co.nz> Subject: Re: [Koha] Generating a barcode list of records created by a particular user in a given time frame Admire, Is your interest in knowing who has cataloged a particular item? Barton's SQL would appear to show who has checked out a particular item, not who created it. If you want to know who added an item you would need to have the Cataloguing Log turned on and query that table (action_logs). This query will get you there. select a.timestamp,a.user,i.barcode from action_logs a LEFT OUTER JOIN items i on a.object=i.itemnumber where a.module='CATALOGUING' and a.action='ADD' and a.info <http://a.info> ="item"; joy On Tue, Jun 7, 2016 at 7:53 AM, Barton Chittenden <barton@bywatersolutions.com <mailto:barton@bywatersolutions.com> > wrote: I think that the simplest query for this would be a modification of https://wiki.koha-community.org/wiki/SQL_Reports_Library#Track_In_House_Use Replace count(*) with barcode, and modify the WHERE clause to search for 'isssue' instead of 'localuse'. SELECT count(*) FROM statistics WHERE type='localuse' AND datetime BETWEEN <<Local USE BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> You'll have to do either a left join or an inner join on items to get the barcode field. SELECT barcode FROM statistics INNER JOIN items USING (itemnumber) WHERE type='issue' AND datetime BETWEEN <<Check outs between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> On Tue, Jun 7, 2016 at 5:36 AM, Admire Mutsikiwa <amutsikiwa@uzlib.uz.ac.zw <mailto:amutsikiwa@uzlib.uz.ac.zw> > wrote:
Hi
I am interested in generating a barcode list of records created by a given user ( as a function of borrower number) in a given time frame. I will appreciate an SQL statement that will enable me to achieve this.
Kind Regards,
Admire Mutsikiwa (Mr)
ICT Manager
Libraries
University of Zimbabwe
Tel:+263-4-303276
Mob:+263771117777
"It is easy to dodge our responsibilities, but we cannot dodge the consequences of dodging our responsibilities." Josiah Charles Stamp <http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp>
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz <mailto: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 <mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha -- Joy Nelson Director of Migrations <http://bywatersolutions.com> ByWater Solutions Support and Consulting for Open Source Software Office: Fort Worth, TX Phone/Fax (888)900-8944 What is Koha? <http://bywatersolutions.com/what-is-koha/>
participants (3)
-
Admire Mutsikiwa -
Barton Chittenden -
Joy Nelson