Disk Space Monitor - Further Scripts

Further sql to support the drive space monitor.

Daily diskspace view -

create view ViewDailyDiskFree
as
select 
 computer, 
 drive, 
 min(percentage) as percentage,
 CAST(FLOOR(CAST(Date AS float)) AS datetime) as dateoccurred,
 datediff(dd,date,getdate()) as daysago
from 
 tbldiskspace
group by 
 computer, drive, CAST(FLOOR(CAST(Date AS float)) AS datetime), datediff(dd,date,getdate())

SQL 7/2000 Cross-tab.
Uses 'case' statement and 'group by' to acheive cross-tab report >

select  computer, 
  drive,
  sum(case daysago when 6 then percentage else 0 end) as percentage_day_6,
  sum(case daysago when 5 then percentage else 0 end) as percentage_day_5,
  sum(case daysago when 4 then percentage else 0 end) as percentage_day_4,
  sum(case daysago when 3 then percentage else 0 end) as percentage_day_3,
  sum(case daysago when 2 then percentage else 0 end) as percentage_day_2,
  sum(case daysago when 1 then percentage else 0 end) as percentage_day_1,
  sum(case daysago when 0 then percentage else 0 end) as percentage_today
from
 ViewDailyDiskFree
group by
 computer, drive
order by
 computer, drive

SQL 2005+ version
Uses PIVOT operator to achieve cross-tab report.

select  computer,
  drive,
  isnull([6],0) as percentage_day_6,
  isnull([5],0) as percentage_day_5,
  isnull([4],0) as percentage_day_4,
  isnull([3],0) as percentage_day_3,
  isnull([2],0) as percentage_day_2,
  isnull([1],0) as percentage_day_1,
  isnull([0],0) as percentage_today
from 
 (select computer, drive, percentage , daysago from ViewDailyDiskFree) p
pivot
 (
 sum(percentage)
 for daysago in ([6],[5],[4],[3],[2],[1],[0])
 ) as pivottable