By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ClusterTests (
TestId INTEGER,
Cluster VARCHAR(1),
TestDate DATETIME,
TestResult VARCHAR(8)
);
INSERT INTO ClusterTests
(TestId, Cluster, TestDate, TestResult)
VALUES
('1', 'A', '2019-01-01', 'Accepted'),
('2', 'A', '2019-01-15', 'Rejected'),
('3', 'B', '2019-01-01', 'Accepted'),
('4', 'B', '2019-01-15', 'Accepted');
4 rows affected
with cte as (
select cluster, CONVERT(NVARCHAR(7), TestDate, 120) Month, testid
from ClusterTests
where testresult = 'Accepted'
)
select c.* from cte c
where not exists (
select 1 from cte
where cluster = c.cluster and testid > c.testid and month = c.month
)
| cluster | Month | testid |
|---|---|---|
| A | 2019-01 | 1 |
| B | 2019-01 | 4 |