CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Tasos
Tasos

Reputation: 7577

Self join table to get a value from a previous row in SQL Server

I have a table like the following one in SQL Server 2008

id, userId, eventDate, typeId, data
1, 5, 01-01-2017, 5, 1234
2, 5, 01-01-2017, 9, 1255
3, 5, 02-01-2017, 4, 5325
4, 5, 03-01-2017, 30, null
5, 8, 10-01-2017, 9, 2551
6, 8, 15-01-2017, 2, 3252
7, 8, 20-01-2017, 3, 2155,
8, 8, 21-01-2017, 100, null

I have several rows there with two specific typeId values that are missing the last data column. Those typeIds are 30 and 100. What I try to do is to find the previous row with typeid = 9 and copy the last column to the one that is empty.

I thought something like that would work.

SELECT * FROM UserEvents ue1 WITH(NOLOCK)
  INNER JOIN UserEvents ue2 ON ue1.userid = ue2.userid and ue1.eventDate > ue2.eventDate and ue2.typeId = 9
  LEFT OUTER JOIN UserEvents ue3 ON (ue1.userid = ue3.userid AND ue2.eventDate > ue3.eventDate AND ue3.typeId = 9)
WHERE ue1.typeId = 100 and ue3.id IS NULL

However, this takes always the very first row of typeId = 9 and not the last one, just before the row with typeId = 100.

My desired outcome is (without the * *):

id, userId, eventDate, typeId, data
1, 5, 01-01-2017, 5, 1234
2, 5, 01-01-2017, 9, 1255
3, 5, 02-01-2017, 4, 5325
4, 5, 03-01-2017, 30, *1255*
5, 8, 10-01-2017, 9, 2551
6, 8, 15-01-2017, 2, 3252
7, 8, 20-01-2017, 3, 2155,
8, 8, 21-01-2017, 100, *2551*

Upvotes: 0

Views: 2136

Answers (1)

Lamak
Lamak

Reputation: 70638

You can use OUTER APPLY:

SELECT A.id,
       A.userid,
       A.eventDate,
       A.typeId,
       ISNULL(A.data,B.data) data
FROM dbo.UserEvents A
OUTER APPLY (SELECT TOP 1 data
             FROM dbo.UserEvents
             WHERE typeId = 9
             AND id < A.id
             ORDER BY id DESC) B;

Here is a demo of it.

And the results are:

╔════╦════════╦════════════╦════════╦══════╗
║ id ║ userid ║ eventDate  ║ typeId ║ data ║
╠════╬════════╬════════════╬════════╬══════╣
║  1 ║      5 ║ 01-01-2017 ║      5 ║ 1234 ║
║  2 ║      5 ║ 01-01-2017 ║      9 ║ 1255 ║
║  3 ║      5 ║ 02-01-2017 ║      4 ║ 5325 ║
║  4 ║      5 ║ 03-01-2017 ║     30 ║ 1255 ║
║  5 ║      8 ║ 10-01-2017 ║      9 ║ 2551 ║
║  6 ║      8 ║ 15-01-2017 ║      2 ║ 3252 ║
║  7 ║      8 ║ 20-01-2017 ║      3 ║ 2155 ║
║  8 ║      8 ║ 21-01-2017 ║    100 ║ 2551 ║
╚════╩════════╩════════════╩════════╩══════╝

Upvotes: 2

Related Questions