Display unique column when not all values are distinct Name Status Contact EpisodeNumber
AA   C      E       1
AA   C      F       1
AA   C      G       1
AA   D      H       1
BB   D      I       2
CC   C      J       3
DD   C      K       4




If D is present on any of the 'status' column of the given Names i don't want to see them. each 'contact' is different which is fine, i need to make sure the EpisodeNumber is the same. What happens is everytime "Status" is changed it creates a new row for that name with the contact that changed it so i can't do a simple select * where status != 'D' because it will show me the other statuses for that given episodenumber. We are running SQL server 2008 R2 with sql management studio version 10.50.6560.0 and report builder 3.0

Name Status Contact EpisodeNumber
CC   C      J       3
DD   C      K       4
If I understand your question correctly, I think NOT EXISTS should work.

DECLARE @T TABLE (
    NAME VARCHAR(30)
    ,STATUS VARCHAR(5)
    ,Contact VARCHAR(5)
    ,EpisodeNumber INT
    );
INSERT INTO @T
VALUES
    ('AA', 'C', 'E', 1),
    ('AA', 'C', 'F', 1),
    ('AA', 'C', 'G', 1),
    ('AA', 'D', 'H', 1),
    ('BB', 'D', 'I', 2),
    ('CC', 'C', 'J', 3),
    ('DD', 'C', 'K', 4)
;
SELECT *
FROM @T t
WHERE NOT EXISTS (
        SELECT *
        FROM @T
        WHERE NAME = t.NAME
            AND EpisodeNumber = t.EpisodeNumber
            AND STATUS = 'D'
        );




| NAME | STATUS | Contact | EpisodeNumber |
|------|--------|---------|---------------|
| CC   | C      | J       | 3             |
| DD   | C      | K       | 4             |