You can use windowing functions LAG (or LEAD) to get the data of the previous (or next) status for each id. That will let you compute the time elapsed in each stage. Then, compute the average time elapsed for each stage.

Here is an example of how to do that:

with input_data (id, dte, status) as (
SELECT 1, TO_DATE('1/1/18','MM/DD/YY'), 'requirement' FROM DUAL UNION ALL
SELECT 1, TO_DATE('1/8/18','MM/DD/YY'), 'analysis' FROM DUAL UNION ALL
SELECT 1, NULL, 'design' FROM DUAL UNION ALL
SELECT 1, TO_DATE('1/30/18','MM/DD/YY'), 'closed' FROM DUAL UNION ALL
SELECT 2, TO_DATE('2/1/18','MM/DD/YY'), 'requirement' FROM DUAL UNION ALL
SELECT 2, TO_DATE('2/18/18','MM/DD/YY'), 'closed' FROM DUAL UNION ALL
SELECT 3, TO_DATE('1/2/18','MM/DD/YY'), 'requirement' FROM DUAL UNION ALL
SELECT 3, TO_DATE('1/29/18','MM/DD/YY'), 'analysis' FROM DUAL UNION ALL
SELECT 3, NULL, 'accepted' FROM DUAL UNION ALL
SELECT 3, TO_DATE('2/5/18','MM/DD/YY'), 'closed' FROM DUAL ),
----- Solution begins here
data_with_elapsed_days as (
SELECT id.*, dte-nvl(lag(dte ignore nulls) over ( partition by id order by dte ), dte) elapsed
from input_data id)
SELECT status, avg(elapsed)
FROM data_with_elapsed_days d
group by status
order by decode(status,'requirement',1,'analysis',2,'design',3,'accepted',4,'closed',5,99);


+-------------+-------------------------------------------+
|   STATUS    |               AVG(ELAPSED)                |
+-------------+-------------------------------------------+
| requirement |                                         0 |
| analysis    |                                        17 |
| design      |                                           |
| accepted    |                                           |
| closed      | 15.33333333333333333333333333333333333333 |
+-------------+-------------------------------------------+

As I said in my comment, that logic computes the elapsed days as the time to the given status from the prior status. Since, "requirement" has no prior status, this logic will always show zero days spent in requirements. It would probably be better to compute the time from the given status to the next status. For "closed", there would be no next status. You could just leave that blank or use SYSDATE as the data of the next status. Here is an example of that:

with input_data (id, dte, status) as (
SELECT 1, TO_DATE('1/1/18','MM/DD/YY'), 'requirement' FROM DUAL UNION ALL
SELECT 1, TO_DATE('1/8/18','MM/DD/YY'), 'analysis' FROM DUAL UNION ALL
SELECT 1, NULL, 'design' FROM DUAL UNION ALL
SELECT 1, TO_DATE('1/30/18','MM/DD/YY'), 'closed' FROM DUAL UNION ALL
SELECT 2, TO_DATE('2/1/18','MM/DD/YY'), 'requirement' FROM DUAL UNION ALL
SELECT 2, TO_DATE('2/18/18','MM/DD/YY'), 'closed' FROM DUAL UNION ALL
SELECT 3, TO_DATE('1/2/18','MM/DD/YY'), 'requirement' FROM DUAL UNION ALL
SELECT 3, TO_DATE('1/29/18','MM/DD/YY'), 'analysis' FROM DUAL UNION ALL
SELECT 3, NULL, 'accepted' FROM DUAL UNION ALL
SELECT 3, TO_DATE('2/5/18','MM/DD/YY'), 'closed' FROM DUAL ),
----- Solution begins here
data_with_elapsed_days as (
SELECT id.*, nvl(lead(dte ignore nulls) over ( partition by id order by dte ), trunc(sysdate))-dte elapsed
from input_data id)
SELECT status, avg(elapsed)
FROM data_with_elapsed_days d
group by status
order by decode(status,'requirement',1,'analysis',2,'design',3,'accepted',4,'closed',5,99);



+-------------+------------------------------------------+
|   STATUS    |               AVG(ELAPSED)               |
+-------------+------------------------------------------+
| requirement |                                       17 |
| analysis    |                                     14.5 |
| design      |                                          |
| accepted    |                                          |
| closed      | 361.666666666666666666666666666666666667 |
+-------------+------------------------------------------+

Find time average time difference between stages, I would try the use of LEAD and AVG as follows: CREATE TABLE Tab1( AccountID INT, StageNum INT, StartTime DATETIME ) INSERT INTO  Time Difference. Time differences between time zones are simple to work out due to the Universal Time Coordinated (UTC). Standard times are shown as positive or negative deviations of UTC, that's why time differences are calculated by determining UTC and calculating each time zone from there.

I agree with @MatthewMcPeak. Your requirements seem a bit odd: you spend zero days of requirement stage but spend an average of 21 days on closed? Fnord.

This solution treats the presented date as the start date of the stage and calculates the difference between it and the start_date of the next phase.

with cte as (
    select status
           , lead(dd ignore nulls) over (partition by id order by dd) - dd as dt_diff
    from your_table)
select status, avg(dt_diff) as avg_ela
from cte
group by status
/

How to Calculate Stage to Stage Duration, Stage-to-stage duration measures the average amount of time it takes for For each Opportunity, you want to determine the date that the opp  Time Zone Converter – Time Difference Calculator Provides time zone conversions taking into account Daylight Saving Time (DST), local time zone and accepts present, past, or future dates. Start by adding a city to convert from:

If you wish to include all stages for each d and estimate the time spent in each (using linear interpolation) then you can create a sub-query with all the statuses and use a PARTITION OUTER JOIN to join them and then use LAG and LEAD to find the date range the status is in and interpolate between:

Oracle Setup:

CREATE TABLE data ( d, dt, status ) AS
SELECT 1, TO_DATE( '1/1/18', 'MM/DD/YY' ),  'requirement' FROM DUAL UNION ALL
SELECT 1, TO_DATE( '1/8/18', 'MM/DD/YY' ),  'analysis'    FROM DUAL UNION ALL
SELECT 1, NULL,                             'design'      FROM DUAL UNION ALL
SELECT 1, TO_DATE( '1/30/18', 'MM/DD/YY' ), 'closed'      FROM DUAL UNION ALL
SELECT 2, TO_DATE( '2/1/18', 'MM/DD/YY' ),  'requirement' FROM DUAL UNION ALL
SELECT 2, TO_DATE( '2/18/18', 'MM/DD/YY' ), 'closed'      FROM DUAL UNION ALL
SELECT 3, TO_DATE( '1/2/18', 'MM/DD/YY' ),  'requirement' FROM DUAL UNION ALL
SELECT 3, TO_DATE( '1/29/18', 'MM/DD/YY' ), 'analysis'    FROM DUAL UNION ALL
SELECT 3, NULL,                             'accepted'    FROM DUAL UNION ALL
SELECT 3, TO_DATE( '2/5/18', 'MM/DD/YY' ),  'closed'      FROM DUAL;

Query:

WITH statuses ( status, id ) AS (
  SELECT 'requirement', 1 FROM DUAL UNION ALL
  SELECT 'analysis',    2 FROM DUAL UNION ALL
  SELECT 'design',      3 FROM DUAL UNION ALL
  SELECT 'accepted',    4 FROM DUAL UNION ALL
  SELECT 'closed',      5 FROM DUAL
),
ranges ( d, dt, status, id, recent_dt, recent_id, next_dt, next_id ) AS (
  SELECT d.d,
         d.dt,
         s.status,
         s.id,
         NVL(
           d.dt,
           LAG( d.dt, 1 )
             IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id )
         ),
         NVL2(
           d.dt,
           s.id,
           LAG( CASE WHEN d.dt IS NOT NULL THEN s.id END, 1 )
             IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id )
         ),
         LEAD( d.dt, 1, d.dt )
           IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id ),
         LEAD( CASE WHEN d.dt IS NOT NULL THEN s.id END, 1, s.id + 1 )
           IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id )
  FROM   data d
         PARTITION BY ( d )
         RIGHT OUTER JOIN statuses s
         ON ( d.status = s.status )
)
SELECT d,
       dt,
       status,
       ( next_dt - recent_dt ) / (next_id - recent_id ) AS estimated_duration
FROM   ranges;

Output:

 D | DT        | STATUS      |                       ESTIMATED_DURATION
-: | :-------- | :---------- | ---------------------------------------:
 1 | 01-JAN-18 | requirement |                                        7
 1 | 08-JAN-18 | analysis    | 7.33333333333333333333333333333333333333
 1 | null      | design      | 7.33333333333333333333333333333333333333
 1 | null      | accepted    | 7.33333333333333333333333333333333333333
 1 | 30-JAN-18 | closed      |                                        0
 2 | 01-FEB-18 | requirement |                                     4.25
 2 | null      | analysis    |                                     4.25
 2 | null      | design      |                                     4.25
 2 | null      | accepted    |                                     4.25
 2 | 18-FEB-18 | closed      |                                        0
 3 | 02-JAN-18 | requirement |                                       27
 3 | 29-JAN-18 | analysis    | 2.33333333333333333333333333333333333333
 3 | null      | design      | 2.33333333333333333333333333333333333333
 3 | null      | accepted    | 2.33333333333333333333333333333333333333
 3 | 05-FEB-18 | closed      |                                        0

Query 2:

Then of you can easily change that to take the average for each status:

WITH statuses ( status, id ) AS (
  SELECT 'requirement', 1 FROM DUAL UNION ALL
  SELECT 'analysis',    2 FROM DUAL UNION ALL
  SELECT 'design',      3 FROM DUAL UNION ALL
  SELECT 'accepted',    4 FROM DUAL UNION ALL
  SELECT 'closed',      5 FROM DUAL
),
ranges ( d, dt, status, id, recent_dt, recent_id, next_dt, next_id ) AS (
  SELECT d.d,
         d.dt,
         s.status,
         s.id,
         NVL(
           d.dt,
           LAG( d.dt, 1 )
             IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id )
         ),
         NVL2(
           d.dt,
           s.id,
           LAG( CASE WHEN d.dt IS NOT NULL THEN s.id END, 1 )
             IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id )
         ),
         LEAD( d.dt, 1, d.dt )
           IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id ),
         LEAD( CASE WHEN d.dt IS NOT NULL THEN s.id END, 1, s.id + 1 )
           IGNORE NULLS OVER ( PARTITION BY d.d ORDER BY s.id )
  FROM   data d
         PARTITION BY ( d )
         RIGHT OUTER JOIN statuses s
         ON ( d.status = s.status )
)
SELECT status,
       AVG( ( next_dt - recent_dt ) / (next_id - recent_id ) ) AS estimated_duration
FROM   ranges
GROUP BY status, id
ORDER BY id;

Results:

STATUS      |                       ESTIMATED_DURATION
:---------- | ---------------------------------------:
requirement |                                    12.75
analysis    | 4.63888888888888888888888888888888888889
design      | 4.63888888888888888888888888888888888889
accepted    | 4.63888888888888888888888888888888888889
closed      |                                        0

db<>fiddle here

Data Mining and Big Data: First International Conference, DMBD , First, we extract all important extreme points of the time series T. The result of this To be able to calculate distance between them, we bring them to the average length by After segmentation stage, we calculate anomaly factor for each candidate threshold e2, anomaly factor threshold a and the length difference width r. Figure 1. Format cells to get average time. Figure 2. Set Time format to calculate average time. To get the average time we should follow the steps: In the cell E2 insert function =AVERAGE(B3:B6) Press Enter . The average formula has a simple syntax, we just have to select the cell range where we want to calculate the average.

Communications and Multimedia Security: 11th IFIP TC 6/TC 11 , 4.2 Phase 2: Routes Analysis and Selection At this stage, all routes between PI,​Gi and PE,Gi and PE,Gi if he use each route, and compare it to the actual time difference between PI,Gi Then, we calculate the mean (average) of the times  Time Calculator. This script is designed to accumulate amounts of time entered as data, display it as a total and give a high, low and mean between the two, and an average of all the entries (12 possible).

How do I calculate the average length of the cell cycle?, Thus, the cell cycle is 0.5/0.00012 = 4167 hours in length, on average, which is nearly (P+M+A+T) — the sum of all cells in phase as prophase, metaphase, this difference in measurement undermines my confidence in the acquired values​. First-time moms were reported to generally experience 6–12 hours in the first stage of labor (from the time they are dilated 4 centimeters) with an average length of 7.7 hours. Differences in the Length of Time

Microelectronic Test Structures for CMOS Technology, Design and test of a high-speed macro template for measuring time delays with an average of pull-up (PU) and pull-down (PD) delays of all the stages in the ring determination of differences in signal propagation delays through two DUTs  The Time Duration Calculator will calculate the time that has elapsed/difference between two dates with time.

Comments

• Maybe we can help If you tell us how calculate the average
• its just one table as described above. Just need expert help on to write a query that will calc the time diff based on available stages. Average would be total Id's AVG time spent on a given stage
• So what is the rule for handling stages with no dates?
• Show us what result you expect for that data, and what have you tried.
• Your requirements seem a little off -- you will always show zero days spend in the "requirements" status? Wouldn't it be better to count the days to the next status rather than days since the prior status?
• Thank you sir. Very helpful
• I thought your query and the query proposed by APC above should return similar results, but i do get quite a different counts.. Can you please shed some light here..what is the difference in your both queries
• The main difference I see is that my version uses SYSDATE to compute the time spent in the most recent status (usually "closed"). Other than that, I think they're basically the same.
• Do u see any issue with this to calculate the %age of each stage COUNT(NVL(status, 0))/ round(avg(elapsed)) This doesn't seem work
• can u plz me help on how to derive the right %age of each stage for the above solution?
• @MatthewMcPeak - nope, it's a legacy issue which I'd already edited :)
• Thank you for the logic