Hi,
I have been struggling for a few days with this issue and can't seem to figure it. I am still a novice at Tranact-SQL.
Below is the query i am trying to run to get a list of computers from a table based on the available free space. I'd like to group them based on the available space, for example, up to 1 GB, 1-2 GB, etc.
With Disk_Space (ResourceID, ComputerName, Description, DiskName, VolumeName, FileSystem, Size, FreeSpace)AS
(
SELECT distinct
SYS.ResourceID,
SYS.Name,
LDISK.Description0,
LDISK.DeviceID0,
LDISK.VolumeName0,
LDISK.FileSystem0,
LDISK.Size0,
LDISK.FreeSpace0
FROM
v_R_System rs
JOIN v_FullCollectionMembership_Valid SYS ON rs.ResourceID = SYS.ResourceID
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
WHERE LDISK.DriveType0 = 3 AND LDISK.DeviceID0 = 'C:' AND sys.CollectionID = 'xxxxxx
)
select ds.ResourceID, ds.ComputerName, Description, DiskName, VolumeName, FileSystem, Size AS 'Total Size in MB', FreeSpace AS 'Free Space in MB',
CASE When FreeSpace BETWEEN 0 AND 1024 Then 'Up to 1GB'
When Freespace BETWEEN 1024 AND 2048 Then '1GB To 2GB'
When Freespace BETWEEN 2048 AND 5120 Then '2GB To 5GB'
When FreeSpace Between 5120 AND 10240 Then '5GB To 10GB'
When FreeSpace > 10240 Then 'More than 10GB'
When FreeSpace IS NULL Then 'No Inventory found'
END AS FreeSpaceRange
From Disk_Space ds
where ds.FreeSpace='1GB To 2GB'
However when i run this query, i get an error :
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1GB To 2GB' to data type int.
Any help is greatly appreciated.
Thanks,
Devesh.