Hi gurus,
Hopefully you can give me a hand.
I want to subtract the time between statuses (rows) within a same set.
For the attached example:
AllocationCode | Time | status | Allocation_Duration |
23 | 2/08/2020 6:30 | 1 | 0 |
23 | 2/08/2020 6:37 | 50 | 0.125833333 |
23 | 2/08/2020 6:56 | 60 | 0.315555556 |
23 | 2/08/2020 7:10 | 63 | 0.225277778 |
23 | 2/08/2020 7:20 | 67 | 0.166666667 |
23 | 2/08/2020 7:32 | 70 | 0.200555556 |
23 | 2/08/2020 7:44 | 80 | 0.205555555 |
23 | 2/08/2020 8:32 | 85 | 0.801944445 |
23 | 2/08/2020 8:41 | 90 | 0.151944445 |
23 | 2/08/2020 9:26 | 99999 | 0.747222222 |
25 | 2/08/2020 8:00 | 1 | 0 |
25 | 2/08/2020 9:03 | 50 | 1.056666667 |
25 | 2/08/2020 9:40 | 60 | 0.6225 |
25 | 2/08/2020 9:59 | 63 | 0.316944444 |
25 | 2/08/2020 10:14 | 67 | 0.246388889 |
25 | 2/08/2020 10:24 | 70 | 0.164166667 |
25 | 2/08/2020 11:09 | 80 | 0.7525 |
25 | 2/08/2020 11:37 | 85 | 0.470277778 |
25 | 2/08/2020 11:43 | 90 | 0.092777778 |
25 | 2/08/2020 12:57 | 99999 | 1.243333333 |
I need to subtract each time row within the same Allocation Code. My desire result is Allocation Duration column.
Status 1 is the start of the set, and 99999 is the end of the set. The data set will be sorted at status level and Allocation code.
Thanks in advance,
Cheers.
MrD
Solved! Go to Solution.
Thanks wwatson,
Very clean and easy solution.!!!
Brilliant
🙂