# 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 <> 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<>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 > t1.id
ORDER BY id) t2
-- WHERE id < 100
)
SELECT
SUM(days) / COUNT(*) Average
FROM
ct
WHERE
days <> 0;
``````

db<>fiddle here

• Thanks for your reply..I am using SQL Server 2008 though so this won't work for me.
– SP1
Mar 11, 2019 at 21:34
• Is there any IDENTITY column in your table? Mar 11, 2019 at 21:37
• Yeah there is an ID column
– SP1
Mar 11, 2019 at 21:38
• Thank you so much..say I want to limit the data to maybe say id < 100 where shall I add that
– SP1
Mar 11, 2019 at 21:54
• Within the CTE part. Mar 11, 2019 at 21:55