cancel
Showing results for 
Search instead for 
Did you mean: 

EV trend analysis or archiving rate patterns

ashks2014
Level 5

Hi,

Looking for the best way to do some trend analysis for EV. So i'd like to know the volume of data we'll archive in a year if we continue at the current rate? Or something similar.

We have the reporting services installed so what are the best ways to measure this?

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

I tend to use these, just change the count in the where clause to fit your needs

--Hourly Rate

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

from saveset

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

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

order by "Archived Date" Desc

 

 

--Daily Rate

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

from saveset

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

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

order by "Archived Date" Desc

 

--Monthly Rate

select "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024

from saveset

where archiveddate > dateadd("mm", -12, getdate ())

group by left(convert (varchar, archiveddate,20),7)

order by "Archived Date" Desc

 

--Yearly Rate

select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024

from saveset

where archiveddate > dateadd("YY", -10, getdate ())

group by left(convert (varchar, archiveddate,20),4)

order by "Archived Date" Desc

View solution in original post

2 REPLIES 2

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

if you have reporting services then you're good to go but you could also use one of the many sql queries taht have been posted here which would give you what you're asking for

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

I tend to use these, just change the count in the where clause to fit your needs

--Hourly Rate

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

from saveset

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

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

order by "Archived Date" Desc

 

 

--Daily Rate

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

from saveset

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

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

order by "Archived Date" Desc

 

--Monthly Rate

select "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024

from saveset

where archiveddate > dateadd("mm", -12, getdate ())

group by left(convert (varchar, archiveddate,20),7)

order by "Archived Date" Desc

 

--Yearly Rate

select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024

from saveset

where archiveddate > dateadd("YY", -10, getdate ())

group by left(convert (varchar, archiveddate,20),4)

order by "Archived Date" Desc