, x.FAC_ACTIVITY [FAC_ACTIVITY]
, x.NUMMAINTEMP [NUMMAINTEMP]
, LH.dtLeaseFrom [LHdtLeaseFrom]
, LH.dtLeaseTo [LHdtleaseto]
, crp.destimated [crpdestimated]
--, pps.HValue [ppsNSFFEE]
,pps.NSFFee
,pps.MTMFee
,pps.SecDepIntRate
, m.stext [memoStext]
FROM vw_DK_RMMISCDATA x
LEFT JOIN Property p
ON x.RM_ID = p.sCode
OUTER APPLY ( -- flatten relevant rows from PropOptions
SELECT
HPROP= MAX(po.HPROP),
STYPE= MAX(po.STYPE),
SVALUE= MAX(po.SVALUE),
NSFFee= MAX(CASE WHEN po.STYPE = 'nsffee' THEN po.hvalue END),
MTMFee= MAX(CASE WHEN po.STYPE = 'MTMFee' THEN po.hvalue END),
SecDepIntRate = MAX(CASE WHEN po.STYPE = 'DDEPOSITINTEREST' THEN po.hvalue END)
FROM PROPOPTIONS po
WHERE po.HPROP = p.hmy -- outer reference
AND po.STYPE IN ('nsffee', 'MTMFee', 'DDEPOSITINTEREST') -- filter
) pps
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden