[Koha] SQL runs outside of staff client but not inside it

[Doug Dearden]

Thanks Robert, that did it.  A much more elegant solution than mine also.  I checked the wiki before I started this but didn't find that report.

Best - Doug

-----Original Message-----
From: koha-bounces at lists.katipo.co.nz [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Robert Williams
Sent: Wednesday, March 20, 2013 10:19 AM
To: koha at lists.katipo.co.nz
Subject: Re: [Koha] SQL runs outside of staff client but not inside it

Doug:

You wrote:
>I am working on some SQL to get a report for last month's acquisitions, and worked up the following code to do it:

[...snip...]

A couple of the SQL Reports posted on the wiki have a WHERE clause that purportedly limits to the previous month. Would something like the following work?

SELECT
EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') as "Title:",
EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') as subtitle,
EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]') as "statement of responsibility",
EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="b"]') as Publisher,
EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="c"]') as Date,
EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="a"]') as "Call Number first part",
EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="b"]') as "Call Number second part", items.dateaccessioned as "Date Accessioned"
FROM biblioitems LEFT JOIN items on (biblioitems.biblioitemnumber=items.biblioitemnumber)
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') 
AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) 
ORDER BY items.dateaccessioned asc;


--Robert

*********************************************
Robert L. Williams
Open Source Services Manager
Amigos Library Services, Inc.
14400 Midway Road
Dallas, TX 75244-3509
800-843-8482, x2870
972-340-2870 (direct)
972-991-6061 (fax)




_______________________________________________
Koha mailing list  http://koha-community.org
Koha at lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha