T-SQL - Based On A specific value Remove the Row

Question

In my case I need to filter a file based on a ITSUser and its role.

The user can have 2 roles "A" and "B".I want only 1 File per 1 Row based on below conditions.

• If StateFile has ITSUser exists in the file and has NO role "B" then --> Yes , No
• Else If StateFile has ITSUser exists in the file and has role "B" then --> Yes , Yes
• Else If StateFile has NO ITSUser exists in the file and has NO role "B" then --> No , No

Below is the query that i am using.

SELECT DISTINCT MSEF.StatefileID,
MSEF.StateFileName,
AFEE.SubmittedDate,
UNO.DisplayName AS SubmittedBy,
        (select DISTINCT (
    CASE WHEN (
     MSETET.ITSUserID = '99' 
                 AND ET.StateTeamName = N'Live')
            THEN 'Yes'
ELSE 'No'
    END)) AS CHICMAN,
    (select DISTINCT (
    CASE WHEN ( 

    MSETET.ITSUserID  IN( '99') 
          AND ET.StateTeamName  IN( N'Live') 
          AND MSETET.StateTeamID=ET.StateTeamID ) --AND MSEF.StateFileID != MSETET.StateFileID    
                 THEN 'Yes'
ELSE 'No'
    END)) AS CHICTeamIsInTheLive  
from MS.StateFile MSEF WITH (NOLOCK)
INNER JOIN EM.StateFileRem AFEE WITH (NOLOCK) ON MSEF.StateFileID = AFEE.StateFileID
INNER JOIN MS.StateTeamMember MSETM WITH (NOLOCK) ON MSETM.StateFileID=MSEF.StateFileID
INNER JOIN SOP.ITSUser UNO WITH (NOLOCK) ON AFEE.SubmittedByUserID = UNO.ITSUserID
INNER JOIN MS.StateTeamStateTeamMember MSETET ON MSETET.StateFileID=AFEE.StateFileID
INNER JOIN [MS].[StateTeam] ET WITH (NOLOCK) ON ET.StateFileID = MSETET.StateFileID
 ORDER BY
    MSEF.StateFileID 
GO 

My Current Output :

My expected Output :

Answer 1

That should work:

select top 1 with ties
    StatefileID
    ,StateFileName
    ,SubmittedDate
    ,SubmittedBy
    ,CHICMAN
    ,CHICTeamIsInTheLive
from 
(
    SELECT DISTINCT MSEF.StatefileID,
    MSEF.StateFileName,
    AFEE.SubmittedDate,
    UNO.DisplayName AS SubmittedBy,
            (select DISTINCT (
        CASE WHEN (
         MSETET.ITSUserID = '99' 
                     AND ET.StateTeamName = N'Live')
                THEN 'Yes'
    ELSE 'No'
        END)) AS CHICMAN,
        (select DISTINCT (
        CASE WHEN ( 

        MSETET.ITSUserID  IN( '99') 
              AND ET.StateTeamName  IN( N'Live') 
              AND MSETET.StateTeamID=ET.StateTeamID ) --AND MSEF.StateFileID != MSETET.StateFileID    
                     THEN 'Yes'
    ELSE 'No'
        END)) AS CHICTeamIsInTheLive  
    from MS.StateFile MSEF WITH (NOLOCK)
    INNER JOIN EM.StateFileRem AFEE WITH (NOLOCK) ON MSEF.StateFileID = AFEE.StateFileID
    INNER JOIN MS.StateTeamMember MSETM WITH (NOLOCK) ON MSETM.StateFileID=MSEF.StateFileID
    INNER JOIN SOP.ITSUser UNO WITH (NOLOCK) ON AFEE.SubmittedByUserID = UNO.ITSUserID
    INNER JOIN MS.StateTeamStateTeamMember MSETET ON MSETET.StateFileID=AFEE.StateFileID
    INNER JOIN [MS].[StateTeam] ET WITH (NOLOCK) ON ET.StateFileID = MSETET.StateFileID
) x
where CHICMAN = 'Yes' or CHICTeamIsInTheLive = 'Yes'
order by row_number() over (partition by StatefileID order by CHICMAN desc, CHICTeamIsInTheLive desc)