Groups down outage with duration of Hours/Min

sv9780

Hello Team,

Getting an error saying SubSequent query not supported. Any help from our team.

SELECT c.Name
	,StartTime.EventTime AS DownTime
	,(
		SELECT TOP 1 EndTime.EventTime
		FROM Orion.Events AS Endtime
		INNER JOIN Orion.Container c ON endtime.NetObjectID = c.ContainerID
		INNER JOIN Orion.Events AS StartTime ON starttime.NetObjectID = c.ContainerID
		WHERE EndTime.EventTime > StartTime.EventTime
			AND EndTime.NetObjectID = StartTime.NetObjectID
			AND EndTime.EventType = 52
			AND EndTime.NetObjectType = 'C'
		ORDER BY EndTime.EventTime
		) AS UpTime
	,MINUTEDIFF(StartTime.EventTime, (
			SELECT TOP 1 EndTime.EventTime
			FROM Orion.Events AS Endtime
			INNER JOIN Orion.Container c ON endtime.NetObjectID = c.ContainerID
			INNER JOIN Orion.Events AS StartTime ON starttime.NetObjectID = c.ContainerID
			WHERE EndTime.EventTime > StartTime.EventTime
				AND EndTime.NetObjectID = StartTime.NetObjectID
				AND EndTime.EventType = 52
				AND EndTime.NetObjectType = 'C'
			ORDER BY EndTime.EventTime
			)) AS MinutesDown
FROM Orion.Events starttime
INNER JOIN Orion.Container c ON starttime.NetObjectID = c.ContainerID
WHERE (StartTime.EventType = 50)
	AND (
		StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE())
			AND DATETRUNC('day', ADDDAY(10, GETDATE()))
		)
ORDER BY StartTime.EventTime DESC

adam.beedell

Offhand, error says something about subquery format not being liked, subquery format does indeed look a bit weird

If they all individually work (please confirm, different environments on this end), gotta stick em together differently or try and reduce em down

I'm not sure they're necessary. I guess it's in there because you've got the two different event types for start and finish (?), you could have WHERE Starttime.EventType in (50, 52)  ORDER BY.. to access both sans subquery, and a self-join gives you access to both records in the same row.

Something in that space probably does it