Capturing SQL row changes (multiple columns) using existing flag column

Question

I have a vehicle registration table laid out as follows:

CREATE TABLE [registrations]
(
    [VIN] int,
    [Name] nvarchar(255),
    [Action] nvarchar(255),
    [Date] DateTime
);

INSERT INTO registrations VALUES 
(1, 'John', 'Add', '2017-01-01'),
(1, '', 'Remove', '2017-01-16'),
(1, 'Fred', 'Add', '2017-02-25'),
(1, 'Tom', 'Change', '2017-06-08'),
(2, 'Nancy', 'Add', '2018-01-15'),
(2, 'Jim', 'Change', '2018-02-05'),
(3, 'Clarence', 'Add', '2018-02-10'),
(3, 'Darlene', 'Change', '2018-02-11'),
(4, 'Charlotte', 'Add', '2018-02-11'),
(5, 'Ferris', 'Add', '2018-02-12'),
(5, 'Dante', 'Change', '2018-02-12'),
(5, 'Susan', 'Change', '2018-02-13');

I'm trying to capture only actions Change, but I want the Name value before and after the change as different columns in the same row.

So a query on the above would return something like:

VIN    OldName    NewName    Date
1      Fred       Tom        2017-02-25
2      Nancy      Jim        2018-02-05
3      Clarence   Darlene    2018-02-11
5      Dante      Susan      2018-02-13

Note: I'm excluding VINs with only 1 transaction, and a Change can come from more than one status (e.g. Add).

Other answers (1) show a great way to do this, but by monitoring a single column changing. I have multiple columns changing (Name is just an example of one), with a flag column (Action) indicating a change was made on any of the rows.

I can get it into a log-looking usable format using guidance from another answer (2):

WITH T AS
(
    SELECT *, COUNT(*) OVER (PARTITION BY VIN) as Cnt
    FROM [registrations]
)
SELECT [VIN], [Action], [Name], [Date]
FROM T
WHERE Cnt > 1
order by [VIN], [Date] desc

But in terms of PIVOTing, I'm not sure how to factor in the fact that there could be 2 or there could be 10 entries per VIN. I only would really want the most recent two (the most recent being the Change).

I'm able to do the extra processing on the above query at application level, but I'd like to know how to do this in SQL.

RDBMS: MS SQL Server 2005

SQLFiddle: http://sqlfiddle.com/#!18/f579f/1

Gordon Linoff · Accepted Answer · 2018-02-27T15:16:23.847

2

This is much easier in SQL Server 2012 using LAG(). In SQL Server 2005 you can use a correlated subquery or APPLY:

SELECT r.VIN, r.Name as OldName, rprev.Name as NewName, r.Date
FROM registrations r OUTER APPLY
     (SELECT TOP (1) r2.*
      FROM registrations r2
      WHERE r2.VIN = r.VIN AND r2.[Date] < r.[Date]
      ORDER BY r2.[Date] DESC
     ) rprev
WHERE r.action = 'Change'
ORDER BY r.[VIN], r.[Date] DESC;

Here is the SQL Fiddle.

EDIT:

Oh, I see, you want only one row per VIN. Here is one way:

SELECT r.*, rprev.Name
FROM (SELECT r.*,
             ROW_NUMBER() OVER (PARTITION BY VIN ORDER BY [Date] DESC) as seqnum
      FROM registrations r
      WHERE r.action = 'Change'
     ) r OUTER APPLY
     (SELECT TOP (1) r2.*
      FROM registrations r2
      WHERE r2.VIN = r.VIN AND r2.[Date] < r.[Date]
      ORDER BY r2.[Date] DESC
     ) rprev
WHERE seqnum = 1;

Here is the SQL Fiddle for this version.

edited Feb 27 '18 at 15:16

answered Feb 27 '18 at 14:38

Gordon Linoff

1,242,037
58
646
786

Wow, thank you. How would you factor in only wanting the most recent change when the status is from one change to another? (e.g. to only see the change from dante to susan, but not ferris to dante) – Jay M Feb 27 '18 at 14:49
@JayM . . . Fair question. That seems to be what you want in the original question. I missed that part originally. – Gordon Linoff Feb 27 '18 at 15:17
beautiful, thanks for your help. also, thank you for opening my eyes to `Apply`. That's a game changer – Jay M Feb 27 '18 at 19:14

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

with x as
(
    select VIN, Name, Action, [Date],
           row_number() over (partition by VIN order by VIN, [date]) rn,
           count(*) over (partition by VIN) cnt
    from   registrations
)
select x.VIN, 
       (select x1.Name
        from   x x1
        where  x1.VIN = x.VIN
        and    x1.rn = x.rn - 1) as OldName,
       x.Name as NewName, 
       [Date]
from   x
where  x.[Action] = 'Change'
and    x.cnt > 1
and    x.rn = (select top(1) x2.rn from x x2 where x2.VIN = x.Vin order by x2.VIN, x2.rn desc)

GO

VIN | OldName  | NewName | Date               
--: | :------- | :------ | :------------------
  1 | Fred     | Tom     | 08/06/2017 00:00:00
  2 | Nancy    | Jim     | 05/02/2018 00:00:00
  3 | Clarence | Darlene | 11/02/2018 00:00:00
  5 | Dante    | Susan   | 13/02/2018 00:00:00

dbfiddle here

Capturing SQL row changes (multiple columns) using existing flag column

2 Answers2