CODEWITHSUNDEEP
sqlsql-server
PyQL
PyQL

Reputation: 1830

SQL - Get value from next row based on specific condition

I have the following table:

| ID | Ref | Type |              LogTime |
|----|-----|------|----------------------|
|  1 |  AA |    1 | 2019-05-03 18:30:01  |
|  2 |  BB |    1 | 2019-05-03 19:42:02  |
|  3 |  AA |    3 | 2019-05-04 12:30:03  |
|  4 |  BB |    3 | 2019-05-05 19:42:04  |
|  5 |  AA |    1 | 2019-05-06 20:55:05  |

I would like to list all rows with Type = 1 and to include the value of LogTime where Ref values are equal and Type =3

Something like this:

| ID | Ref | Type |              LogTime |        LogTime_Type3 |
|----|-----|------|----------------------|----------------------|
|  1 |  AA |    1 | 2019-05-03 18:30:01  | 2019-05-04 12:30:03  |
|  2 |  BB |    1 | 2019-05-03 19:42:02  | 2019-05-05 19:42:04  |
|  5 |  AA |    1 | 2019-05-06 20:55:05  | NULL                 |

I tried to use LEAD(LogTime) Over.. but I were not able to specify the records with type=3

Can you please help.

Here is my SqlFiddle

Upvotes: 0

Views: 504

Answers (2)

JustFanOfYou
JustFanOfYou

Reputation: 21

By using subquery also we can achieve your expected output

SELECT DISTINCT
     O.ID,
     O.Ref,
     O.[Type],
     O.LogTime,
    (SELECT TOP 1 I.LogTime FROM LoginLogout I
     WHERE I.Ref = O.Ref
        AND I.[Type] = 3 
        AND I.LogTime > O.LogTime) AS LogTime_Type3 
FROM LoginLogout O
WHERE O.[Type] = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can just use join:

SELECT t.*, t3.LogTime as LogTime3
FROM Trans t LEFT JOIN
     Trans t3
     ON t3.ref = t.ref and t3.TYPE = '3'
WHERE t.TYPE = '1'
ORDER BY t.id;

One way to get the next time is to use OUTER APPLY:

SELECT t.*, t3.LogTime as LogTime3
FROM Trans t OUTER APPLY
     (SELECT TOP (1) t3.*
      FROM Trans t3
      WHERE t3.ref = t.ref and
            t3.LogTime > t.LogTime and
            t3.TYPE = '3'
      ORDER BY t.LogTime ASC
     ) t3
WHERE t.TYPE = '1'
ORDER BY t.id;

Or, using window functions, a cumulative minimum seems the most appropriate:

SELECT t.*
FROM (SELECT t.*,
             MIN(CASE WHEN t.TYPE = '3' THEN t.LogTime END) OVER (PARTITION BY ref ORDER BY LogTime DESC) as LogTime3
      FROM Trans t 
     ) t
WHERE t.TYPE = '1'
ORDER BY t.id;

Upvotes: 1

Related Questions