<span style="font-family: arial;">Nabonita</span>,<br />
<br />
Simply change the <br />
<br />
<span style="font-size: 13px; font-family: verdana,sans-serif;">WHERE issues.issuedate = 'yyyy-mm-dd'</span><br />
<br />
part to read<br />
<br />
<span style="font-size: 13px; font-family: verdana,sans-serif;">WHERE issues.issuedate <= 'yyyy-mm-dd'</span><br />
<br />
in each query.<br />
<p>Randy Rowe<br />
Lincoln City Libraries I.T.<br />
<br />
<br />
</p>
<br />
<br />
-----Original Message-----<br />
From: "nabonita@jncasr.ac.in" <nabonita@jncasr.ac.in><br />
Sent 4/20/2011 11:35:49 PM<br />
To: "Church, Beverly" <bchurch@liblime.com><br />
Cc: "koha@lists.katipo.co.nz" <koha@lists.katipo.co.nz><br />
Subject: Re: [Koha] SQL statement for generating current checkouts<br />
<br />
<span style="font-family: 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.<br />
<br />
Regards<br />
<br />
<div id="EmailSignature">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" class="normal-link">nabonita@jncasr.ac.in</a><br />
Web: <a class="normal-link" target="_blank" href="http://lib.jncasr.ac.in">http://lib.jncasr.ac.in</a></div>
<br />
<br />
<br />
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 #22437f; padding: 0px 0px 0px 5px; margin: 0px 0px 0px 5px;"><span style="font-size: 13px; font-family: 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" />
</span><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 target="_blank" href="mailto:bchurch@liblime.com">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">nabonita@jncasr.ac.in</a>></span><br />
<blockquote style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid #cccccc; padding-left: 1ex;" class="gmail_quote">
<span style="font-family: 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 target="_blank" href="mailto:nabonita@jncasr.ac.in">nabonita@jncasr.ac.in</a><br />
Web: <a target="_blank" href="http://lib.jncasr.ac.in/">http://lib.jncasr.ac.in</a></div>
</span>
<br />
_______________________________________________<br />
Koha mailing list <a target="_blank" href="http://koha-community.org">http://koha-community.org</a><br />
<a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br />
<a target="_blank" href="http://lists.katipo.co.nz/mailman/listinfo/koha">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br />
<br />
</blockquote></div>
<br />
</blockquote></span>
<pre>_______________________________________________
Koha mailing list <a target="_blank" href="http://koha-community.org">http://koha-community.org</a>
Koha@lists.katipo.co.nz
<a target="_blank" href="http://lists.katipo.co.nz/mailman/listinfo/koha">http://lists.katipo.co.nz/mailman/listinfo/koha</a>
</pre>