
1. Re: How can I calculate the total downtime of my system, which consists of many components?
swaroop.gantela Aug 1, 2018 8:37 PM (in response to Florian Raab)Florian,
This may not be feasible for you,
but maybe it can give ideas:
Gannt chart  machine availability  Merge failure during the same time

2. Re: How can I calculate the total downtime of my system, which consists of many components?
Florian Raab Aug 3, 2018 6:13 AM (in response to swaroop.gantela)Thank you for your reply, but I don't think that really is what I'm searching for.
I don't want to make a static calculation and do it again every time I need this, I need something dynamic, also my SQL database is very large..so I don't think the way to cross join my table with another one is really sufficient.
Maybe I just don't get the idea of what you try to tell me..
But what I found in the other thread is an idea to restructure my SQL table like in
https://redheadedstepdata.io/lookupvstransactional/?content=Reference%20Post
Then I'd have in the column datetype entries like
start 12:35:21
start 12:37:51
end 12:40:35
start 12:41:10
start 12:50:25
start 12:56:58
end 14:21:20
end xxxxxxx
end xxxxxxx
But I still don't know how to figure out my table calculation.
At first I thought about going from the first start to the next end, but if there are 3 starts and one end, then this end is not the real end of my downtime..so by having three starts, I have to have the third end entry to have the total downtime. I don't know how to really describe what my problem then is, but maybe you get the idea.
Although, thank you very much for spending your time helping me!

3. Re: How can I calculate the total downtime of my system, which consists of many components?
swaroop.gantela Aug 3, 2018 2:52 PM (in response to Florian Raab)Florian,
Apologies for my previous response.
It is the thorough general solution for such problems,
but it is not practical for large datasets.
It think there may be a way with your original data setup,
with each row having the ErrorStart and ErrorResolved Times.
Please see if the below could give ideas.
I think you were correctly describing it earlier.
It is then a series of conditionals to compare the current row to previous row.
IF FIRST()=0 THEN SUM([downtime straight]) // first row, return downtime
ELSEIF ATTR([Error Start])>LOOKUP(ATTR([Error Resolved]),1) // starts after previous resolved, return full downtime
THEN SUM([downtime straight])
ELSEIF ATTR([Error Resolved])<LOOKUP(ATTR([Error Resolved]),1) // resolved before previous, return 0
THEN 0
ELSEIF ATTR([Error Start])<LOOKUP(ATTR([Error Resolved]),1) // starts before previous resolved but ends after, return overhang
THEN DATEDIFF('minute',LOOKUP(ATTR([Error Resolved]),1),ATTR([Error Resolved]))END
This may not cover all possible situations, but I think it gives the idea.
You will need to check your Table Calculation Settings to match your true setup.I think the key thing is to put ErrorStart at the top.
Please see workbook v10.3 attached in the Forum Thread.

277489down.xlsx 8.1 KB

277489down.twbx 17.4 KB


4. Re: How can I calculate the total downtime of my system, which consists of many components?
Florian Raab Aug 16, 2018 6:09 AM (in response to swaroop.gantela)1 of 1 people found this helpfulHey,
I worked around a little bit and solved it with a SQL Query, which was not too easy to receive the correct results for every scenario.
but there it is.
SELECT deviceid, min(starttime) starttime, MAX(endtime) endtime from
(SELECT DEVICEID, starttime, endtime, max(grp) over(partition by deviceid order by starttime asc) grp2
FROM
(SELECT deviceid, starttime, endtime,
CASE WHEN (starttime not between
min(prev_starttime) over(partition by deviceid order by starttime) and
max(prev_endtime) over (partition by deviceid order by starttime))
or
min(prev_starttime) over(partition by deviceid order by starttime) is null
THEN row_number() over(partition by deviceid order by starttime)
end grp
FROM
(SELECT Deviceid, starttime, endtime,
lag(starttime) over(partition by deviceid order by starttime) prev_starttime,
lag(endtime) over(partition by deviceid order by starttime) prev_endtime
from TABLENAME
WHERE ALARMCODE NOT IN ('1130', '4106', '4136', '1018')
AND NOT ENDTIME < STARTTIME
)
a)
b)
group by DEVICEID, grp2
order by starttime
Thank you for your help

5. Re: How can I calculate the total downtime of my system, which consists of many components?
swaroop.gantela Aug 16, 2018 12:05 PM (in response to Florian Raab)Florian,
Glad you were able to find the solution.
Looks good. All the best.