Forum Discussion

EVKydd's avatar
EVKydd
Level 4
11 years ago

NULL value for oldest and youngest items

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!

  • 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

     

  • 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