SQL Query
So I use these queries for rates.
For Archived Date it would be these:
--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
For Item Date it would be these:
--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
