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 |