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 |