NULL value for oldest and youngest items

[EVKydd]

Hello All,

I am trying to calculate how much storage we would be able to repurpose if we convinced our legal department to let us purge items older than 5 years.

 I ran this query in our SQL environment but the results are not complete (screenshot attached).

USE EnterpriseVaultDirectory
SELECT ArchiveName, OldestItemDateUTC, YoungestItemDateUTC, HighestItemSequenceNumber, IndexedItems
FROM EnterpriseVaultDirectory.dbo.IndexView
ORDER BY OldestItemDateUTC

The majority of the archives return NULL for the oldest and youngest items. Is there a way to synchronize the tables?

Thanks in advance!

[TonySterling · Accepted solution]

Wouldn't it be better to use something like this?

You can just change the where clause to fit your needs.

--Monthly Rate
select "Item Date" = left (convert (varchar, iddatetime,20),7),"Monthly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024
from saveset
where iddatetime > dateadd("mm", -12, getdate ())
group by left(convert (varchar, iddatetime,20),7)
order by "Item Date" Desc

--Yearly Rate
select "Item Date" = left (convert (varchar, iddatetime,20),4),"Yearly Rate" = count (*),"Size" = sum (Convert(bigint,itemsize))/1024
from saveset
where iddatetime > dateadd("YY", -10, getdate ())
group by left(convert (varchar, iddatetime,20),4)
order by "Item Date" Desc