Filtering Duration and linked condition over several rows
Hello,
I have a sample data here:
QueueNo.
ActionNo.
ActionName
Start
Finished
1111
5000
Start
08:00
08:05
1111
5001
Moving
08:05
08:10
1111
5002
Parking
08:10
08:12
1111
5003
Moving
08:12
08:29
1111
5004
Success
08:29
08:30
2222
5100
Start
09:00
09:00
2222
5101
Moving
09:01
09:10
2222
5102
Failed
09:10
09:10
Its a wagon which is moving automatically and creates data of every action it does. As you can see, only the distinct Queuenumber distinguish if the whole Mission with its tiny subactions are belonging together. Different mission can have totally different Actions. Between different continuous actions there is no gap in time. Now I want to know the duration from the very first start of a mission to the very last action of that sameQueueNo. For QueueNo: 1111 I want to get 30minutes duration time. And for 2222 it would be 10minutes.
The second thing I need to filter is the count of these QueueNo. which has a 'start' and a 'success' ending and the same also for other processes, when e.g. the process 'failed'.