Date Calculations within same field

[GS500]

Hello,

I need help with a load script.  I'm trying to calculate the difference between multiple time records with different Event IDs, for each PersonID.  I have a table like below.  EventID 1 = Start Time, EventID2 = End Time.  There can be multiple Start and End Times per date.

PersonID	DateTime	EventID
1	1/1/19 7:05 AM	1
2	1/1/19 8:03 AM	1
1	1/1/19 12:00 PM	2
1	1/1/19 1:00 PM	1
2	1/1/19 4:00 PM	2
1	1/1/19 4:30 PM	2

I need to find the total duration between the EventIDs 1 and 2 for each Person ID and Date like:

PersonID	Date	Duration
1	1/1/2019	8:25:00
2	1/1/2019	7:57:00

I think I need to use some combination of Previous and Peek, but not sure exactly how to accomplish this.  Any help pointing me in the right direction would be appreciated.

Edit: Corrected dates in example table

[dwforest]

Something like this:

LOAD
    PersonID,
    Date(Floor(DateTime)) AS Date,
    if(EventID=2, if(Previsou(PersonID)=PersonID and Previous(EventID)=1,Previous(DateTime) - DateTime)) as Duration
;
Select
    PersonID,
    DateTime,
    EventID
From (yourtable)
Order By PersonID, DateTime, EventID;

[Channa]

how ru calculating Duration 8:25

[GS500]

I'm sorry, my example had error, all dates should have been 1/1/2019.  I have edited original post, please see example table again.  It is the total duration for the day 1/1/2019

7:05AM to 12:00PM = 4:55

1:00PM to 4:30PM = 3:30

4:55 + 3:30 = total duration of 8:25

[Channa · Accepted solution]

  If(PersonID = Previous(PersonID), Time(Previous (DateTime)-DateTime)) as Difference

[GS500]

Thanks, I got it to work with a combination of both of your responses. 

However, I have found that there is an additional issue with my data that I need to solve for.  Sometimes the Start and End Times happen at the same second, so when I do the order by it doesn't always sort the way I need it to, with Start Time before End Time.  

How can I either make sure the sort always has Start Time before End Time or just exclude those events that happen simultaneously?  

Thanks

[GS500]

I figured it out, just have to sort by DateTime Asc, EventID Desc.  

Thanks!