SQL Server 2022

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

CREATE TABLE CaseTracking 
(
    CaseID	VARCHAR(512),
    State	    VARCHAR(512),
    EventTimeStamp	VARCHAR(512),
    Cleared	VARCHAR(512)
);

INSERT INTO CaseTracking ([CaseID], [State], EventTimeStamp, Cleared) VALUES
	('1', 'OrderReceived', '2024-03-20', '0'),
	('1', 'Completed', '2024-03-21', '0'),
	('1', 'Completed', '2024-03-22', '1'),
	('2', 'OrderReceived', '2024-03-24', '0'),
	('2', 'Completed', '2024-03-25', '0'),
    ('2', 'Completed', '2024-03-26', '1'),
    ('2', 'Completed', '2024-03-27', '0'),
  	('3', 'OrderReceived', '2024-03-24', '0'),
  	('3', 'OrderReceived', '2024-03-25', '1'),
	('3', 'Completed', '2024-03-26', '0');

WITH last AS (
  SELECT
    CaseID,
    State,
    MAX(EventTimeStamp) as EventTimeStamp
  FROM CaseTracking
  GROUP BY CaseID, State
)
SELECT
  i.CaseID,
  CASE WHEN c1.Cleared = 0 THEN c1.EventTimeStamp END AS OrderReceived,
  CASE WHEN c2.Cleared = 0 THEN c2.EventTimeStamp END AS Completed
FROM last i
LEFT JOIN last o ON o.CaseID = i.CaseID
  AND o.State = 'OrderReceived'
LEFT JOIN CaseTracking c1 ON c1.CaseID = i.CaseID
  AND c1.State = o.State
  AND c1.EventTimeStamp = o.EventTimeStamp
LEFT JOIN last c ON c.CaseID = i.CaseID
  AND c.State = 'Completed'
LEFT JOIN CaseTracking c2 ON c2.CaseID = c1.CaseID
  AND c2.State = c.State
  AND c2.EventTimeStamp = c.EventTimeStamp
WHERE i.State = 'OrderReceived'

CaseID	OrderReceived	Completed
1	2024-03-20	null
2	2024-03-24	2024-03-27
3	null	2024-03-26