Correalated sub...

i have a historical tale with seven different columns in it. I would like to join to this table and get the latest data for a particular key in it, the problem is one row in the historical table may only have one updated column in which case i need a compilatoun of several different rows to make the 'latest' row.(i.e. one updated row may only update one field...) i think i know what i want to do code follows... but it doesnt work... any ideas?
SELECT
CTRP.CUSIP as CUSIP,
CTRP.PORTFOLIO_NAME as PORTFOLIO,
CF.FACTOR * TRCURORIGFACE as CUR_FACE,
CF.FACTOR as CUR_FACTOR,
CPA.PX_ASK as CUR_PX_ASK,
CPS.PX_SPD AS CUR_PX_SPD,
CPSD.PX_SPD_DT AS CUR_PX_SPD_DT,
DMB.DISC_MRGN_BID AS DISC_MRGN_BID,
CWC.WAL_CALL AS CUR_WAL_CALL,
CT.TRMODDUR AS CUR_TRMODDUR,
CPA.RECORD_DATE AS CPA_RT,
CPS.RECORD_DATE AS CPS_RT,
CPSD.RECORD_DATE AS CPSD_RT,
DMB.RECORD_DATE AS DMB_RT,
CMC.RECORD_DATE AS CMC_RT,
CWC.RECORD_DATE AS CWC_RT,
CT.RECORD_DATE AS CT_RT,
CF.RECORD_DATE AS CF_RT

FROM
(SELECT
DEAL_BOND_NAME AS CUSIP,
DEAL_BOND_ID,
sum(trcurorigface * case tran_deal_type_name when 'Buy' then 1 when 'Sell' then -1 end) AS trcurorigface,
PORTFOLIO_NAME
FROM
VIEW_TRAN_DEAL_BOND
LEFT OUTER JOIN DEAL_BOND DB ON DB.ID = VIEW_TRAN_DEAL_BOND.DEAL_BOND_ID
WHERE
hedge = 0 OR hedge is null
GROUP BY
PORTFOLIO_NAME,
DEAL_BOND_NAME,
DEAL_BOND_ID) CTRP
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CF ON CF.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPA ON CPA.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPS ON CPS.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPSD ON CPSD.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY DMB ON DMB.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CMC ON CMC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CWC ON CWC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CT ON CT.ID = CTRP.DEAL_BOND_ID
WHERE
CPA.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH1
WHERE PX_ASK IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH1.DEAL_BOND_ID) and
CF.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH2
WHERE FACTOR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH2.DEAL_BOND_ID) and
CPS.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH3
WHERE PX_SPD IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH3.DEAL_BOND_ID) and
CPSD.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH4
WHERE PX_SPD_DT IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH4.DEAL_BOND_ID) and
DMB.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH5
WHERE DISC_MRGN_BID IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH5.DEAL_BOND_ID) and
CWC.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH6
WHERE WAL_CALL IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH6.DEAL_BOND_ID) and
CT.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH7
WHERE TRMODDUR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH7.DEAL_BOND_ID)

You should be able to create sub-queries that select the latest data for each of the seven fields by the key and join those seven via the key using a where clause in each of the sub-queries to filter out where your respective field is not NULL. In the sub-queries, be sure to order by the record date and only select top 1. An extremely simplified example follows:

select
t.Key,
t1.Field1,
t2.Field2
from
Table t
inner join
(
select top 1
t.Field1
from
Table t
where
t.Key = <Parameter Value>
and Field1 is not null
order by
t.RecordDate
) t1
on
t1.Key = t.Key
inner join
(
select top 1
t.Field2
from
Table t
where
t.Key = <Parameter Value>
and t.Field2 is not null
order by
t.RecordDate
) t2
on t2.Key = t.Key
where
t.Key = <Parameter Value>

I don't think your example is working because you're asking for a certain row where the Record_Date field is equal to (possibly) seven different values at the same time, which is impossible.