Calculate Average between two dates of different rows I have the below table

BIDID AppID AppStatus   Time
23390   16  In Review   2017-07-03 
23390   16  Approved    2017-09-03 
23390   16  In Review   2017-10-11 
23390   16  Approved    2017-12-11 
23390   16  Approved    2017-14-11 


I want to calculate the time difference according to the following logic

First find the In Review status and then get the next approved status and then calculate the time difference. Then get the next In Review status and then next Approved and calculate the time difference. Then get an average.

So in this case what I am looking for is 

Say 

2017-07-03  - 2017-09-03  - 2 days
    2017-10-11  - 2017-12-11   -2 days


Last Approved is ignored because there was no matching In review before it

Then get total average (2 + 2) / 2 = 2


Can someone please tell me how I can achieve this.

Thanks
WITH ct AS
(
SELECT
    CASE WHEN AppStatus = 'Approved'
              AND LAG(AppStatus) 
                  OVER (PARTITION BY BIDID, AppID ORDER BY [Time]) = 'In Review'
         THEN 
             DATEDIFF (day, LAG([Time]) OVER (PARTITION BY BIDID, AppId 
                                             ORDER BY [Time]), [Time])
         ELSE
             0
    END days
FROM
    t
)
SELECT
    SUM(days) / COUNT(*) Average
FROM
    ct
WHERE
    days &lt;&gt; 0;


The CTE part calculates DATEDIFF with the previous row, every time it finds an 'Approved' AppStatus after an 'In review'AppStatus.



BIDID | AppID | AppStatus | Time                | days
----: | ----: | :-------- | :------------------ | ---:
23390 |    16 | In Review | 07/03/2017 00:00:00 |    0
23390 |    16 | Approved  | 09/03/2017 00:00:00 |    2
23390 |    16 | In Review | 10/11/2017 00:00:00 |    0
23390 |    16 | Approved  | 12/11/2017 00:00:00 |    2
23390 |    16 | Approved  | 14/11/2017 00:00:00 |    0



The simply sums the calculated days and divide by the rows that has calculated days.



| Average |
| ------: |
|       2 |



db&lt;>fiddle here

UPDATE

Due you are on 2008 and as per comments you pointed out there is an IDENTITY column, you can simulate LAG/LEAD function using an APPLY join with the next ID.

WITH ct AS
(
SELECT
    CASE WHEN t1.AppStatus = 'In Review' AND t2.AppStatus = 'Approved'
         THEN DATEDIFF(day, t1.[Time], t2.[Time])
         ELSE 0
    END as Days
FROM
    t t1
CROSS APPLY(SELECT TOP 1 * 
            FROM t
            WHERE id &gt; t1.id
            ORDER BY id) t2
-- WHERE id &lt; 100
)
SELECT
    SUM(days) / COUNT(*) Average
FROM
    ct
WHERE
    days &lt;&gt; 0;


db&lt;>fiddle here