SQL Server 2019

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

CREATE TABLE #DATA (ASOFDATE DATE, CUSTOMERID INT, ACTIVE BIT, FIRSTORDER BIT)
INSERT INTO #DATA VALUES
('2018-01-31',  206424, NULL,   NULL),
('2018-02-28',  206424, NULL,   NULL),
('2018-03-31',  206424, 1,      1),
('2022-06-30',  206424, NULL,   NULL),
('2022-07-31',  206424, NULL,   NULL),
('2018-06-30',  247034, NULL,   NULL),
('2018-07-31',  247034, NULL,   NULL),
('2018-08-31',  247034, 1,  1),
('2022-05-31',  247034, NULL,   NULL)
9 rows affected

SELECT CUSTOMERID, MIN(ASOFDATE) AS StartDate
FROM #DATA
WHERE FIRSTORDER = 1
GROUP BY CUSTOMERID
ORDER BY CUSTOMERID


CUSTOMERID
StartDate


206424
2018-03-31

247034
2018-08-31


WITH StartDates AS (
    SELECT CUSTOMERID, MIN(ASOFDATE) AS StartDate
    FROM #DATA
    WHERE FIRSTORDER = 1
    GROUP BY CUSTOMERID
)
DELETE D
FROM StartDates S
JOIN #DATA D
    ON D.CUSTOMERID = S.CUSTOMERID
    AND D.ASOFDATE < S.StartDate
4 rows affected

SELECT *
FROM #Data
ORDER BY CUSTOMERID, ASOFDATE


ASOFDATE
CUSTOMERID
ACTIVE
FIRSTORDER


2018-03-31
206424
True
True

2022-06-30
206424
null
null

2022-07-31
206424
null
null

2018-08-31
247034
True
True

2022-05-31
247034
null
null

ASOFDATE	CUSTOMERID	ACTIVE	FIRSTORDER
2018-03-31	206424	True	True
2022-06-30	206424	null	null
2022-07-31	206424	null	null
2018-08-31	247034	True	True
2022-05-31	247034	null	null