On 15 July 2015 at 09:14, Admire Mutsikiwa <amutsik...@uzlib.uz.ac.zw>
wrote:
> I am interested in an SQL query that will enable me to list the number of
> articles deposited into dspace per month per collection or by e-person.
Hi Admire
A couple of years ago Susan Thornton helped me out with a query similar to
what I use on 5.2, below:
SELECT
cm.community_id,
co.collection_id,
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.resource_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
AND it.in_archive
AND text_value >= '2015-06'
AND text_value < '2015-07'
GROUP BY 1, 2, 3 ORDER BY 2;
This is Susan's original SQL:
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;
Sean
--
------------------------------------------------------------------------------
Don't Limit Your Business. Reach for the Cloud.
GigeNET's Cloud Solutions provide you with the tools and support that
you need to offload your IT needs and focus on growing your business.
Configured For All Businesses. Start Your Cloud Today.
https://www.gigenetcloud.com/
_______________________________________________
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
