SELECT DISTINCT P2.criteriaId
    ,hasExceptions=CASE WHEN DC.dealComponentId IS NOT NULL
                                AND DC.exclusionIndc='N'
                           THEN 'Y'
                           ELSE 'N'
                      END
     ,hasExclusions=CASE WHEN DC2.dealComponentId IS NOT NULL
                                AND DC2.exclusionIndc='Y'
                           THEN 'Y'
                           ELSE 'N'
                      END
FROM

(
 SELECT  RL.ratioCd
        ,PC.criteriaId
        ,PC.marketCd
        ,PC.product_line
        ,PC.vintageFrom
        ,PC.vintageTo
        FROM  SMLkpRatio RL
        JOIN SMRatioCriteria PC
         ON PC.ratioCd= RL.ratioCd
         AND PC.dealComponentId IS NULL
	 AND PC.isCurrent = 'Y' AND PC.criteriaStatusCd=4
         AND PC.ratingFrom=1 AND PC.ratingTo=21
         AND PC.seasoningFrom IS NULL AND PC.seasoningTo IS NULL
        WHERE    RL.isActive='Y'


       ) P2
 LEFT JOIN SMRatioCriteria DC
 ON P2.ratioCd=DC.ratioCd
 AND P2.marketCd = DC.marketCd
 AND P2.product_line = DC.product_line
 AND DC.vintageFrom BETWEEN  P2.vintageFrom and P2.vintageTo
 AND DC.dealComponentId IS NOT NULL
 AND DC.isCurrent ='Y'  --Only current deal level
 AND DC.exclusionIndc='N'
LEFT JOIN SMRatioCriteria DC2
 ON P2.ratioCd=DC2.ratioCd
 AND P2.marketCd = DC2.marketCd
 AND P2.product_line = DC2.product_line
 AND DC2.vintageFrom BETWEEN  P2.vintageFrom and P2.vintageTo
 AND DC2.dealComponentId IS NOT NULL
 AND DC2.isCurrent ='Y'  --Only current deal level
 AND DC2.exclusionIndc='Y'
 WHERE
 DC.criteriaId IS NOT NULL OR DC2.criteriaId IS NOT NULL