Re: [Dspace-tech] Annual submission Rates

2010-10-13 Thread Admire 
Thank you very much for the SQL statement. It really does the job.  I only
added an order by year  to the outer query, but the job was already well
done. Here is the query:

 

SELECT to_char(date_trunc('year', t1.ts), '') AS year, count(*) FROM
(SELECT to_timestamp(text_value, '-MM-DD') AS ts FROM metadatavalue
WHERE metadata_field_id = 12) t1 GROUP BY date_trunc('year', t1.ts) order by
year;

 

 

Thnaks for saving my dear.

 

From: George Hamilton [mailto:george.hamil...@ed.ac.uk] 
Sent: Tuesday, October 12, 2010 6:51 PM
To: dspace-tech@lists.sourceforge.net
Subject: Re: [Dspace-tech] Annual submission Rates

 

Hi

The following should work (on Postgres):

SELECT to_char(date_trunc('year', t1.ts), '') AS year, count(*) FROM
(SELECT to_timestamp(text_value, '-MM-DD') AS ts FROM metadatavalue
WHERE metadata_field_id = 12) t1 GROUP BY date_trunc('year', t1.ts);

George

On 12/10/10 16:47, Admire wrote: 

Hi All,

Hope I find you well.  I am interested in establishing the number of items
deposited each year in our IR. Is there anyone out there who had done that
and can provide me with the SQL  statement to do that.

 

Regards,

 

Admire

 
 

--
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
 
 
___
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
  

 


-- 
This message has been scanned for viruses and 
dangerous content by  <http://www.mailscanner.info/> MailScanner, and is 
believed to be clean. 

--
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb___
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech

Re: [Dspace-tech] Annual submission Rates

2010-10-12 Thread George Hamilton 

Hi

The following should work (on Postgres):

SELECT to_char(date_trunc('year', t1.ts), '') AS year, count(*) FROM 
(SELECT to_timestamp(text_value, '-MM-DD') AS ts FROM metadatavalue 
WHERE metadata_field_id = 12) t1 GROUP BY date_trunc('year', t1.ts);


George

On 12/10/10 16:47, Admire wrote:


Hi All,

Hope I find you well.  I am interested in establishing the number of 
items deposited each year in our IR. Is there anyone out there who had 
done that and can provide me with the SQL  statement to do that.


Regards,

Admire


--
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2&  L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb


___
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
   


The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.
--
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb___
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech

Re: [Dspace-tech] Annual submission Rates

2010-10-12 Thread Claudia Jürgen 
Hello Admire,

assuming that you count as deposited, when the item was publically 
available (reached the in archive status) the sql  for this year would be:

select count(item_id) from metadatavalue where metadata_field_id=12 and 
text_value like '2010%';

metadata_field_id = 12 refers to dc.date.available on a default installation

Hope that helps

Claudia Jürgen

Am 12.10.2010 17:47, schrieb Admire:
> Hi All,
>
> Hope I find you well.  I am interested in establishing the number of items
> deposited each year in our IR. Is there anyone out there who had done that
> and can provide me with the SQL  statement to do that.
>
>
>
> Regards,
>
>
>
> Admire
>
>
>
>
>
> --
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2&  L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
>
>
>
> ___
> DSpace-tech mailing list
> DSpace-tech@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/dspace-tech

-- 
Claudia Juergen
Universitaetsbibliothek Dortmund
Eldorado
0231/755-4043
https://eldorado.tu-dortmund.de/

--
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
___
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech