select
count(*)
FROM deal_master DM
JOIN #SMUserPermission SUP(index indx_#SMUserPermission)
ON SUP.deal_id = DM.deal_id 

JOIN tranche_master TM
ON DM.deal_id = TM.deal_id 

LEFT OUTER JOIN ratings..debt_ratifer RF
ON TM.debt_num = RF.debt_num
and RF.shadow_cd <> "ULR"
and RF.ratg_typ_cd = "LT"

LEFT OUTER JOIN ratings..debt_ratgs DR
ON TM.debt_num = DR.debt_num
and DR.ratifer_num = RF.ratifer_num
and DR.ratg_indc = "Y"
and DR.ratg_typ_cd = RF.ratg_typ_cd
and DR.ratg_expire_dt is null 

LEFT OUTER JOIN LookUp LKP
ON LKP.lookUpDesc = DR.ratg_txt
and LKP.lookUpCategory= 10  

LEFT OUTER JOIN ratings..debt_watchlist DW
ON DW.debt_num = RF.debt_num
AND RF.ratifer_num = DW.ratifer_num
AND DW.ratg_typ_cd = "LT"
AND DW.watch_expire_dt is null
 
LEFT OUTER JOIN ratings..debt_ratgs PR
ON PR.debt_num = RF.debt_num
and PR.ratifer_num = RF.ratifer_num
and PR.ratg_indc = "Y"
and PR.ratg_typ_cd = "LT"
and PR.ratg_expire_dt is not null
and PR.ratg_dt = (select max(DPR.ratg_dt) from ratings..debt_ratgs DPR
                where DPR.debt_num = RF.debt_num
                and DPR.ratifer_num = RF.ratifer_num
                and DPR.ratg_indc = "Y"
                and DPR.ratg_typ_cd = "LT"
                and DPR.ratg_expire_dt is not null
                )

LEFT OUTER JOIN ratings..debt_ratifer RF_ST
ON TM.debt_num = RF_ST.debt_num
and RF_ST.shadow_cd <> "ULR"
and RF_ST.ratg_typ_cd = "ST"
 
LEFT OUTER JOIN ratings..debt_ratgs DR_ST
ON TM.debt_num = DR_ST.debt_num
and DR_ST.ratifer_num = RF_ST.ratifer_num
and DR_ST.ratg_indc = "Y"
and DR_ST.ratg_typ_cd = RF_ST.ratg_typ_cd
and DR_ST.ratg_expire_dt is null

LEFT OUTER JOIN ratings..debt_ratifer RF_ULR
ON TM.debt_num = RF_ULR.debt_num
and RF_ULR.shadow_cd = "ULR" 

LEFT OUTER JOIN ratings..debt_ratgs DR_ULR
ON TM.debt_num = DR_ULR.debt_num
and DR_ULR.ratifer_num = RF_ULR.ratifer_num
and DR_ULR.ratg_indc = "Y"
and DR_ULR.publish_indc = "Y"
and DR_ULR.ratg_txt not like '(P)%'
and (DR_ULR.ratg_expire_dt is null or DR_ULR.ratg_expire_dt > getdate())