[Koha] SQL report - Date issues

[ramirouvia .]

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>:

> 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
> https://lists.katipo.co.nz/mailman/listinfo/koha
>