cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Qeury to find what should be expiring

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello all,

EV11.0.1 CHF3 (soon to be CHF5), SQL2012, Windows 2012R2

I am looking for a query which can show how many items in a journal archive are older than X years.

I am asked to provide an overview what is expected to be expired if and when expiry is turned on.

I have a query that should show this, but when running it against a Vaultstore, I get an error about an 'arethmic overflow' (or something similar)

Does anyone have a simple query, which shows this, based on the itemdate (expiry will be set for Modified Date)

Thanks

Regards. Gertjan
2 REPLIES 2

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

I use these, the convert to bigint should keep it from overflowing.  :)  You can always edit the WHERE clause for a date range

-Hourly Rate

select "Item Date" = left (convert (varchar, IDDateTime,20),14),"Hourly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024

from saveset

where IDDateTime > dateadd("dd", -7, getdate ())

group by left(convert (varchar, IDDateTime,20),14)

order by "Item Date" Desc

 

 

--Daily Rate

select "Item Date" = left (convert (varchar, IDDateTime,20),10),"Daily Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024

from saveset

where IDDateTime > dateadd("dd", -30, getdate ())

group by left(convert (varchar, IDDateTime,20),10)

order by "Item Date" Desc

 

--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

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Will try tomorrow, thanks Tony

Regards. Gertjan