SQL Server 2022

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

CREATE TABLE CaseTracking 
(
    [Case]	VARCHAR(512),
    [State]	VARCHAR(512),
    EventTimeStamp	VARCHAR(512),
    Cleared	VARCHAR(512)
);

INSERT INTO CaseTracking ([Case], [State], EventTimeStamp, Cleared) VALUES
	('1', 'OrderReceived', '2024-03-20 12:20', '0'),
	('1', 'Completed', '2024-03-20 12:51', '0'),
	('1', 'Completed', '2024-03-20 12:55', '1'),
	('2', 'OrderReceived', '2024-03-21 10:00', '0'),
	('2', 'Completed', '2024-03-22 08:00', '0');

SELECT * FROM CaseTracking

SELECT [CASE]    
      ,MAX([OrderReceived]) as [OrderReceived] 
      ,CASE MIN([Completed]) WHEN '1990-1-1' THEN NULL ELSE MIN([Completed]) END as [Completed]
FROM
(
  
SELECT [Case]
    ,[OrderReceived]
    ,[Completed]

FROM (SELECT [Case]
        ,[Cleared]
        ,[State]
        ,[EventTimeStamp]  
        ,CASE [CLEARED]  WHEN 1 THEN '1990-1-1' ELSE [EventTimeStamp] END as ets
     
    FROM CaseTracking
        ) AS SourceTable

PIVOT (
    MAX(ets)
    FOR [State] IN ([OrderReceived]
    ,[Completed])) AS PivotTable

) t
GROUP BY [CASE]

Case	State	EventTimeStamp	Cleared
1	OrderReceived	2024-03-20 12:20	0
1	Completed	2024-03-20 12:51	0
1	Completed	2024-03-20 12:55	1
2	OrderReceived	2024-03-21 10:00	0
2	Completed	2024-03-22 08:00	0

CASE	OrderReceived	Completed
1	2024-03-20 12:20	null
2	2024-03-21 10:00	2024-03-22 08:00

Warning: Null value is eliminated by an aggregate or other SET operation.