[Koha] SQL statement for generating current checkouts

[Church, Beverly]

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: bchurch at liblime.com
skype: beverlychurch


On Thu, Apr 21, 2011 at 12:35 AM, nabonita at jncasr.ac.in <
nabonita at 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: nabonita at 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 = 'yyyy-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 =
> 'yyyy-mm-dd'
>
>
>
> * * * *
> Beverly Church
> LibLime Project Manager
>
> phone: 1-888-564-2457 ext. 717
>             1-301-654-8088 ext. 292
> email: bchurch at liblime.com
> skype: beverlychurch
>
>
> 2011/4/20 nabonita Guha <nabonita at jncasr.ac.in>
>
>> 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: nabonita at jncasr.ac.in
>> Web: http://lib.jncasr.ac.in
>>
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20110421/f9d3eb9e/attachment-0001.htm