Thanks for the great query!
Sue
------------------------------------------------------------------------
Sue Thornton
Office: (757) 224-4130
Mobile: (757) 506-9903
susan.m.thorn...@nasa.gov<mailto:susan.m.thorn...@nasa.gov>
From: van Hoek, Wilko [mailto:wilko.vanh...@gesis.org]
Sent: Tuesday, May 14, 2013 3:47 AM
To: Thornton, Susan M. (LARC-B702)[LITES]; Sean Carte; dspace-tech
Subject: AW: [Dspace-tech] Number of items archived each month
Hello,
I don't know whether you have already solved it, but I've put up the following
query to sum up accession and availability dates of documents on a monthly
basis:
SELECT * FROM
((SELECT 'dc.date.accessioned' AS field, substring(text_value FROM 1 FOR 4) AS
year,substring(text_value FROM 6 FOR 2) AS month, COUNT(item_id) as numberOfDocs
FROM metadatavalue
WHERE metadata_field_id = (SELECT metadata_field_id FROM metadatafieldregistry
WHERE metadata_schema_id = 1 AND element = 'date' AND qualifier = 'accessioned')
GROUP BY year, month ORDER BY year,month)
UNION
(SELECT 'dc.date.available' AS field, substring(text_value FROM 1 FOR 4) AS
year,substring(text_value FROM 6 FOR 2) AS month, COUNT(item_id) as numberOfDocs
FROM metadatavalue
WHERE metadata_field_id = (SELECT metadata_field_id FROM metadatafieldregistry
WHERE metadata_schema_id = 1 AND element = 'date' AND qualifier = 'available')
GROUP BY year, month ORDER BY year,month)) AS accessAvail
ORDER BY field, year, month
Maybe this is helpful to someone!
Wilko
Von: Thornton, Susan M. (LARC-B702)[LITES] [mailto:susan.m.thorn...@nasa.gov]
Gesendet: Montag, 13. Mai 2013 20:27
An: Sean Carte; dspace-tech
Betreff: Re: [Dspace-tech] Number of items archived each month
You will have to join the Item table to the metadatavalue table where
metadata_field_id = 99 (where 99 = date.accessioned). Do your date
specification on date.accessioned since that's the date/timestamp the Item was
added to the repository.
Good luck,
Sue
------------------------------------------------------------------------
Sue Thornton
Office: (757) 224-4130
Mobile: (757) 506-9903
susan.m.thorn...@nasa.gov<mailto:susan.m.thorn...@nasa.gov>
From: Sean Carte [mailto:sean.ca...@gmail.com]
Sent: Monday, May 06, 2013 5:57 AM
To: dspace-tech
Subject: [Dspace-tech] Number of items archived each month
Is it possible to get a count of the number of items archived in a particular
month?
I've been using the following:
SELECT COUNT(*) FROM item WHERE last_modified >= '2013-04-01 00:00:00' AND
last_modified < '2013-05-01 00:00:00' AND owning_collection = 22;
... but I don't think this is giving me what I need. Presumably 'last_modified'
gets updated whenever anything happens to an item. Where does the issue date of
the record live?
Sean
--
------------------------------------------------------------------------------
AlienVault Unified Security Management (USM) platform delivers complete
security visibility with the essential security capabilities. Easily and
efficiently configure, manage, and operate all of your security controls
from a single console and one unified framework. Download a free trial.
http://p.sf.net/sfu/alienvault_d2d
_______________________________________________
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
