Here's the query I used to provide monthly counts of Items added to our
repository in April of 2013, grouped by Community, Collection, and yyyy-mm
(from the date.accessioned metadata field). You can adjust the date range,
grouping, and sort order to what you need. Also, this query only lists Items
that are "active" in the repository (not deleted or withdrawn):
SELECT
cm.name AS community_name
, co.name AS collection_name
, substring(mv.text_value,1,7) AS year_month
, count(*) AS num_items_added
FROM metadatavalue mv
, item it
, collection co
, community cm
, community2collection c2c
WHERE mv.item_id = it.item_id
AND it.owning_collection = co.collection_id
AND co.collection_id = c2c.collection_id
AND c2c.community_id = cm.community_id
AND mv.metadata_field_id = 11
/* date.accessioned */
AND it.in_archive
/* Make sure the Item has not been deleted or withdrawn */
AND text_value >= '2013-04' AND text_value < '2013-05' /* specify
the starting date here in format yyyy-mm */
GROUP BY 1, 2, 3
ORDER BY 1, 2;
I run the query in PGAdminIII, export the output into a .csv file, and import
it into Excel where I make it pretty and distribute. :)
Good luck!
Sue
------------------------------------------------------------------------
Sue Thornton
Office: (757) 224-4130
Mobile: (757) 506-9903
[email protected]<mailto:[email protected]>
From: van Hoek, Wilko [mailto:[email protected]]
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:[email protected]]
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
[email protected]<mailto:[email protected]>
From: Sean Carte [mailto:[email protected]]
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
