Taking more time to execute - Pls Optimize

[VenkatSQL]

Hi experts

The below query took more time to complete the process

SELECT
SCC ->table Fields Value,
SRD ->table Fields Value
SCE ->table Fields Value,
SRH ->table Fields Value,
SVC ->table Fields Value

FROM
SCS_CONTRACTS SCC
INNER JOIN SRD -- Table 1
ON SCC.icontract_id = SRD.icontract_id
INNER JOIN SCE -- Table 2
ON SCC.icontract_id = SCE.icontract_id
inner JOIN
(
select scc.icontract_id, coalesce(sum(svc.camount),0) AS camount
from SCC
LEFT JOIN SVC
ON SCC.icontract_id = SVC.icontract_id
where scc.dtupdate_last >= '2007-09-01 00:00:00.000' and scc.dtupdate_last <='2007-09-28 23:59:59.997'
and scc.sstatus in ('A','C','E')
Group by SCC.icontract_id
) SVC
ON SCC.icontract_id = SVC.icontract_id
---------------------*********
-- The below mentioned sub query is problem
---------------------*********
-- We have to compare the SRH table with contract_id and from the SRH table we have to take the field values which is lastly updated.. For example for single contract id say 2001 we may have 4 or 5 records in SRH table. We have to take thw value which is lastly updated
---------------------*********

INNER JOIN
(
select SRH.icontract_id,
SRH.cadj_prem_rate_1 , SRH.cadj_prem_rate_2 , SRH.cadj_prem_rate_3 , SRH.cadj_prem_rate_4 ,
SRH.cadj_admin_rate_1 , SRH.cadj_admin_rate_2 , SRH.cadj_admin_rate_3 , SRH.cadj_admin_rate_4 ,
SRH.cadj_admin_rate_5 , SRH.cadj_admin_rate_6 , SRH.cadj_admin_rate_7 , SRH.cadj_admin_rate_8 ,
SRH.cadj_admin_rate_9 , SRH.cadj_admin_rate_10
from scc
left join
(
select srh.* from SRH,
(select srh.icontract_id,
max(srh.dtupdate_last)as dtupdate_last
from Srh
where srh.dtupdate_last >= '2007-09-01 00:00:00.000' and srh.dtupdate_last <='2007-09-28 23:59:59.997'
group by srh.icontract_id ) sk1
where
(SRH.icontract_id = sk1.icontract_id and SRH.dtupdate_last = sk1.dtupdate_last)
)
SRH
on Scc.icontract_id = SRH.icontract_id
where (scc.dtupdate_last >= '2007-09-01 00:00:00.000' and scc.dtupdate_last <='2007-09-28 23:59:59.997'
and scc.sstatus IN ('A','C','E'))
)SRH
ON SRD.icontract_id = SRH.icontract_id
where scc.dtupdate_last >= '2007-09-01 00:00:00.000' and scc.dtupdate_last <='2007-09-28 23:59:59.997'
and scc.sstatus IN ('A','C','E')
and sce.iearn_set_id = 1


Please optimize the query.....

thanks in advance

s.v