Subtracting times when multiple column value changes

General
Enthusiast

Subtracting times when multiple column value changes

Hi,

Ticket_ID  Name  Person_Indicator   time      Transaction Type  Transaction time when indicator is  A1
111 AAA 1 12:05:00 a1 5
111 AAA 1 12:05:00 a2
111 AAA 2 12:10:00 a1 51
112 bbb 1 13:01:00 a1 0
112 bbb 2 13:01:00 a1 61
113 ccc 1 14:00:00 a3
113 ccc 1 14:00:00 a2
113 ccc 2 14:02:00 a1 1
113 ccc 3 14:03:00 a1

I have this data set. Columns 1,2,3 and 4. I want to subtract time between consequent transactions only when I see transaction type "A1" . First it should check whether for same ID, indicator has changed. If it has, then I want subtraction between A1 of indicator 1 and A1 of Indicator 2. 

If indicator has not changed then look at ID and see if that has changed. If it has, then find the immediate next A1 and subtract the times. 

I want to display result in a new column as shown above (in minutes). 

Tags (1)