IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_DownTimeEvents_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_DownTimeEvents_DataGet] AS RETURN 1')
END
GO
INSERT INTO #DowntimeEvents
SELECT
ManagedEntityMonitorRowId
,FromDateTime
,ToDateTime
,HealthState
FROM (SELECT
l.ManagedEntityMonitorRowId
,FromDateTime = l.[DateTime]
,ToDateTime = r.[DateTime]
,HealthState =
CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)
THEN l.NewHealthState
ELSE ISNULL(r.OldHealthState, l.NewHealthState)
END
FROM @StateEvent l
LEFT JOIN @StateEvent r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) AND (l.[DateTime] < r.[DateTime])
WHERE (NOT EXISTS (SELECT * FROM @StateEvent m
WHERE (l.ManagedEntityMonitorRowId = m.ManagedEntityMonitorRowId)
AND (m.[DateTime] > l.[DateTime])
AND (m.[DateTime] < ISNULL(r.[DateTime], '99991231'))
)
)
) AS tbl
WHERE HealthState <> 1