SQL Server 2022

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

CREATE TABLE StudyData (
    StudyID INT,
    ClientName VARCHAR(100),
    DateSign DATE,
    SchedStart DATE,
    ActStart DATE,
    ActEnd DATE,
    SchedEnd DATE,
    AssignmentType VARCHAR(100)
)
INSERT StudyData
VALUES
    (1, 'Client1', NULL, '2022-01-05', NULL, NULL, NULL, 'Final'),
    (2, 'Client2', NULL, '2023-04-15', NULL, NULL, NULL, 'Final'),
    (3, 'Client3', NULL, '2024-01-10', NULL, NULL, NULL, 'Draft'),
    (3, 'Client3', NULL, '2024-01-25', NULL, NULL, NULL, 'Final'),
    (4, 'Client4', NULL, '2023-12-10', NULL, NULL, NULL, 'Final'),
    (5, 'Client5', NULL, '2023-12-31', NULL, NULL, NULL, 'Draft'),
    (5, 'Client5', NULL, '2024-01-20', NULL, NULL, NULL, 'Final'),
    (6, 'Client6', NULL, '2022-10-10', NULL, NULL, NULL, 'Final'),
    (7, 'Client7', NULL, '2023-09-15', NULL, NULL, NULL, 'Final'),
    (8, 'Client8', NULL, '2023-12-30', NULL, NULL, NULL, 'Final'),  -- Two "Final" rows
    (8, 'Client8', NULL, '2023-12-31', NULL, NULL, NULL, 'Final')
11 rows affected

-- Using GROUP BY and COUNT(*) together with a filter for "Final" to get just the IDs
SELECT S.StudyID
FROM StudyData S
GROUP BY S.StudyID
HAVING COUNT(*) = 1
AND MAX(S.AssignmentType) = 'Final'


StudyID


1

2

4

6

7


-- The above wrapped in a subquery and joined with the desired data
SELECT S.*
FROM (
    SELECT S.StudyID
    FROM StudyData S
    GROUP BY S.StudyID
    HAVING COUNT(*) = 1
    AND MAX(S.AssignmentType) = 'Final'
) SELECTED
JOIN StudyData S
    ON S.StudyID = SELECTED.StudyID


StudyID
ClientName
DateSign
SchedStart
ActStart
ActEnd
SchedEnd
AssignmentType


1
Client1
null
2022-01-05
null
null
null
Final

2
Client2
null
2023-04-15
null
null
null
Final

4
Client4
null
2023-12-10
null
null
null
Final

6
Client6
null
2022-10-10
null
null
null
Final

7
Client7
null
2023-09-15
null
null
null
Final


-- Using NOT EXISTS() to exclude "final" rows having associated "draft" rows 
SELECT *
FROM StudyData S
WHERE S.AssignmentType = 'Final'
AND NOT EXISTS(
    SELECT *
    FROM StudyData S2
    WHERE S2.StudyID = S.StudyID
    AND S2.AssignmentType = 'Draft'
) 


StudyID
ClientName
DateSign
SchedStart
ActStart
ActEnd
SchedEnd
AssignmentType


1
Client1
null
2022-01-05
null
null
null
Final

2
Client2
null
2023-04-15
null
null
null
Final

4
Client4
null
2023-12-10
null
null
null
Final

6
Client6
null
2022-10-10
null
null
null
Final

7
Client7
null
2023-09-15
null
null
null
Final

8
Client8
null
2023-12-30
null
null
null
Final

8
Client8
null
2023-12-31
null
null
null
Final


-- Selecting any "Final" row, where there is only one row for that StudyID
SELECT *
FROM (
    SELECT *, COUNT(*) OVER(PARTITION BY S.StudyID) AS NumRows
    FROM StudyData S
) SN
WHERE SN.NumRows = 1
AND SN.AssignmentType = 'Final'


StudyID
ClientName
DateSign
SchedStart
ActStart
ActEnd
SchedEnd
AssignmentType
NumRows


1
Client1
null
2022-01-05
null
null
null
Final
1

2
Client2
null
2023-04-15
null
null
null
Final
1

4
Client4
null
2023-12-10
null
null
null
Final
1

6
Client6
null
2022-10-10
null
null
null
Final
1

7
Client7
null
2023-09-15
null
null
null
Final
1

StudyID	ClientName	DateSign	SchedStart	ActStart	ActEnd	SchedEnd	AssignmentType
1	Client1	null	2022-01-05	null	null	null	Final
2	Client2	null	2023-04-15	null	null	null	Final
4	Client4	null	2023-12-10	null	null	null	Final
6	Client6	null	2022-10-10	null	null	null	Final
7	Client7	null	2023-09-15	null	null	null	Final