ChrisM@Work (5/21/2013)
What are typical values for SUM(nUsed_Min) and SUM(nSize)?Your query is easier to read if table aliases are used:
SELECT TOP 10d.nDeviceID,
d.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min) AS Min_use,
MAX(nused_Max) AS Max_use,
SUM(nSize) AS Total_disk,
(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS total_used
FROM dbo.StatisticalDisk sd
LEFT JOIN dbo.StatisticalDiskIdentification sdi
on sdi.nStatisticalDiskIdentificationID = sd.nStatisticalDiskIdentificationID
LEFT JOIN dbo.PivotStatisticalMonitorTypeToDevice pm
on pm.nPivotStatisticalMonitorTypeToDeviceID = sdi.nPivotStatisticalMonitorTypeToDeviceID
LEFT JOIN Device d
on d.nDeviceID = pm.nDeviceID
INNER JOIN pivotdevicetogroup pdg
ON pdg.nDeviceID = Device.nDeviceID
INNER JOIN DeviceGroup dg
ON pdg.nDeviceGroupID = dg.nDeviceGroupID
INNER JOIN [time]
ON sd.dPollTime = [time].PK_Date
WHERE sd.dPollTime = dateadd(MM, 1, '2013')
AND dg.sGroupName IN ('CUSTOMER')
GROUP BY d.nDeviceID,
d.sDisplayName,
dg.sgroupname,
sDescription,
dPollTime,
nSize
HAVING (CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
ORDER BY dPollTime DESC
When i run then the query i get the following result:
ndeviceID sdisplayname sgroupname sdescription dpolltime max_use min_use total_disk total_used
552 LWP-MEIPS Mondriaan C:\ 2013-02-01 00:00:00.00068042613766803189248849671372880,06 847
I want Max_use and Min_use and Total disk with a shorter number.
The Sum of Min and nsize mean to make a percentage about the avg use of the total disk.