How to identify the latest record for the given set of data based on ChangedFrom, ChangedTo and ModifiedOn Combination from the data

Question

Hi Everyone, We have two tables Employees and EmployeeAudit Table in Azure Synapse. When a new record is inserted in employees table, Audit table captures the record with PreviousValue as NULL and NewValue as currentvalue for the employee to be inserted in employees table with a current_timestamp as modifiedon. Similarly, when an existing employee has updates the PreviousValue will be value before update and NewValue would be current updated value of the employee in the employees table. Refer the sample data

Code: CREATE TABLE [dbo].[EmployeeAudit]

(              

[Employee Id] int,              

[Attribute Name] varchar(100),              

[PreviousValue] varchar(100),

[NewValue] varchar(100),              

[ModifiedOn] datetime

)

INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'Employee Name',NULL,'Mike',CAST('2/14/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'Employee Name','Mike ','Mike F',CAST('2/14/2024 12:01:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'City','New York','London',CAST('2/14/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'City','London','Dubai',CAST('2/14/2024 12:02:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'City','Dubai','New York',CAST('2/14/2024 12:03:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'Salary',NULL,'100',CAST('2/14/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'Bonus','100','0',CAST('2/14/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'Bonus','120','0',CAST('2/14/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'Bonus','150','0',CAST('2/14/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (101,'Employee Name',NULL,'Jordon',CAST('2/16/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (101,'City',NULL,'Toronto',CAST('2/16/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (101,'City','Toronto','Vancouver',CAST('2/17/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'ModifiedBy',NULL,'#ABC System',CAST('2/14/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'ModifiedBy','#ABC System','John',CAST('2/14/2024 12:01:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (101,'ModifiedBy',NULL,'#ABC System',CAST('2/16/2024 12:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'ModifiedOn','2/14/2024 12:00:01','2/14/2024 12:01:01',CAST('2/14/2024 12:01:02' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (100,'ModifiedOn','2/14/2024 12:01:01','2/14/2024 12:02:01',CAST('2/14/2024 12:02:02' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (101,'ModifiedOn',NULL,'2/16/2024 12:00:01',CAST('2/16/2024 12:00:02' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (103,'Employee Name','Jim H','Jim ,',CAST('2/17/2024 4:00:00' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (103,'Employee Name','Jim ,','Jim Howard',CAST('2/17/2024 4:00:00' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (103,'Employee Name','Jim Howard','Jim H',CAST('2/17/2024 4:00:00' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (103,'ModifiedBy',NULL,'James',CAST('2/17/2024 4:00:01' AS DATETIME)); INSERT INTO [dbo].[EmployeeAudit] ([Employee Id],[Attribute Name],[PreviousValue],[NewValue],[ModifiedOn]) VALUES (103,'ModifiedOn',NULL,'2/17/2024 4:00:01',CAST('2/17/2024 4:01:01' AS DATETIME));

However, we have a scenario where a user has updated a record for the same attribute multiple times within the same modifiedon.  Refer below table as example

I’m looking for SQL Code on how to sequence the audit records based on the PreviousValue, NewValue and ModifiedOn for a given Employee Id and Attribute Name. If I have multiple records with same ModifiedOn, visually we could clearly say the first record was the first update followed by second and then third by comparing PreviousValue and NewValue. Jim H -> Jim , and then Jim , -> Jim Howard  and then Jim Howard -> Jim H The Expected output should look like as follows -

The most recent record should be marked with IsCurrent=1 and the remaining records as 0.

Answer 1

@Inturi Bhanu Venkata Satya Shiva Sai
Thank you for reaching out. I have tried to repo the issue from my end and this is the result I got. WITH RankedAudit AS ( SELECT [Employee Id], [Attribute Name], [PreviousValue], [NewValue], [ModifiedOn], ROW_NUMBER() OVER (PARTITION BY [Employee Id], [Attribute Name] ORDER BY [ModifiedOn] DESC) AS RowNum FROM EmployeeAudit ) SELECT [Employee Id], [Attribute Name], [PreviousValue], [NewValue], [ModifiedOn], CASE WHEN RowNum = 1 THEN 1 ELSE 0 END AS IsCurrent FROM RankedAudit ORDER BY [Employee Id], [Attribute Name], RowNum;

Answer 2

Enable Change Tracking. See this link for Change tracking functions to get latest and changed information for records. https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/change-tracking-functions-transact-sql?view=sql-server-ver16