Lifecycle in date

[#1]

I have an ID , Stage and the date . I would like to calculate the lifecycle of an ID as it goes through the stages A, B, C,D and again back to A.

1) How much time does  it take to go from stage A again to stage A for an Id , (data sorted by id and date).

ID  Date                              Stage                   

19 30/7/2014 4:19:03 PM  A

19 1/8/2014 12:04:01 PM  A       Difference of dates row 2 – row1

19 3/8/2014 9:31:22 AM    A       !Difference of dates row 3 – row 2

….

2) the same e.g. from stage A to B.

Please note that the lifecycle could include different number of stages. What is sure is they start from A and finish to A. (lifecycle)

Calculation of date differences between stage A to stage A  and finally the average difference.

Thanks for your ideas

[swuehl · Accepted solution]

Not sure if I got everything correct, please find attached.

[#3]

Hello Swuehl,

Very good idea !

Now you have calculated :

A-A

A-B

A-C

A-D

A-E

A-F

I would like to ask you how could we get also the datediff for the cases also :

B-A

B-B

B-C

B-D

B-E

...

C-A

C-B

..

D-A

D-B

...

E-A

E-B

....

Thanks in advance for your ideas

[swuehl]

B-A would calculate the difference between B to the end of the lifecycle, right?

Similar C-A,D-A,E-A

All other combinations are not necessarily belonging to the same lifecycle right? Do you want to calculate the difference then across lifecycles?

[#5]

Yes.

[#6]

To be more precise :

To have also :

1) the intermediate cycles :

B-B , C-C , ....

2) One stage difference : B-C,C-D,D-E ,E-F ..