Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If(PersonID = Previous(PersonID), Time(Previous (DateTime)-DateTime)) as Difference
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;
how ru calculating Duration 8:25
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
If(PersonID = Previous(PersonID), Time(Previous (DateTime)-DateTime)) as Difference
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
I figured it out, just have to sort by DateTime Asc, EventID Desc.
Thanks!