I have an advance SQL report which has previously been used within our IT dept which lists volumes, size, space left and works out how many months are left until volume is full.
What I want to do is date the number of months left til full "months_remains" and add it to the current date to find out a month/year in MM/YYYY format. E.g. if a volume showed 12 months, I would want a colume to show a date of November 2010 (as today is November 2009)
Can anyone assist in the SQL which I will need to add to achieve this.
The report current reads as:
SELECT Nodes.NodeID, Nodes.Caption AS NodeName, max(v.caption) as volume_name,
CAST(max(volumesize/1073741822.30351) AS DECIMAL(12, 2))as disksize_gb ,
CAST(max(avgdiskused/1073741822.30351 )AS DECIMAL(12, 2)) as maxavgdiskused_gb ,
CAST(min(avgdiskused/1073741822.30351 )AS DECIMAL(12, 2)) as minavgdiskused_gb ,
count(distinct datename(mm,vd.datetime)+ datename(yy,vd.datetime)) as no_of_months ,
CAST(max(volumespaceavailable/1073741822.30351) AS DECIMAL(12, 2)) as disk_remains ,
CAST((max(avgdiskused/1073741822.30351)-min(avgdiskused/1073741822.30351))/ count(distinct datename(mm,vd.datetime))AS DECIMAL(12, 2)) as avg_usage,
CAST(max(volumespaceavailable/1073741822.30351) / ((max(avgdiskused/1073741822.30351)-min(avgdiskused/1073741822.30351)+0.001) / count(distinct datename(mm,vd.datetime)+ datename(yy,vd.datetime)))
AS DECIMAL(12, 0)) as months_remains
FROM
nodes,VolumeUsage_Daily vd, volumes v
WHERE
nodes.nodeid=vd.nodeid
and vd.volumeid=v.volumeid
and nodes.nodeid=v.nodeid
and nodes.machinetype like '%Windows%'
GROUP by Nodes.Caption, Nodes.NodeID ,v.volumeid
ORDER by Nodes.Caption,v.volumeid
