<font size="2"><font face="verdana,sans-serif">Hi Nabonita,<br><br>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. </font></font><br>
<font size="2"><font face="verdana,sans-serif"><br>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 <= '</font></font><font size="2"><font face="verdana,sans-serif">2011-03-29</font></font><font size="2"><font face="verdana,sans-serif">' or issues.lastreneweddate <= '</font></font><font size="2"><font face="verdana,sans-serif">2011-03-29</font></font><font size="2"><font face="verdana,sans-serif">'<br>
<br></font></font><font size="2"><font face="verdana,sans-serif"></font></font><font size="2"><font face="verdana,sans-serif"><br></font></font><font size="2"><font face="verdana,sans-serif">Bev<br><br clear="all"></font></font><br>
* * * * <br>Beverly Church <br>LibLime Project Manager<br><br>phone: 1-888-564-2457 ext. 717 <br> 1-301-654-8088 ext. 292<br>email: <a href="mailto:bchurch@liblime.com" target="_blank">bchurch@liblime.com</a><br>
skype: beverlychurch<br>
<br><br><div class="gmail_quote">On Thu, Apr 21, 2011 at 12:35 AM, <a href="mailto:nabonita@jncasr.ac.in">nabonita@jncasr.ac.in</a> <span dir="ltr"><<a href="mailto:nabonita@jncasr.ac.in">nabonita@jncasr.ac.in</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><font face="arial">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.<div class="im">
<br><br>Regards<br><br><div>Nabonita<br>
<br>
--<br>
Nabonita Guha<br>
Library-cum-Information Officer<br>
Library, Jawaharlal Nehru Centre <br>
for Advanced Scientific Research,<br>
Jakkur Post, Bangalore 560064<br>
Karnataka, India<br>
Phone: +91-80-22082930<br>
Email: <a href="mailto:nabonita@jncasr.ac.in" target="_blank">nabonita@jncasr.ac.in</a><br>
Web: <a href="http://lib.jncasr.ac.in" target="_blank">http://lib.jncasr.ac.in</a></div><br><br><br></div><div><div></div><div class="h5">At Wednesday, 20-04-2011 on 5:46 pm Church, Beverly wrote:<br><blockquote style="border-width: 0pt 0pt 0pt 2px; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color rgb(34, 67, 127); padding: 0px 0px 0px 5px; margin: 0px 0px 0px 5px;">
<font size="2"><font face="verdana,sans-serif">Nabonita,<br><br>You can use this query if you want to see the title:<br><br>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 = 'yyyy-mm-dd'<br>
<br>If you don't need the title, use this query:<br><br>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 = 'yyyy-mm-dd'<br clear="all">
</font></font><br><br><br>* * * * <br>Beverly Church <br>LibLime Project Manager<br><br>phone: 1-888-564-2457 ext. 717 <br> 1-301-654-8088 ext. 292<br>email: <a href="mailto:bchurch@liblime.com" target="_blank">bchurch@liblime.com</a><br>
skype: beverlychurch<br>
<br><br><div class="gmail_quote">2011/4/20 nabonita Guha <span dir="ltr"><<a href="mailto:nabonita@jncasr.ac.in" target="_blank">nabonita@jncasr.ac.in</a>></span><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<font face="arial">Dear Koha users/Developers,<br><br>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.<br><br>Looking forward to hearing from you.<br><br>With best regards<br><br><div>Nabonita<br>
<br>
--<br>
Nabonita Guha<br>
Library-cum-Information Officer<br>
Library, Jawaharlal Nehru Centre <br>
for Advanced Scientific Research,<br>
Jakkur Post, Bangalore 560064<br>
Karnataka, India<br>
Phone: +91-80-22082930<br>
Email: <a href="mailto:nabonita@jncasr.ac.in" target="_blank">nabonita@jncasr.ac.in</a><br>
Web: <a href="http://lib.jncasr.ac.in/" target="_blank">http://lib.jncasr.ac.in</a></div></font>
<br>_______________________________________________<br>
Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
<a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
<br></blockquote></div><br></blockquote></div></div></font>
</blockquote></div><br>