07-07-2016 01:55 AM
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
07-07-2016 07:24 AM
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
07-07-2016 09:02 AM
Will try tomorrow, thanks Tony