SQL - Identifiy changes of rows-CodePudding

I have this table:

ChangeType = 1 => Record creation

ChangeType = 3 => Record deletion

I'd like to determine which operation I have to do on records. If a record was created and then deleted (line 1 and 2). I'd like to flag them as 'Ignore' (in Operation column). If only creation happened, flag it as 'Create' and if only deletion, flag it as 'Delete'.

Final result will look like this:

1----Ignore

2----Ignore

3----Create

4----Delete

5----Create

Line 2 occured after line 1. If before, mark line 1 as Create and 2 as Ignore)

How it can be done? Cursor?

Thank you

Table creation script:

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] GO

Insert:

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'          ') GO
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'          ') GO
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'          ') GO
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'          ') GO
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'          ') GO

CodePudding user response：

Updating the records based on Status seems to be a nice option:

These are the last (based on CreatedOn) records per EntityKey,CompositeEntityKey group.

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;

output:

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

DBFIDDLE

CodePudding user response：

You can use CTE maybe like this (result here)

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