Hi Barrett, I noticed a potential err in your given sql. Should the last line be ... and item.item_id = handle.item_id ?
OK, back to your question. The bitstream submit date is written in metadatavalue. Try this one: SELECT bitstream.bitstream_id, bitstream.name, bitstream.source, handle.handle, metadatavalue.text_value FROM bitstream, handle, bundle, item2bundle, item, metadatavalue WHERE bitstream.bitstream_id = bundle.primary_bitstream_id and bundle.bundle_id =item2bundle.bundle_id and item2bundle.item_id = item.item_id and item.item_id = handle.resource_id and item.item_id = metadatavalue.item_id and metadatavalue.metadata_field_id=XXX; The XXX is an id you can find out from your metadatafieldregistry table where dc.description.provenance belongs. Add more conditions to the sql to filter out unwanted rows. Cheers, Allen Lam. HKU Hub Administrator, http://hub.hku.hk Vanessa Barrett wrote: > > Can anyone help with a fairly basic SQL statement to report on the > date bitstreams were added to the database? > > The following is the statement I am using which works fine and gives > me all bitstreams in the database > > SELECT bitstream.bitstream_id, bitstream.name, bitstream.source, > handle.handle > > FROM bitstream, handle, bundle, item2bundle, item > > WHERE bitstream.bitstream_id = bundle.primary_bitstream_id > > and bundle.bundle_id =item2bundle.bundle_id > > and item2bundle.item_id = item.item_id > > and item.item_id = handle.handle_id > > However I can’t seem to find anything in the db_schema that would > appear to be the date a bitstream was added to the database. I did try > > AND itemsbydateaccessioned.date_accessioned > ? > > but this table seems to no longer exist and anyway it would be the > date the item was added not the date the bitstream was added. > > I am using DSpace 1.5.2. > > Cheers, > > Vanessa Barrett > Digital Services Librarian > The University of Adelaide, AUSTRALIA 5005 > Ph : +61 8 8303 4625 > e-mail: [email protected] > > CRICOS Provider Number 00123M > ----------------------------------------------------------- > IMPORTANT: This message may contain confidential or legally privileged > information. If you think it was sent to you by mistake, please delete > all copies and advise the sender. For the purposes of the SPAM Act > 2003, this email is authorised by The University of Adelaide. > > Think green: read on the screen. > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------------ > Download Intel® Parallel Studio Eval > Try the new software tools for yourself. Speed compiling, find bugs > proactively, and fine-tune applications for parallel performance. > See why Intel Parallel Studio got high marks during beta. > http://p.sf.net/sfu/intel-sw-dev > ------------------------------------------------------------------------ > > _______________________________________________ > Dspace-general mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/dspace-general > ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Dspace-general mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-general
