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: [email protected] [mailto:[email protected]] On Behalf Of Robert Williams Sent: Wednesday, March 20, 2013 10:19 AM To: [email protected] 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 [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
