Calculating SLAs/ turnaround times from single table

[JPrince_22]

Hi all

I am trying to put together some MI on SLAs based on the time between certain steps. The difficulty is that account, step and start time are all columns in the same table. Can anyone advise how I can summarise turnaround times between certain steps on a case by case basis? 

Sample data and output required shown below. 

It would also be great to base this only on working hours, and get some % hit and miss based on expected turnaround times but not sure if this is a step too far!

Thanks in advance 

Sample Data
Case	Step	DateTime
Case 1	Step A	01/04/2020 08:00
Case 1	Step B	01/04/2020 09:45
Case 1	Step C	01/04/2020 11:45
Case 1	Step D	02/04/2020 15:00
Case 1	Step E	02/04/2020 16:40
Case 1	Step F	03/04/2020 09:00
Case 2	Step A	01/04/2020 10:24
Case 2	Step B	01/04/2020 12:09
Case 2	Step C	01/04/2020 14:09
Case 2	Step D	02/04/2020 17:24
Case 2	Step E	02/04/2020 19:04
Case 2	Step F	03/04/2020 11:24
Case 3	Step A	01/04/2020 12:48
Case 3	Step B	01/04/2020 14:33
Case 3	Step C	01/04/2020 16:33
Case 3	Step D	02/04/2020 19:48
Case 3	Step E	02/04/2020 21:28
Case 3	Step F	03/04/2020 13:48
Case 4	Step A	01/04/2020 15:12
Case 4	Step B	01/04/2020 16:57
Case 4	Step C	01/04/2020 18:57
Case 4	Step D	02/04/2020 22:12
Case 4	Step E	02/04/2020 23:52
Case 4	Step F	03/04/2020 16:12
Case 5	Step A	01/04/2020 17:36
Case 5	Step B	01/04/2020 19:21
Case 5	Step C	01/04/2020 21:21
Case 5	Step D	03/04/2020 00:36
Case 5	Step E	03/04/2020 02:16
Case 5	Step F	03/04/2020 18:36
Case 6	Step A	01/04/2020 20:00
Case 6	Step B	01/04/2020 21:45
Case 6	Step C	01/04/2020 23:45
Case 6	Step D	03/04/2020 03:00
Case 6	Step E	03/04/2020 04:40
Case 6	Step F	03/04/2020 21:00

Sample Output
	Step A-B	Step C-D	Step D-E	Step E-F
Case 1	1:45	3:15	1:40	16:20
Case 2	1:45	3:15	1:40	16:20
Case 3	1:45	3:15	1:40	16:20
Case 4	1:45	3:15	1:40	16:20
Case 5	1:45	3:15	1:40	16:20
Case 6	1:45	3:15	1:40	16:20

[tex628]

Hi @JPrince_22 ,

Is the maximum amount of steps defined or can there occur cases in the future with more steps than there is now? 

Br,
J

[JPrince_22]

Hi @tex628 

There are 48 steps in total however only 7 of them are of interest. New cases continuously cycle through the steps so new cases will be added all the time, but the steps themselves will remain stable. 

Thanks 

[tex628 · Accepted solution]

Use the pivot function with Case and Stage highlighted:



Then you calculate the differences in time with new columns! 

Br,
J

[JPrince_22]

@tex628  that has done it - thank you!!!