SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
	Jul 25 2020 11:26:55 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE [dbo].[MyTable](
[EntityKey] [int] NULL,
[CompositeEntityKey] [int] NULL,
[DynEntityTypeKey] [int] NULL,
[ChangeType] [tinyint] NULL,
[CreatedOn] [datetime] NULL,
[FinalOperation] [nchar](10) NULL) ON [PRIMARY] 
INSERT [dbo].[MyTable] ([EntityKey], [CompositeEntityKey], [DynEntityTypeKey], [ChangeType], [CreatedOn], [FinalOperation]) 
VALUES (12345, 54321, 3, 1, CAST(N'2022-03-20T11:29:19.230' AS DateTime), N'          '); 

INSERT [dbo].[MyTable] ([EntityKey], [CompositeEntityKey], [DynEntityTypeKey], [ChangeType], [CreatedOn], [FinalOperation]) 
VALUES (12345, 54321, 3, 3, CAST(N'2022-03-20T11:30:19.230' AS DateTime), N'          '); 

INSERT [dbo].[MyTable] ([EntityKey], [CompositeEntityKey], [DynEntityTypeKey], [ChangeType], [CreatedOn], [FinalOperation]) 
VALUES (12345, 98765, 3, 1, CAST(N'2022-03-21T11:29:19.230' AS DateTime), N'          '); 

INSERT [dbo].[MyTable] ([EntityKey], [CompositeEntityKey], [DynEntityTypeKey], [ChangeType], [CreatedOn], [FinalOperation]) 
VALUES (12345, 14523, 3, 3, CAST(N'2022-03-19T11:29:19.230' AS DateTime), N'          ');

INSERT [dbo].[MyTable] ([EntityKey], [CompositeEntityKey], [DynEntityTypeKey], [ChangeType], [CreatedOn], [FinalOperation]) 
VALUES (14523, 98765, 3, 1, CAST(N'2022-03-20T14:29:19.230' AS DateTime), N'          '); 
5 rows affected

select 
   entityKey,
   CompositeEntityKey,
   DynEntityTypeKey,
   ChangeType,
   CreatedOn,
   CASE WHEN MAX(CreatedOn) OVER (PARTITION By EntityKey,CompositeEntityKey ORDER BY CreatedOn DESC)=CreatedOn
     THEN 1
     ELSE 0
     END Status
from MyTable
order by 1,2;


entityKey
CompositeEntityKey
DynEntityTypeKey
ChangeType
CreatedOn
Status


12345
14523
3
3
2022-03-19 11:29:19.230
1

12345
54321
3
3
2022-03-20 11:30:19.230
1

12345
54321
3
1
2022-03-20 11:29:19.230
0

12345
98765
3
1
2022-03-21 11:29:19.230
1

14523
98765
3
1
2022-03-20 14:29:19.230
1

entityKey	CompositeEntityKey	DynEntityTypeKey	ChangeType	CreatedOn	Status
12345	14523	3	3	2022-03-19 11:29:19.230	1
12345	54321	3	3	2022-03-20 11:30:19.230	1
12345	54321	3	1	2022-03-20 11:29:19.230	0
12345	98765	3	1	2022-03-21 11:29:19.230	1
14523	98765	3	1	2022-03-20 14:29:19.230	1