[Koha] SQL statement for generating current checkouts
Dear Koha users/Developers, Can you please help me in building SQL statement to generate current checkouts on a given date (not for a date range). For example, I need to generate a report to get list of (barcodes with borrowers card number, surname and firstname and borrower's category with date of issue) were on Loan as on 2011-03-29. Looking forward to hearing from you. With best regards Nabonita -- Nabonita Guha Library-cum-Information Officer Library, Jawaharlal Nehru Centre for Advanced Scientific Research, Jakkur Post, Bangalore 560064 Karnataka, India Phone: +91-80-22082930 Email: nabon...@jncasr.ac.in Web: http://lib.jncasr.ac.in ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL statement for generating current checkouts
Nabonita, You can use this query if you want to see the title: SELECT issues.issuedate, items.barcode, biblio.title, borrowers.firstname,borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE issues.issuedate = '-mm-dd' If you don't need the title, use this query: SELECT issues.issuedate, items.barcode, borrowers.firstname, borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber WHERE issues.issuedate = '-mm-dd' * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchu...@liblime.com skype: beverlychurch 2011/4/20 nabonita Guha > Dear Koha users/Developers, > > Can you please help me in building SQL statement to generate current > checkouts on a given date (not for a date range). For example, I need to > generate a report to get list of (barcodes with borrowers card number, > surname and firstname and borrower's category with date of issue) were on > Loan as on 2011-03-29. > > Looking forward to hearing from you. > > With best regards > > Nabonita > > -- > Nabonita Guha > Library-cum-Information Officer > Library, Jawaharlal Nehru Centre > for Advanced Scientific Research, > Jakkur Post, Bangalore 560064 > Karnataka, India > Phone: +91-80-22082930 > Email: nabon...@jncasr.ac.in > Web: http://lib.jncasr.ac.in > > ___ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > http://lists.katipo.co.nz/mailman/listinfo/koha > > ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL statement for generating current checkouts
Hi Nabonita, Try this query. It looks for any items that are current checked out where the checkout date was on or before 2011-03-29, or the last renewal date was or before 2011-03-29. SELECT issues.issuedate, items.barcode, biblio.title, borrowers.firstname,borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE issues.issuedate <= '2011-03-29' or issues.lastreneweddate <= '2011-03-29' Bev * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchu...@liblime.com skype: beverlychurch On Thu, Apr 21, 2011 at 12:35 AM, nabon...@jncasr.ac.in < nabon...@jncasr.ac.in> wrote: > Thanks Beverly for send me the SQL query. But this is retrieving only those > titles which are issued on the given date. Where as I need to know that the > titles which were on loan AS OF 2011-03-29. This may contain overdue books, > and books which were issued/renewed on or before the given date. I'm not > sure if this sort of result can be generated in Koha. > > > Regards > > Nabonita > > -- > Nabonita Guha > Library-cum-Information Officer > Library, Jawaharlal Nehru Centre > for Advanced Scientific Research, > Jakkur Post, Bangalore 560064 > Karnataka, India > Phone: +91-80-22082930 > Email: nabon...@jncasr.ac.in > Web: http://lib.jncasr.ac.in > > > > At Wednesday, 20-04-2011 on 5:46 pm Church, Beverly wrote: > > Nabonita, > > You can use this query if you want to see the title: > > SELECT issues.issuedate, items.barcode, biblio.title, > borrowers.firstname,borrowers.surname, borrowers.cardnumber, > borrowers.categorycode FROM issues LEFT JOIN borrowers ON > borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON > issues.itemnumber = items.itemnumber LEFT JOIN biblio ON > items.biblionumber=biblio.biblionumber WHERE issues.issuedate = '-mm-dd' > > If you don't need the title, use this query: > > SELECT issues.issuedate, items.barcode, borrowers.firstname, > borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues > LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT > JOIN items ON issues.itemnumber = items.itemnumber WHERE issues.issuedate = > '-mm-dd' > > > > * * * * > Beverly Church > LibLime Project Manager > > phone: 1-888-564-2457 ext. 717 > 1-301-654-8088 ext. 292 > email: bchu...@liblime.com > skype: beverlychurch > > > 2011/4/20 nabonita Guha > >> Dear Koha users/Developers, >> >> Can you please help me in building SQL statement to generate current >> checkouts on a given date (not for a date range). For example, I need to >> generate a report to get list of (barcodes with borrowers card number, >> surname and firstname and borrower's category with date of issue) were on >> Loan as on 2011-03-29. >> >> Looking forward to hearing from you. >> >> With best regards >> >> Nabonita >> >> -- >> Nabonita Guha >> Library-cum-Information Officer >> Library, Jawaharlal Nehru Centre >> for Advanced Scientific Research, >> Jakkur Post, Bangalore 560064 >> Karnataka, India >> Phone: +91-80-22082930 >> Email: nabon...@jncasr.ac.in >> Web: http://lib.jncasr.ac.in >> >> ___ >> Koha mailing list http://koha-community.org >> Koha@lists.katipo.co.nz >> http://lists.katipo.co.nz/mailman/listinfo/koha >> >> > ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL statement for generating current checkouts
Thanks Beverly for send me the SQL query. But this is retrieving only those titles which are issued on the given date. Where as I need to know that the titles which were on loan AS OF 2011-03-29. This may contain overdue books, and books which were issued/renewed on or before the given date. I'm not sure if this sort of result can be generated in Koha. Regards Nabonita -- Nabonita Guha Library-cum-Information Officer Library, Jawaharlal Nehru Centre for Advanced Scientific Research, Jakkur Post, Bangalore 560064 Karnataka, India Phone: +91-80-22082930 Email: nabon...@jncasr.ac.in Web: http://lib.jncasr.ac.in At Wednesday, 20-04-2011 on 5:46 pm Church, Beverly wrote: Nabonita, You can use this query if you want to see the title: SELECT issues.issuedate, items.barcode, biblio.title, borrowers.firstname,borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE issues.issuedate = '-mm-dd' If you don't need the title, use this query: SELECT issues.issuedate, items.barcode, borrowers.firstname, borrowers..surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber WHERE issues.issuedate = '-mm-dd' * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchu...@liblime.com skype: beverlychurch 2011/4/20 nabonita Guha Dear Koha users/Developers, Can you please help me in building SQL statement to generate current checkouts on a given date (not for a date range). For example, I need to generate a report to get list of (barcodes with borrowers card number, surname and firstname and borrower's category with date of issue) were on Loan as on 2011-03-29. Looking forward to hearing from you. With best regards Nabonita -- Nabonita Guha Library-cum-Information Officer Library, Jawaharlal Nehru Centre for Advanced Scientific Research, Jakkur Post, Bangalore 560064 Karnataka, India Phone: +91-80-22082930 Email: nabon...@jncasr.ac.in Web: http://lib.jncasr.ac.in [1] ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha Links: -- [1] http://lib.jncasr.ac.in/ ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL statement for generating current checkouts
Nabonita, Simply change the WHERE issues.issuedate = '-mm-dd' part to read WHERE issues.issuedate <= '-mm-dd' in each query. Randy Rowe Lincoln City Libraries I.T. -Original Message- From: "nabon...@jncasr.ac.in" Sent 4/20/2011 11:35:49 PM To: "Church, Beverly" Cc: "koha@lists.katipo.co.nz" Subject: Re: [Koha] SQL statement for generating current checkouts Thanks Beverly for send me the SQL query. But this is retrieving only those titles which are issued on the given date. Where as I need to know that the titles which were on loan AS OF 2011-03-29. This may contain overdue books, and books which were issued/renewed on or before the given date. I'm not sure if this sort of result can be generated in Koha. Regards Nabonita -- Nabonita Guha Library-cum-Information Officer Library, Jawaharlal Nehru Centre for Advanced Scientific Research, Jakkur Post, Bangalore 560064 Karnataka, India Phone: +91-80-22082930 Email: nabon...@jncasr.ac.in Web: http://lib.jncasr.ac.in At Wednesday, 20-04-2011 on 5:46 pm Church, Beverly wrote: Nabonita, You can use this query if you want to see the title: SELECT issues.issuedate, items.barcode, biblio.title, borrowers.firstname,borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE issues.issuedate = '-mm-dd' If you don't need the title, use this query: SELECT issues.issuedate, items.barcode, borrowers.firstname, borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber WHERE issues.issuedate = '-mm-dd' * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchu...@liblime.com skype: beverlychurch 2011/4/20 nabonita Guha Dear Koha users/Developers, Can you please help me in building SQL statement to generate current checkouts on a given date (not for a date range). For example, I need to generate a report to get list of (barcodes with borrowers card number, surname and firstname and borrower's category with date of issue) were on Loan as on 2011-03-29. Looking forward to hearing from you. With best regards Nabonita -- Nabonita Guha Library-cum-Information Officer Library, Jawaharlal Nehru Centre for Advanced Scientific Research, Jakkur Post, Bangalore 560064 Karnataka, India Phone: +91-80-22082930 Email: nabon...@jncasr.ac.in Web: http://lib.jncasr.ac.in ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL statement for generating current checkouts
Hi Beverly, Thanks for the SQL statement. This is the output what I was looking for. With regards Nabonita -- Nabonita Guha Library-cum-Information Officer Library, Jawaharlal Nehru Centre for Advanced Scientific Research, Jakkur Post, Bangalore 560064 Karnataka, India Phone: +91-80-22082930 Email: nabon...@jncasr.ac.in Web: http://lib.jncasr.ac.in At Thursday, 21-04-2011 on 5:52 pm Church, Beverly wrote: Hi Nabonita, Try this query. It looks for any items that are current checked out where the checkout date was on or before 2011-03-29, or the last renewal date was or before 2011-03-29.. SELECT issues.issuedate, items.barcode, biblio.title, borrowers.firstname,borrowers.surname, borrowers.cardnumber, borrowers.categorycode FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber = issues.borrowernumber LEFT JOIN items ON issues.itemnumber = items.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE issues.issuedate Links: -- [1] http://lib.jncasr.ac.in/ ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
