SQL Server - noob with SQL query

Hi, I have a SQL query 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. E.g. if a volume showed 12 months, I would output 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.

I think I need to use DATEADD(month,months_remain,GETDATE()) AS full_date, but can get it to work as I get error.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'months_remain'.

The query currently 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(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

Since your months_remain column is a computed column, you need to use a sub-query to make your query work.

SELECT *, DATEADD(month,months_remain,GETDATE()) AS full_date FROM (

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(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) A

Regards,
http://www.sql-server-helper.com/sql-server-2008/connection-error-1326.aspx

Thanks for the reply, but as a totally newbie to SQL, can you confirm where u have put the line

SELECT *, DATEADD(month,months_remain,GETDATE()) AS full_date FROM ( goes where is shown in your reply?

If so where do I close the bracket after the from?

Sorry for the questions but really do not know anything about SQL

The closing bracket is just after the GROUP BY :

GROUP by Nodes.Caption, Nodes.NodeID ,v.volumeid) A

You need to include the table alias I specified, in this case "A".

Regards,
http://www.sql-server-helper.com/tips/date-formats.aspx

Use a Common Table Expression:

WITH Capacity AS
(SELECT n.NodeID, n.Caption AS NodeName, v.volumeid, MAX(v.Caption) AS volume_name,
 CAST(MAX(volumesize/1073741822.30351) AS DECIMAL(12, 2))AS disksize_gb,
 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_remain
 FROM Nodes n JOIN VolumeUsage_Daily vd ON n.NodeId = vd.nodeid
 JOIN volumes v ON vd.volumeid = v.volumeid AND n.NodeId = v.NodeId
 WHERE  n.machinetype LIKE '%Windows%'
 GROUP BY n.Caption, n.NodeID, v.volumeid)
SELECT NodeId, NodeName, volumeid, volume_name, disksize_gb, disk_remains, avg_usage, months_remain,
DATEADD(m, months_remain, GETDATE()) AS full_date
FROM Capacity
ORDER BY NodeName, volumeid;

F Cali, I have tried the extra lines you posted but I get a message of "Msg 207, Level 16, State 1, Line 1 Invalid column name 'months_remain'." when I execute

Jonathan VH, thanks for your SQL you posted, but when I execute it I get the message "Msg 517, Level 16, State 1, Line 1  Adding a value to a 'datetime' column caused overflow."

It sounds as though your months_remain value can be huge, so the DATEADD would evaluate to a full_date in a year beyond 9999. You could hard-code for that eventuality:

CASE WHEN months_remain > 95000 THEN CAST('99991231' AS datetime) ELSE DATEADD(m, months_remain, GETDATE()) END AS full_date

The name of your column was months_remains and not months_remain, so just change the query I provided for that particular column.

Regards,
http://www.sql-server-helper.com/sql-server-2008/import-export-unknown-column-type-geography-geometry.aspx

F Cali, where would I put the Sort By within?