Calculate time spent in previous status at each transition to new status

Questions & Answers
1

Hi,

I face a situation I can’t wrap my head around.
We need to know if a transition took more than x days to happen. It isn’t about the total time spent in status, but the time spent in status for EACH occurrence.

As a picture is worth a thousand words :

SchemaEAZYBI
SchemaEAZYBI.PNG1234×240 8.51 KB

Let’s say the task spent :

  • 3 days in StatusA
  • 2 days in StatusB
  • 4 days in StatusA
  • 4 days in StatusB
  • 2 days in StatusA
    Ideally we would have ‘0’ the day of the first transition to StatusB, ‘1’ the day of the second transition to StatusB and ‘0’ the day of the third transition to StatusB.

The average time in status won’t cut it as (3 + 2 + 4) / 3 = 3.
Neither will a difference between dates in status as the same status may come up a undefined number of times.

I’d take any help you may provide.

Thanks.

Best regards !