My post about how to receive approximate last contacted time spawn good feedback and thanks to Robin Drake for rewriting my SQL script without use of cursors while producing single grid. Following is his version:

use OperationsManager

Go

declare @substract float

declare @numberOfMissing float

declare @interval float

-- Get the number of missing heartbeats

select @numberOfMissing = SettingValue from GlobalSettings GS

join ManagedTypeProperty MTP with(nolock) on GS.ManagedTypePropertyId = MTP.ManagedTypePropertyId

where MTP.ManagedTypePropertyName = 'NumberOfMissingHeartBeatsToMarkMachineDown'

-- Get the heartbeat interval

select @interval = SettingValue from GlobalSettings GS

join ManagedTypeProperty MTP with(nolock) on GS.ManagedTypePropertyId = MTP.ManagedTypePropertyId

where MTP.ManagedTypePropertyName = 'HeartbeatInterval'

-- Calculate the amount of lapsed time before a system is marked as non contactable

select @substract = (@numberOfMissing * @interval)/100000

select B.DisplayName, AH.TimeStarted, (cast((cast(tmp.MaxTimeStarted as float)- @substract) as datetime)) as [ApproxLastContactedTime (UTC)],

dateadd(hh, +9, (cast((cast(tmp.MaxTimeStarted as float)- @substract) as datetime))) as 'ApproxLastContactedTime (Pacific)' from Availability A

join BaseManagedEntity B with(nolock) on B.BaseManagedEntityId = A.BaseManagedEntityId

join AvailabilityHistory AH with(nolock) on AH.BaseManagedEntityId = A.BaseManagedEntityId

join

(

select MAX(AHTMP.TimeStarted) AS MaxTimeStarted, BME.BaseManagedEntityId from AvailabilityHistory AHTMP

join BaseManagedEntity BME with(nolock) on BME.BaseManagedEntityId = AHTMP.BaseManagedEntityId

where BME.IsDeleted = 0

group by BME.BaseManagedEntityId

)

TMP on AH.TimeStarted = MaxTimeStarted

where A.IsAvailable = 0 and B.IsDeleted = 0

Robin, Thanks again!