[Koha] SQL report - Date issues

[Kerrie Stevens]

That looks to have fixed the issue – thank you!

SELECT biblioitems.biblionumber, biblio.copyrightdate, biblioitems.marcxml
FROM biblioitems
JOIN biblio
ON biblioitems.biblionumber=biblio.biblionumber
WHERE ExtractValue( marcxml, '//datafield[@tag="505"]/*' ) = "" and biblio.copyrightdate is null and ExtractValue( marcxml, '//datafield[@tag="260"]/*' ) = "" and ExtractValue( marcxml, '//datafield[@tag="264"]/*' ) = ""
ORDER BY biblio.copyrightdate desc


Kerrie Stevens

From: ramirouvia . [mailto:ramirouvia at gmail.com]
Sent: Wednesday, March 8, 2017 10:54 PM
To: Kerrie Stevens <KStevens at harvest.edu.au>
Cc: koha at lists.katipo.co.nz
Subject: Re: [Koha] SQL report - Date issues

Try this:

SELECT biblioitems.biblionumber, biblio.copyrightdate, biblioitems.marcxml
FROM biblioitems
JOIN biblio
ON biblioitems.biblionumber=biblio.biblionumber
WHERE ExtractValue( marcxml, '//datafield[@tag="505"]/*' ) = "" and biblio.copyrightdate is null and ExtractValue( marcxml, '//datafield[@tag="260"]/*' ) = "" and ExtractValue( marcxml, '//datafield[@tag="264"]/*' ) = ""
ORDER BY biblio.copyrightdate desc

For more information you can read this:
https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC

Regards,
Ramiro



2017-03-08 2:30 GMT-03:00 Kerrie Stevens <KStevens at harvest.edu.au<mailto:KStevens at harvest.edu.au>>:
I have a report that picks out items that have no contents (505) added and also have no date entered. The problem is that the report is picking up materials that do have dates entered in both the 260 or 264 fields, so it is not working 100% correctly.

Can anyone give me some pointers to get it working properly?

SELECT biblioitems.biblionumber, biblio.copyrightdate, biblioitems.marcxml
FROM biblioitems
JOIN biblio
ON biblioitems.biblionumber=biblio.biblionumber
WHERE biblioitems.marcxml not like "%505%" and biblio.copyrightdate is null
ORDER BY biblio.copyrightdate desc

Results that come back includes bibs with date entries in the 260 and 264 fields - why would they be selected as having nothing in the date field, when they clearly do? Eg: both formats: '1958' or '[19--]' might appear in the fields

Thanks for any help you may provide...


Kerrie Stevens
Harvest Bible College
_______________________________________________
Koha mailing list  http://koha-community.org
Koha at lists.katipo.co.nz<mailto:Koha at lists.katipo.co.nz>
https://lists.katipo.co.nz/mailman/listinfo/koha