Calculate the sum of times

[Anonymous]

Hi,

I hope you are able to help me.

I have to calculate the lack time during a production. This lacks are defined by an "EventID". The first numbers defines the type of the Event. The last defines if it is a Event-Start (1) or the Event-End (2). The StartDT column is my Date with Time. 

The column Line is only for better understanding.

My result for Event 1 should be the sum of

DATEDIFF ( StartDT (Line 1) ; StartDT (Line 2) ; SECONDS) = 12

DATEDIFF ( StartDT (Line 4) ; StartDT (Line 3) ; SECONDS) = 2

=> Lack Time for Event 1 = 14 seconds.

For Event 2 it is 12 seconds and for the Event of SerialNo R01190 and Event 3 it is 12 seconds.

StationID	StartDT	ModuleSerialNo	EventID		Line
1	2019-02-28 10:10:21	003Q	1002		1
1	2019-02-28 10:10:09	003Q	1001		2
1	2019-02-28 10:10:25	003Q	1002		3
1	2019-02-28 10:10:23	003Q	1001		4
1	2019-02-28 10:11:09	003Q	2001		5
1	2019-02-28 10:11:21	003Q	2002		6
1	2019-02-28 10:10:09	R0119	3001		7
1	2019-02-28 10:10:21	R01190	3002		8

Maybe someone has a solution.

Best regards

Timo

[MattAllington · Accepted solution]

You will really struggle to get a good working solution if you load your data like this.  I suggest you watch my video to see how you can create time durations for each row.  Then you can just add the durations with SUM 

https://www.youtube.com/watch?v=xN2IRXQ2CvI