You need to look at sequential records and count the time if the previous record was “down”. In SQL Server you can do this ... I think it looks like mysql
With OrderedRows AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY System ORDER BY StatusTime) AS RowNum
FROM yourTable
)
SELECT o_this.System,
SUM(DATEDIFF(second, o_this.StatusTime, o_next.StatusTime)) AS DownTimeSeconds
FROM OrderedRows o_this
JOIN
OrderedRows o_next
ON o_next.System = o_this.System
AND o_next.RowNum = o_this.RowNum + 1
WHERE o_this.Status = 'down'
GROUP BY o_this.System;
Without ranking functions, try something like:
SELECT t.System,
SUM(DATEDIFF('s',
t.StatusTime,
(SELECT MIN(t_next.StatusTime)
FROM yourTable AS t_next
WHERE t_next.System = t.System
AND t_next.StatusTime > t.StatusTime
)
)) AS DownTimeSeconds
FROM yourTable as t
WHERE t.Status = 'down'
GROUP BY t.System;
But this event may have problems with the fact that it is an aggregate in the aggregate.