January 13, 2015 at 1:36 pm
I have the following table where I log the database file size
database_id Space_Used_in_MB file_id Time_collected
8, 400, 1,02/12/2014
8, 900, 1,02/20/2014
.........................
...........................
8,3700,1,01/13/2015
9, 500, 1,02/12/2014
9, 900, 1,02/20/2014
.........................
...........................
9, 1200,1,01/13/2015
I need to find out the percentage growth till today for each database. Any help is greatly appreciated. Thank you.
January 13, 2015 at 3:23 pm
There's more than one way to skin this cat. Here's one:
if object_id('tempdb.dbo.#fileSize') is not null drop table #fileSize
create table #fileSize
(
database_id int,
space_used_in_mb int,
file_id int,
time_collected date
)
insert into #fileSize
values
(8, 400, 1, '2014-02-12'),
(8, 900, 1, '2014-02-20'),
(8, 3700, 1, '2015-01-13'),
(9, 500, 1, '2014-02-12'),
(9, 900, 1, '2014-02-20'),
(9, 1200, 1, '2015-01-13')
;with minMax as
(
select
database_id,
file_id,
MinTimeCollected = min(time_collected),
MaxTimeCollected = max(time_collected)
from #fileSize
group by database_id, file_id
)
select
m.database_id,
m.file_id,
m.MinTimeCollected,
m.MaxTimeCollected,
StartSize = fn.space_used_in_mb,
EndSize = fx.space_used_in_mb,
Diff = fx.space_used_in_mb / (nullif(fn.space_used_in_mb, 0) * 1.0)
from minMax m
inner join #fileSize fn
on fn.database_id = m.database_id
and fn.file_id = m.file_id
and fn.time_collected = m.MinTimeCollected
inner join #fileSize fx
on fx.database_id = m.database_id
and fx.file_id = m.file_id
and fx.time_collected = m.MaxTimeCollected
January 13, 2015 at 3:40 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply