Subquery or Unions for adding more strict conditions?

Question

I have some rows that are coming up that I want to exclude

My Code:

SELECT DISTINCT T1.FILENUM, T5.OFG, T4.UN
FROM
T1
LEFT OUTER JOIN 
   T2 ON T1.ID=T2ID
LEFT OUTER JOIN 
   T3 ON T1.ID = T3.ID
LEFT OUTER JOIN 
   T4 ON T3.ID=T4ID
LEFT OUTER JOIN 
   T5 ON T2.ID = T5.ID
WHERE DATECondition
AND T1.FILENUM LIKE 'S%'
AND (T4.UN = 26 OR T4.UN = 25 OR T4.UN = 24 OR T4.UN = 32)

Results

FILENUM    OFG     UN
S1          S      26
S1          C      25
S1          D      26
S2          S      26
S2          S      24
S3          S      26
S4          S      26
S4          C      25
S5          S      32
S6          S      24
S7          S      25
S7          S      24

What these tables means are that there are complaints of records of filenum that UN are users and routings on the complaint. OFG is the complainant. UN is the user routing the Filenums.

1. What I wanted to add extra is, only when (OFG not like '[cd]%' or OFG IS NULL), then I also want to make sure that for a row: if OFG is C or D, please exclude ALL rows even if it has S.

2. I want to scan these rows and look at the UN. If the count for filenum that UN has 24 and UN is also 26 equals 2 or more, only show the filenum row with un is 26. The result is that, I have a user that is a supervisor is always accessing the complaint and also the same user (UN) who also only handles the complaint alone. I want to show that if that supervisor user access the case with another user, don't include his row because he may be just reading the complaint. There are also 3 other users I want to compare this spervisor user.

So another example, if count of filenum of sueprvisor user is 1, include the row in my result. If the count for filenum where un in (25, 24), 2 users one being the supervisor, is greater than 2, then only include the row with un that is 25. Repeat this with un in (26, 24) and un in (32, 24).

Results 2:

FILENUM    OFG     UN
S2          S      26    
S3          S      26
S5          S      32
S6          S      24
S7          S      25

Answer 1

Try this.

CREATE TABLE #test1
  (FILENUM VARCHAR(50),OFG CHAR(1),UN INT)

INSERT #test1
VALUES ('S1','S',26),('S1','C',25),('S1','D',26),
       ('S2','S',26),('S2','S',24),('S3','S',26),
       ('S4','S',26),('S4','C',25),('S5','S',32),
       ('S6','S',24),('S7','S',25),('S7','S',24) 


WITH cte
     AS (SELECT Row_number()OVER(partition BY filenum ORDER BY un DESC) rn,
                *
         FROM   #test1
         WHERE  FILENUM NOT IN(SELECT FILENUM
                               FROM   #test1
                               WHERE  OFG IN ( 'C', 'D' )))
SELECT FILENUM,OFG,UN
FROM   cte
WHERE  rn = 1 

OUTPUT :

FILENUM OFG UN
------- --- --
S2      S   26
S3      S   26
S5      S   32
S6      S   24
S7      S   25