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

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

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