---------------------------------------------------------------------------------------------------------------
------------------------------------------------ AUSEVICH.RU --------------------------------------------------
-- http://ausevich.ru/ekspert/ispolzovanie-dannyh-poluchennyh-sborshhikom-dannyh-i-zapisannyh-v-sql-server   --
---------------------------------------------------------------------------------------------------------------

SELECT 
    cdet.CounterID, 
    cdet.MachineName, 
    cdet.CounterName,
    cdet.InstanceName, 
    cdet.ObjectName, 
    cdata.CounterDateTime, 
    cdata.CounterValue,
    d.DisplayString 
FROM  
	(
		SELECT 
			cd_lr.CounterID, 
			cd_lr.CounterDateTime,
			cd_lr.GUID, 
			cd.CounterValue 
		FROM ( 
				SELECT 
					GUID,
					CounterID, 
					MAX(CounterDateTime) as CounterDateTime 
				FROM 
					CounterData 
				GROUP BY 
					CounterID,
					GUID 
				) as cd_lr 
					LEFT JOIN CounterData as cd 
						ON cd_lr.CounterID = cd.CounterID 
							AND cd_lr.CounterDateTime = cd.CounterDateTime
							AND cd_lr.GUID = cd.GUID
		) as cdata
			LEFT JOIN CounterDetails as cdet
				ON cdet.CounterID = cdata.CounterID
			LEFT JOIN dbo.DisplayToID d 
				ON d.GUID = cdata.GUID