Re: [Koha] Report help
Is this run at the MySQL level or in the reports module?
* * * *
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 Wed, May 25, 2011 at 4:23 PM, Nicole Engard neng...@gmail.com wrote:
I did try it and it does work. You need a more recent version of MySQL
for it to work ... 5. something ... It's in Debian Squueze.
Nicole
On Wed, May 25, 2011 at 3:21 PM, Church, Beverly bchu...@liblime.com
wrote:
Have you tried it? I can't get it to work.
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 Wed, May 25, 2011 at 3:08 PM, Nicole Engard neng...@gmail.com
wrote:
For those who are curious, cait found my answer and it's on the wiki:
http://wiki.koha-community.org/wiki/SQL_Reports_Library#Title.2FSubtitle_List
Nicole
On Wed, May 25, 2011 at 1:49 PM, Nicole Engard neng...@gmail.com
wrote:
Bev,
I was thinking more along the lines of this type of thing:
http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC
which I can do with the version of MySQL that I have installed. Just
wants sure how to fit it in to the report.
Nicole
On Wed, May 25, 2011 at 11:59 AM, Church, Beverly
bchu...@liblime.com
wrote:
Hi Nicole,
This query should work:
select title, IF( LOCATE('datafield tag=245', biblioitems.marcxml)
=
0 OR
LOCATE('subfield code=b', biblioitems.marcxml, LOCATE('datafield
tag=245', biblioitems.marcxml)) = 0 OR LOCATE('subfield
code=b',
biblioitems.marcxml, LOCATE('datafield tag=245',
biblioitems.marcxml))
LOCATE('/datafield', biblioitems.marcxml, LOCATE('datafield
tag=245',
biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml,
LOCATE('subfield
code=b', biblioitems.marcxml, LOCATE('datafield tag=245',
biblioitems.marcxml)) + 19, LOCATE('/subfield',
biblioitems.marcxml,
LOCATE('subfield code=b', biblioitems.marcxml, LOCATE('datafield
tag=245', biblioitems.marcxml)) + 19) - (LOCATE('subfield
code=b',
biblioitems.marcxml, LOCATE('datafield tag=245',
biblioitems.marcxml)) +
19))) AS 'Sub-title' FROM biblioitems, biblio where
biblioitems.biblionumber = biblio.biblionumber and LOCATE('datafield
tag=245', biblioitems.marcxml) != 0 ORDER BY title asc
* * * *
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 Wed, May 25, 2011 at 12:10 PM, Nicole Engard neng...@gmail.com
wrote:
I'm looking for some help with using the marc xml reports trick :)
I need a report that shows the title and the subtitle, how would I
do
that?
Thanks in advance!
Nicole
___
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 nabon...@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: 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
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 nabon...@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: 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
