December 4, 2008 at 8:43 am
Im trying to write a query that will take 2 dates, a start and an end date, from those it will return the percentage change in disk space between the two dates.
The table contains 3 Columns: a timestamp, Drive letter, and FreeSpace.
The table has information about 3 disks on it, C,D and E.
select DriveLetter,FreeMB as Diff from dbo.vw_DriveSpaceHistory
where DTStamp = @StartDate or DTStamp = @EndDate
Group by DriveLetter,FreeMB
That returns a table like this:
DriveLetter Diff
C 25166
C 25202
D 119565
D 119665
E 105715
E 108936
How do I find out the percentage differnce for all the drives above by modifying my select statment? Is it possible?
Basically I want the (1st line, minus the 2nd line) divided by the 1st line * 100 and etc for the rest of the result set.
December 4, 2008 at 8:56 am
select D1.DriveLetter,
sum(D1.FreeMB) as free_mb_start, sum(D2.FreeMB) as free_mb_end,
(sum(D2.FreeMB) / sum(D1.FreeMB) * 100) - 1 as percent_change
from dbo.vw_DriveSpaceHistory as D1
join dbo.vw_DriveSpaceHistory as D2
on D1.DriveLetter = D2.DriveLetter
where D1.DTStamp = @StartDate and D2.DTStamp = @EndDate
Group by D1.DriveLetter
December 4, 2008 at 9:09 am
Ah very smart! I didn't think of joining the table to itself!
Works a treat. Thank you very much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply