Migrate time when column was last changed from history table?

Question

I would like to know when UserId was changed to the current value.

Say we got a table Foo:

Foo

Id | UserId
---+-------
1  | 1
2  | 2

Now I would need to be able to execute a query like:

SELECT UserId, UserIdModifiedAt FROM Foo

Luckily I have logged all the changes in history to table FooHistory:

FooHistory

Id | FooId | UserId | FooModifiedAt
---|-------+--------+---------------
1  | 1     | NULL   | 1.1.2019 02:00
2  | 1     | 2      | 1.1.2019 02:01
3  | 1     | 1      | 1.1.2019 02:02
4  | 1     | 1      | 1.1.2019 02:03
5  | 2     | 1      | 1.1.2019 02:04
6  | 2     | 2      | 1.1.2019 02:05
7  | 2     | 2      | 1.1.2019 02:06

So all the data we need is available (above the user of Foo #1 was last modified 02:02 and the user of Foo #2 02:05). We will add a new column UserIdModifiedAt to Foo

Foo v2

Id | UserId | UserIdModifiedAt
---+--------|-----------------
1  | 1      | NULL
2  | 2      | NULL

... and set its values using a trigger. Fine. But how to migrate the history? What script would fill UserIdModifiedAt for us?

See an example of the table structure:

DROP TABLE IF EXISTS [Foo]
DROP TABLE IF EXISTS [FooHistory]
CREATE TABLE [Foo]
(
    [Id] INT NOT NULL CONSTRAINT [PK_Foo] PRIMARY KEY,
    [UserId] INT,
    [UserIdModifiedAt] DATETIME2 -- Automatically updated based on a trigger
)
CREATE TABLE [FooHistory]
(
    [Id] INT IDENTITY NOT NULL CONSTRAINT [PK_FooHistory] PRIMARY KEY,
    [FooId] INT,
    [UserId] INT,
    [FooModifiedAt] DATETIME2 NOT NULL CONSTRAINT [DF_FooHistory_FooModifiedAt] DEFAULT (sysutcdatetime())
)
GO
CREATE TRIGGER [trgFoo]
    ON [dbo].[Foo]
    AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT [UserId] FROM inserted EXCEPT SELECT [UserId] FROM deleted)
    BEGIN
        UPDATE [Foo] SET [UserIdModifiedAt] = SYSUTCDATETIME() FROM [inserted] WHERE [Foo].[Id] = [inserted].[Id]
    END
    INSERT INTO [FooHistory] ([FooId], [UserId])
    SELECT [Id], [UserId] FROM inserted
END
GO
/* Test data */
INSERT INTO [Foo] ([Id], [UserId]) VALUES (1, NULL)
WAITFOR DELAY '00:00:00.010'

UPDATE [Foo] SET [UserId] = NULL
WAITFOR DELAY '00:00:00.010'

UPDATE [Foo] SET [UserId] = 1
WAITFOR DELAY '00:00:00.010'

UPDATE [Foo] SET [UserId] = 1
WAITFOR DELAY '00:00:00.010'

SELECT * FROM [Foo]
SELECT * FROM [FooHistory]

Related question: Select first row in each GROUP BY group?.

Answer 1

If I understand your question right, it looks like you have already answered it yourself by the way you created your trigger on dbo.Foo.

It looks like the UserIdModifiedAt is modified the first time the UserId changes and not modified when it does not change, in which case your answer is simply dbo.Foo.UserIdModifiedAt.

If you did not mean to write this trigger like that, I think it is possible to retrieve that value from FooHistory but it's much more complicated.

The code below might do what I think you were asking for

;WITH FooHistoryRanked
AS (
  SELECT FH.Id, FH.FooId, FH.FooModifiedAt, FH.UserId
        , RankedASC  = ROW_NUMBER() OVER(PARTITION BY FH.FooId ORDER BY FooModifiedAt ASC) -- 1 = first change to that Foo record
    FROM [FooHistory] FH 
)
,Matches AS
(
SELECT FHR1.*
    , PreviousUserId        = FHR2.UserId
    , PreviousFooModifiedAt = FHR2.FooModifiedAt 
    , PreviousHistoryId     = FHR2.Id
  FROM FooHistoryRanked FHR1
  -- join on Foo filters on current value
  INNER JOIN [Foo] F ON F.Id = FHR1.FooId
                    AND ( FHR1.UserId = F.UserId 
                        OR (FHR1.UserId IS NULL AND F.UserId IS NULL)
                        )
  -- Find preceding changes to a different value
  LEFT JOIN FooHistoryRanked FHR2 ON FHR2.FooId      = FHR1.FooId
                                 AND FHR2.RankedASC  = FHR1.RankedASC - 1 -- previous change 
                                 AND ( FHR2.UserId     <> FHR1.UserId
                                    OR ( FHR2.UserId IS NULL AND FHR1.UserId IS NOT NULL )
                                    OR ( FHR2.UserId IS NOT NULL AND FHR1.UserId IS NULL )
                                     )
)
,MatchesRanked AS
(
-- select the modifications that had a different value before OR that are the only modification
SELECT *, MatchRanked = ROW_NUMBER() OVER(PARTITION BY FooId ORDER BY Id DESC)
  FROM Matches
  WHERE RankedASC = 1 OR PreviousFooModifiedAt IS NOT NULL
)
SELECT * 
  FROM MatchesRanked
  WHERE MatchRanked = 1 -- just get the last qualifying record
  ORDER BY FooId, FooModifiedAt DESC, UserId;

PS: 1) Performance could be a problem if these tables were big... 2) you could probably use LAG instead of the LEFT JOIN but I am just used to do things this way...