WITH CTE AS (
 SELECT A.EMPLID
 , C.VENDOR_ID
 , B.FIRST_NAME
 , B.LAST_NAME
 , A.BANK_CD
 , A.ACCOUNT_NUM
 , A.ACCOUNT_TYPE
 , A.PRIORITY
 , A.LAST_UPDATE_DATE
 , A.EFFDT
 , MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE
 , ROW_NUMBER() OVER(PARTITION BY A.EMPLID
  ORDER BY A.EFFDT DESC
  , D.EFFDT DESC) AS RN
  FROM PS_DIRECT_DEPOSIT D INNER JOIN PS_DIR_DEP_DISTRIB A ON A.EMPLID = D.EMPLID
   AND A.EFFDT = D.EFFDT INNER JOIN PS_EMPLOYEES B ON B.EMPLID = A.EMPLID INNER JOIN PS_GHS_DIR_DEP_VND C ON C.EMPLID = A.EMPLID
     INNER JOIN PS_DIR_DEP_DISTRIB E ON E.EMPLID = A.EMPLID AND E.EFFDT = A.EFFDT AND E.PRIORITY = A.PRIORITY
 WHERE B.EMPL_STATUS NOT IN ('T','R','D')
   AND ((A.DEPOSIT_TYPE = 'P'
   AND A.AMOUNT_PCT = 100)
    OR A.PRIORITY = 999
    OR A.DEPOSIT_TYPE = 'B')
   AND D.EFF_STATUS = 'A' )

 SELECT CASE WHEN RN = 1 THEN 'NEW ROW' WHEN RN = 2 THEN 'OLD ROW' END AS
 'ROW_TYPE' , *
 FROM CTE
 WHERE RN IN (1, 2)
 AND MAX_UPDATE_DATE >= GETDATE() - 8

ROW_TYPE    EMPLID     VENDOR_ID    FIRST_NAME    LAST_NAME    BANK_CD    ACCOUNT_NUM    ACCOUNT_TYPE    PRIORITY    LAST_UPDATE_DATE    EFFDT       MAX_UPDATE_DATE    RN
NEW ROW     12345      XYZ123       John          Smith        111111122  45678          C               999         03/12/2019          03/12/2019  03/12/2019         1
OLD ROW     12345      XYZ123       John          Smith        111111122  45678          C               999         10/25/2017          10/25/2017  10/25/2017         2
NEW ROW     47831      A86464       Samm          Bulle        754566654  98865          C               999         03/12/2019          06/08/2018  03/12/2019         1
OLD ROW     47831      A86464       Samm          Bulle        754566654  45678          C               999         10/25/2017          06/08/2018  10/25/2017         2
NEW ROW     32456      KG4561       Kilo          Renne        875123311  32146          C               300         09/02/2018          09/02/2018  09/02/2018         1
OLD ROW     32456      KG4561       Kilo          Renne        971215477  78131          C               310         12/21/2017          12/21/2017  12/21/2017         2

WITH CTE AS (
 SELECT A.EMPLID
 --, C.VENDOR_ID
 , B.FIRST_NAME
 , B.LAST_NAME
 , A.BANK_CD
 , A.ACCOUNT_NUM
 , A.ACCOUNT_TYPE
 , A.PRIORITY
 , A.LAST_UPDATE_DATE
 , A.EFFDT
 , MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE
 , ROW_NUMBER() OVER(PARTITION BY A.EMPLID
  ORDER BY A.EFFDT DESC
  , D.EFFDT DESC) AS RN
  FROM PS_DIRECT_DEPOSIT D INNER JOIN PS_DIR_DEP_DISTRIB A ON A.EMPLID = D.EMPLID
   AND A.EFFDT = D.EFFDT INNER JOIN PS_EMPLOYEES B ON B.EMPLID = A.EMPLID INNER JOIN PS_GHS_DIR_DEP_VND C ON C.EMPLID = A.EMPLID
 ---ADDING SELF JOIN BELOW---
     INNER JOIN PS_DIR_DEP_DISTRIB E ON E.EMPLID = A.EMPLID AND E.EFFDT =
     A.EFFDT AND E.PRIORITY = A.PRIORITY
 WHERE B.EMPL_STATUS NOT IN ('T','R','D')
   AND ((A.DEPOSIT_TYPE = 'P'
   AND A.AMOUNT_PCT = 100)
    OR A.PRIORITY = 999
    OR A.DEPOSIT_TYPE = 'B')
   AND D.EFF_STATUS = 'A'
---ADDING NEW WHERE CONDITION BELOW---
   AND (E.ACCOUNT_NUM <> A.ACCOUNT_NUM OR E.BANK_CD <> A.BANK_CD ))
 SELECT CASE WHEN RN = 1 THEN 'NEW ROW' WHEN RN = 2 THEN 'OLD ROW' END AS 'ROW_TYPE'
 , *
  FROM CTE
  WHERE RN IN (1, 2)
  AND MAX_UPDATE_DATE >= GETDATE() - 8