SQL specific column update time in system-versioned temporal table-CodePudding

Is there any easy way to get information about the last update date of a selected column using system-versioned temporal table?

I have a table with columns A, B, C, each of them is updated randomly and separately, but I am interested in whether it is able to easily extract the date of the last update in column B.

I added a photo for the sake of simplicity, I need to extract information when there was the last change in the value in column A (in the photo I marked the last change in this column)

CodePudding user response：

Use the lag() window function to look for changes in B, summarize that set to find max(StartTime), and use that in a Where filter to select your latest record.

Select * From history.table
Where StartTime=(Select max(StartTime) from
    (   Select *,
           B<>lag(B) Over (Order By StartTime) as B_Changed
        From history.table
    )
    Where B_Changed
)

CodePudding user response：

I was able to find a simple solution that solves each case, below is the solution

SELECT TOP (1) * FROM (
    SELECT 
        ID,
        A,
        LAG(A) OVER(PARTITION BY ID ORDER BY StartTime) AS PreviousA,
        UserID,
        StartTime
    FROM dbo.Table FOR SYSTEM_TIME ALL 
)t
WHERE t.A <> t.PreviousA
ORDER BY t.StartTime desc

The query returns the last modification in the column, if there was no modification in the table or only another column was modified, it correctly returns an empty row informing that there were no changes. Maybe someone will need it in the future. Thank you for your help.