Case or If/Then Statement

 More info to view.
    Customers can have differenct contract numbers so we are just testing the absolute. 
`if there is a contract on file for any of the customers it would be "Y" Else "N" 
so the customer below should only display one record indicating "Y"
    SELECT DISTINCT
        r.CUST_ACCT_ID AS ACCT_NUM,
        r.PRI_ACCT_IND AS PRIMARY_ACCOUNT,
        r.CUST_NUM AS PRI_ACCT_NUM,
        r.CUST_ACCT_NAM,
        CASE WHEN (cvo.CNTRCT_ID) IS NULL  THEN 'N' ELSE 'Y' END AS CARVOUT_LEAD
    Results
    ACCT_NUM    PRIM_ACCT  PRI_ACCT_NUM CUST_ACCT_NAM   SPCL_GRP
    050774    Y           050774           ACME CUSTOMER   N
    050774    Y           050774           ACME CUSTOMER   Y

So all you want is

Acct     MasterAcct    Grouping
1        1             Y
2        1             Y
3        3             Y
4        3             Y
5        5             N

In which case, something like this:

Setting up temporary table & data

declare @t table (Acct int, MasterAcct int)
insert into @t select 1,1 union all select 2,1 union all select 3,3 union all select 4,3 union all select 5,5

and the query itself:

;
WITH T2
     AS (SELECT Acct,
                MasterAcct,
                Rank() OVER (partition BY masteracct ORDER BY acct DESC) AS AccRank
         FROM   @t)
SELECT Acct,
       MasterAcct,
       CASE
         WHEN AccRank <> 1
              AND Acct = MasterAcct THEN 'Y'
         WHEN Acct <> MasterAcct THEN 'Y'
         ELSE 'N'
       END
FROM   T2
ORDER  BY Acct,
          MasterAcct