you can try with coalesce function as given in the blog posthttp://stackoverflow.com/questions/6270316/sql-server-select-most-recent-records-from-a-group-of-similar-records Thanks,Eswar Koneti www.eskonr.com
Date: Fri, 13 Jun 2014 15:26:28 -0500 Subject: Re: [mssms] SQL Query for reporting help - free Disk space From: [email protected] To: [email protected] Brian mason or Garth On Fri, Jun 13, 2014 at 11:58 AM, elsalvoz <[email protected]> wrote: Anyone? On Wed, Jun 11, 2014 at 9:06 AM, elsalvoz <[email protected]> wrote: Hello All, I'm trying to find a way to query for disk space <500 MB on all servers last scan plus last 10 history scans. I'm able to get the data that I'm after but I can't seem find a way to limit the history to only latest 10 rows. I'm hoping someone has created something similar or can me to adjust this query. I'm not sure if I'm approaching this task with the best query either so feel free to suggest a better approach query wise. Thanks in advance. SELECT SYS.Name AS [Server Name], CASE WHEN RSYS.Is_Virtual_Machine0 = 1 THEN 'Virtual Machine' ELSE 'Physical Machine' END AS [Hardware Type], vHSLDISK.TimeStamp AS [Scaned Date], vHSLDISK.DeviceID0 AS [System Drive C:], vHSLDISK.FreeSpace0 AS [Free space (MB)]FROM v_FullCollectionMembership AS SYS INNER JOIN v_HS_LOGICAL_DISK AS vHSLDISK ON SYS.ResourceID = vHSLDISK.ResourceID INNER JOIN v_R_System AS RSYS ON SYS.ResourceID = RSYS.ResourceIDWHERE (vHSLDISK.DeviceID0 = 'c:') AND (SYS.CollectionID = 'MAS00E1E') AND (vHSLDISK.FreeSpace0 <= 500) UNION ALLSELECT SYS.Name, CASE WHEN RSYS.Is_Virtual_Machine0 = 1 THEN 'Virtual Machine' ELSE 'Physical Machine' END AS [Hardware Type], LDISK.TimeStamp, LDISK.DeviceID0, LDISK.FreeSpace0 AS [Free space (MB)] FROM v_FullCollectionMembership AS SYS INNER JOIN v_GS_LOGICAL_DISK AS LDISK ON SYS.ResourceID = LDISK.ResourceID INNER JOIN v_R_System AS RSYS ON SYS.ResourceID = RSYS.ResourceID WHERE (LDISK.DeviceID0 = 'c:') AND (SYS.CollectionID = 'MAS00E1E') AND (LDISK.FreeSpace0 <= 500)ORDER BY [Free space (MB)]
