cancel
Showing results for 
Search instead for 
Did you mean: 

EV11.01 - get item date and archiving date

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello all,

I'm asked to figure out if items are archived from a Journal Mailbox within 24 hours. The only way I could think of was to run a DA query, and look at the items found, and possibly get it from there.

It might however be possible using a SQL query against the Vault Store/Archive? Does anyone know if this is possible somehow?

I'd like to be able to run a query which looks at items archived on a certain day, and get the item date for those. (maybe even only get a list of item-dates which are 24 hours older than the archived date). Any advise on if this is possible, what to look for, or even a query would be highly appreciated!

Thanks, GJ

 

Regards. Gertjan
1 ACCEPTED SOLUTION

Accepted Solutions

ChrisLangevin
Level 6
Employee

Gertjan,

This should be possible using the IdDateTime and ArchivedDate columns in the Saveset table.

 

--Get item dates for items archived on a specific date
SELECT Idtransaction, IdDateTime, ArchivedDate
FROM Saveset
WHERE ArchivedDate >= '2015-10-22' AND ArchivedDate < '2015-10-23'
--Get items that were archived more than one day after arrival
SELECT Idtransaction, IdDateTime, ArchivedDate
FROM Saveset
WHERE IdDateTime < DATEADD(DAY, -1, ArchivedDate)
--Get items that were archived on a specific date and more than one day after arrival
SELECT Idtransaction, IdDateTime, ArchivedDate
FROM Saveset
WHERE ArchivedDate >= '2015-10-22' AND ArchivedDate < '2015-10-23'
AND IdDateTime < DATEADD(DAY, -1, ArchivedDate)

--Chris

View solution in original post

2 REPLIES 2

ChrisLangevin
Level 6
Employee

Gertjan,

This should be possible using the IdDateTime and ArchivedDate columns in the Saveset table.

 

--Get item dates for items archived on a specific date
SELECT Idtransaction, IdDateTime, ArchivedDate
FROM Saveset
WHERE ArchivedDate >= '2015-10-22' AND ArchivedDate < '2015-10-23'
--Get items that were archived more than one day after arrival
SELECT Idtransaction, IdDateTime, ArchivedDate
FROM Saveset
WHERE IdDateTime < DATEADD(DAY, -1, ArchivedDate)
--Get items that were archived on a specific date and more than one day after arrival
SELECT Idtransaction, IdDateTime, ArchivedDate
FROM Saveset
WHERE ArchivedDate >= '2015-10-22' AND ArchivedDate < '2015-10-23'
AND IdDateTime < DATEADD(DAY, -1, ArchivedDate)

--Chris

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

That last one does exactly what I need. Thanks a mill Chris. I'd give you 100 kudo's if I could.

Regards. Gertjan