SQL Server 2019

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

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

with t1 as (
select *,
lead(ChangeType) over (partition by EntityKey,CompositeEntityKey order by EntityKey,CompositeEntityKey,createdon) as next_type,
lag(ChangeType) over (partition by EntityKey,CompositeEntityKey order by EntityKey,CompositeEntityKey,createdon )  as previous_type 
from mytable
)
select t1.*,
case 
   when next_type is null and previous_type is null then
      case changetype when 1 then 'create' when 3 then 'delete' end
   when next_type = 3 and changetype = 1 then 'ignore'
   when previous_type = 1 and changetype = 3 then 'ignore'
end as operation
from t1


EntityKey
CompositeEntityKey
DynEntityTypeKey
ChangeType
CreatedOn
FinalOperation
next_type
previous_type
operation


12345
14523
3
3
2022-03-19 11:29:19.230
          
null
null
delete

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

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

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

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

EntityKey	CompositeEntityKey	DynEntityTypeKey	ChangeType	CreatedOn	next_type	previous_type	operation
12345	14523	3	3	2022-03-19 11:29:19.230	null	null	delete
12345	54321	3	1	2022-03-20 11:29:19.230	3	null	ignore
12345	54321	3	3	2022-03-20 11:30:19.230	null	1	ignore
12345	98765	3	1	2022-03-21 11:29:19.230	null	null	create
14523	98765	3	1	2022-03-20 14:29:19.230	null	null	create