CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SP1
SP1

Reputation: 1202

Calculate time difference between adjacent rows

I have the below table format

 ID   Status   Date
    16  In Review   2017-07-03 08:23:11.000
    16  Approved    2017-07-03 08:23:20.000
    16  Approved    2017-07-11 10:34:27.000

I am trying to get the time difference between In Review and Approved which should be 9 seconds..

This is a continuation of my previous Question where Gordon provided me an answer.

Here is the code for it

select avg(avg_bid_diff)
from (select bid, avg(diff*1.0) as avg_bid_diff
      from (select bid, appid,
                   datediff(second, min(starttime), max(statustime)) as diff
            from t
            where appstatus in ('In Review', 'Approved')
            group by bid, appid
            having count(*) = 2
           ) ba
      group by bid
     ) b;

The issue is that when it considers min and max I get the time difference between the 1st and the 3rd row and when only 2 status are there but what I need is the time Difference between the First In Review and the next Approved

Can someone please tell me how I should change the query to get the next Approved time difference.

Thanks

Upvotes: 0

Views: 126

Answers (1)

Emil
Emil

Reputation: 336

It was bit tricky, but so far it seems to work good. The only limitation is that for each unique (BidID, AppID) pair there is NO or ONE record with status 'In Review'.

I have used whole different approach with chained CTE:

DECLARE @table TABLE
(
    BidID int,
    AppID int,
    AppStatus nvarchar(20),
    StatusTime DATETIME2
);

INSERT INTO @table (BidID, AppID, AppStatus, StatusTime)
VALUES
(1, 1, 'In Review', '2019-01-02 12:00:00'),
(1, 1, 'Approved', '2019-01-02 13:00:00'),
(1, 1, 'Approved', '2019-01-02 13:30:00'),
(1, 2, 'In Review', '2019-01-04 13:00:00'),
(1, 2, 'Approved', '2019-01-04 15:00:00'),
(2, 2, 'Approved', '2019-01-07 14:30:00'),
(2, 2, 'In Review', '2019-01-07 15:00:00'),
(2, 2, 'Approved', '2019-01-07 16:00:00'),
(3, 1, 'In Review', '2019-01-09 13:00:00'),
(4, 1, 'Approved', '2019-01-09 13:00:00');

;WITH OrderedRecords(BidID, AppID, AppStatus, StatusTime, [Order])
AS (

    SELECT BidID
    ,AppID
    ,AppStatus
    ,StatusTime
    , [Order] = ROW_NUMBER() OVER(PARTITION BY BidID, AppID ORDER BY StatusTime)
    FROM @table
),
BidAverage(BidID, AveragePerBid)
AS (
    SELECT OR1.BidID, AVG(CAST(DATEDIFF(HOUR, OR1.StatusTime, OR2.StatusTime) AS DECIMAL)) AS AveragePerBid
    FROM OrderedRecords OR1
    INNER JOIN OrderedRecords OR2
        ON OR1.BidID = OR2.BidID AND OR1.AppID = OR2.AppID AND OR2.AppStatus = 'Approved' AND OR2.[Order] = OR1.[Order] + 1
    WHERE OR1.AppStatus = 'In Review'
    GROUP BY OR1.BidID
)
SELECT AVG(AveragePerBid) AS AveragePerTable
FROM BidAverage


AveragePerTable
---------------------------------------
1.250000

Here I try to explain the important steps:

  1. Number records per BidID,AppID,StatusTime - this just numbers (orders) the records based on time the status appears and the numbering restarts per each (BidID, AppID) pair.

    ;WITH OrderedRecords(BidID, AppID, AppStatus, StatusTime, [Order])
AS (

SELECT BidID
,AppID
,AppStatus
,StatusTime
, [Order] = ROW_NUMBER() OVER(PARTITION BY BidID, AppID ORDER BY StatusTime)
FROM @table
)
SELECT * FROM OrderedRecords

BidID       AppID       AppStatus            StatusTime                  Order
----------- ----------- -------------------- --------------------------- --------------------
1           1           In Review            2019-01-02 12:00:00.0000000 1
1           1           Approved             2019-01-02 13:00:00.0000000 2
1           1           Approved             2019-01-02 13:30:00.0000000 3
1           2           In Review            2019-01-04 13:00:00.0000000 1
1           2           Approved             2019-01-04 15:00:00.0000000 2
2           2           Approved             2019-01-07 14:30:00.0000000 1
2           2           In Review            2019-01-07 15:00:00.0000000 2
2           2           Approved             2019-01-07 16:00:00.0000000 3
3           1           In Review            2019-01-09 13:00:00.0000000 1
4           1           Approved             2019-01-09 13:00:00.0000000 1

  2. Pick the right pairs of 'In Review', 'Approved' records

Basically the inner select of CTE BidAverage selects all 'InReview' records from the 'OrderedRecords' CTE and by joining the same table tries to find 'Approved' records with same BidID, AppID and Order which is 1 higher than the 'InReview' Order (that's the trick to get adjacent 'In reviw' and 'Approved'. The INNER join cuts off 'In review' records without 'Approved' and vice versa.

;WITH OrderedRecords(BidID, AppID, AppStatus, StatusTime, [Order])
AS (

    SELECT BidID
    ,AppID
    ,AppStatus
    ,StatusTime
    , [Order] = ROW_NUMBER() OVER(PARTITION BY BidID, AppID ORDER BY StatusTime)
    FROM @table
)
SELECT OR1.BidID, OR1.AppID, OR1.AppStatus, OR1.StatusTime, OR1.[Order], OR2.AppStatus, OR2.StatusTime, OR2.[Order]
FROM OrderedRecords OR1
INNER JOIN OrderedRecords OR2
    ON OR1.BidID = OR2.BidID AND OR1.AppID = OR2.AppID AND OR2.AppStatus = 'Approved' AND OR2.[Order] = OR1.[Order] + 1
WHERE OR1.AppStatus = 'In Review'


BidID       AppID       AppStatus            StatusTime                  Order                AppStatus            StatusTime                  Order
----------- ----------- -------------------- --------------------------- -------------------- -------------------- --------------------------- --------------------
1           1           In Review            2019-01-02 12:00:00.0000000 1                    Approved             2019-01-02 13:00:00.0000000 2
1           2           In Review            2019-01-04 13:00:00.0000000 1                    Approved             2019-01-04 15:00:00.0000000 2
2           2           In Review            2019-01-07 15:00:00.0000000 2                    Approved             2019-01-07 16:00:00.0000000 3

When you put all of this together (final script at the top of this answer), you get what you need. As I stated above the limitation here is that there is only one 'In Review' record per BidID and AppID. (But multiple 'Approved' can be present and 'In review' does not need to be the first one by time)

Upvotes: 1

Related Questions