0

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

1 Answer 1

2
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

8
  • 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?
    – McNets
    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.
    – McNets
    Mar 11, 2019 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.