SQL Server 2022

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

DROP TABLE IF EXISTS #Movements
CREATE TABLE #Movements 
(
    CreateDate datetime2,
    EventCode nvarchar(3)
)

INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-20', 'EMA')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-21', 'EMD')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-22', 'EMD')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-22', 'EMF')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-23', 'EMI')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-24', 'EMI')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-25', 'EMI')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-26', 'EMI')

SELECT * FROM #Movements


CreateDate
EventCode


2023-03-20 00:00:00.0000000
EMA

2023-03-21 00:00:00.0000000
EMD

2023-03-22 00:00:00.0000000
EMD

2023-03-22 00:00:00.0000000
EMF

2023-03-23 00:00:00.0000000
EMI

2023-03-24 00:00:00.0000000
EMI

2023-03-25 00:00:00.0000000
EMI

2023-03-26 00:00:00.0000000
EMI


SELECT EventCode, MAX(CreateDate)
FROM #Movements
WHERE EventCode = 'EMI'
GROUP BY EventCode


EventCode
(No column name)


EMI
2023-03-26 00:00:00


select t.*
from #Movements t
LEFT JOIN (
  SELECT EventCode, MIN(CreateDate) as CreateDate
  FROM #Movements
  WHERE EventCode = 'EMI'
  GROUP BY EventCode
) s on s.EventCode = t.EventCode and t.CreateDate > s.CreateDate
where s.CreateDate is null


CreateDate
EventCode


2023-03-20 00:00:00.0000000
EMA

2023-03-21 00:00:00.0000000
EMD

2023-03-22 00:00:00.0000000
EMD

2023-03-22 00:00:00.0000000
EMF

2023-03-23 00:00:00.0000000
EMI

CreateDate	EventCode
2023-03-20 00:00:00.0000000	EMA
2023-03-21 00:00:00.0000000	EMD
2023-03-22 00:00:00.0000000	EMD
2023-03-22 00:00:00.0000000	EMF
2023-03-23 00:00:00.0000000	EMI
2023-03-24 00:00:00.0000000	EMI
2023-03-25 00:00:00.0000000	EMI
2023-03-26 00:00:00.0000000	EMI