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
Then I'd have in the column datetype entries like
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!
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
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]))
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.
1 of 1 people found this helpful
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
(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))
min(prev_starttime) over(partition by deviceid order by starttime) is null
THEN row_number() over(partition by deviceid order by starttime)
(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
WHERE ALARMCODE NOT IN ('1130', '4106', '4136', '1018')
AND NOT ENDTIME < STARTTIME
group by DEVICEID, grp2
order by starttime
Thank you for your help
Glad you were able to find the solution.
Looks good. All the best.