CODEWITHSUNDEEP
sqlsql-server
Kelvinlin
Kelvinlin

Reputation: 25

Finding a difference in time in minutes for values in the same column

What I want to do here is to find the time difference between StatusID = 'Processed' and StatusID = 'NEW' according to its owner ID. and after getting the difference for each owner ID, I want to find the maximum, minimum and average time difference. This is the sample data for it.

OWNER_ID CREATED_TIME STATUS_ID
1 2020-07-16 08:29:29.000 NEW
1 2022-02-21 04:38:01.000 PROCESSED
3 2022-02-28 14:24:28.000 1
3 2022-02-28 14:27:32.000 CONVERTED
4 2022-02-28 14:33:06.000 NEW
4 2022-02-28 14:33:19.000 IN_PROCESS
5 2022-03-01 12:01:48.000 NEW
5 2022-03-01 12:02:00.000 IN_PROCESS

I have tried out this code to get the time difference but my code is not working.

SELECT 
    OWNER_ID, 
    DATEDIFF(SECOND, (SELECT CREATED_TIME
                      FROM table
                      WHERE STATUS_ID = 'IN_PROCESS'),
                     (SELECT CREATED_TIME
                      FROM table
                      WHERE STATUS_ID = 'NEW'))
FROM 
    table
GROUP BY 
    OWNER_ID

The desired output is in this format and after getting the result, I want to find the maximum, minimum and average time difference.

OWNER_ID TIME_DIFFERENCE(in mins)
1 500
3 800
4 1300

Upvotes: 0

Views: 107

Answers (1)

Squirrel
Squirrel

Reputation: 24783

Use a CASE expression with aggregate

SELECT OWNER_ID, 
       DATEDIFF(SECOND,
                MIN (CASE WHEN STATUS_ID = 'NEW' THEN CREATED_TIME END),
                MAX (CASE WHEN STATUS_ID = 'IN_PROCESS' THEN CREATED_TIME END) )
FROM   table
GROUP BY OWNER_ID

Upvotes: 1

Related Questions