DSpace – Item count for period report example
Posted On February 3, 2014
The approach used here is inelegant and might need some optimisation but does its purpose!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
SELECT * FROM item WHERE item_id=4347SELECT * FROM bi_item WHERE item_id=4347SELECT * FROM metadatavalue WHERE item_id=4347 ORDER BY metadata_field_id, place ASCSELECT * FROM handleSELECT * FROM metadatafieldregistry -- date accessioned is metadata_field_id=11, date available is metadata_field_id=12. -- Items have multiple dates for accessioned and available!!!SELECT i.item_id, i.last_modified, i.in_archive, i.withdrawn, mv.text_value AS date, ( SELECT handle FROM handle WHERE resource_id=i.item_id) AS handle FROM item i LEFT OUTER JOIN metadatavalue mv ON i.item_id = mv.item_id WHERE i.in_archive IS true AND i.withdrawn IS false --and mv.metadata_field_id=12 and mv.place=1 -- earliest date available --and mv.metadata_field_id=12 and mv.place=(select max(place) from metadatavalue where metadata_field_id=12 and item_id=i.item_id) -- latest date available AND mv.metadata_field_id=11 AND mv.place=1 -- earliest date accessioned --and mv.metadata_field_id=11 and mv.place=(select max(place) from metadatavalue where metadata_field_id=11 and item_id=i.item_id) -- latest date accessioned AND cast(mv.text_value AS timestamp WITH time zone) > '2014-01-01' --and cast(mv.text_value as timestamp with time zone) < '2015-01-01' --and i.item_id=4347 ORDER BY i.item_id DESCSELECT * FROM bi_item WHERE sort_2 > '2014-01-01' --and sort_2 < '2015-01-01' ORDER BY item_id DESCSELECT * FROM bi_item WHERE sort_2 IS NULLSELECT Count('x') FROM item i LEFT OUTER JOIN metadatavalue mv ON i.item_id = mv.item_id WHERE 1=1 AND i.in_archive IS true AND i.withdrawn IS false AND mv.metadata_field_id=12 AND mv.place=1 -- earliest date available --and mv.metadata_field_id=12 and mv.place=(select max(place) from metadatavalue where metadata_field_id=12 and item_id=i.item_id) -- latest date available --and mv.metadata_field_id=11 and mv.place=1 -- earliest date accessioned --and mv.metadata_field_id=11 and mv.place=(select max(place) from metadatavalue where metadata_field_id=11 and item_id=i.item_id) -- latest date accessioned --and cast(mv.text_value as timestamp with time zone) < '2013-06-30T23:59:59' --and cast(mv.text_value as timestamp with time zone) > '2014-03-01'SELECT Count('x') FROM bi_item WHERE sort_2 > '2014-01-01' --and sort_2 < '2015-01-01' |
* dc.date.accessioned = the date/time that DSpace received this document (i.e. date/time the item was created in DSpace)