select 
	(select max(rej_Start_date) from tbl_rej_info t2 where t2.ref_id = t1.ref_id and t2.spkid = 900) as latest_rej_start_date,
	(select max(date_completed) from tbl_ref_status t3 where t3.ref_id = t1.ref_id and t3.spkid = 300) as latest_date_completed,
	(select case when max(IsNull(date_completed, rej_flc_date)) from tbl_ref_status t3 where t3.ref_id = t1.ref_id and t3.spkid = 1800) as latest_transaction_date,
	(select min(rej_flc_date) from tbl_ref_status t3 where t3.ref_id = t1.ref_id and t3.date_completed is null) as next_action_date
from tbl_master t1

Declare @tbl_master Table 
(
	master_number int,
	ref_id int 
)

Insert @tbl_master 
Select 000465787,397  

Declare @tbl_rej_info Table
(
	ref_id int, 
	rej_start_date	Smalldatetime
)

Insert @tbl_rej_info 
Select 397,'11/04/2004' Union All 
Select 397,'05/05/2004'	Union All 
Select 397,'05/03/2004'	Union All 
Select 397,'02/05/2004'

Declare @tbl_ref_status Table 
(
	ref_id  int,
	status  varchar(10),
	rej_flc_date smalldatetime,   
	date_compeleted smalldatetime, 
	masterservice_id  int,
	spkid int 
)

Insert @tbl_ref_status
Select 397,'NEW','11/05/2004',NULL,127,900 Union All
Select 397,'NEW','12/05/2005',NULL,220,400 Union All
Select 397,'NEW','06/05/2005',NULL,403,300 Union All
Select 397,'NEW','12/12/2005',NULL,187,300 Union All
Select 397,'NEW','11/05/2004',NULL,127,900 Union All
Select 397,'NEW','01/05/2004',NULL,127,900 Union All
Select 397,'NEW','07/05/2004',NULL,127,400 Union All
Select 397,'NEW','07/05/2004',NULL,127,1800



Select * From 
(
	Select Max(rej_start_date) As latest_rej_start_date,tbl1.ref_id 
	From @tbl_ref_status tbl1 Inner Join @tbl_rej_info tbl2 on tbl1.ref_id = tbl2.ref_id 
	and tbl1.spkid = 900
	Group by tbl1.ref_id
) as A  
Inner Join 
(
	Select Max(IsNull(date_compeleted,rej_flc_date)) lates_transaction_date ,ref_id From  @tbl_ref_status 
	Where  spkid = 1800
	Group by ref_id 
) as B 
On A.ref_id = b.ref_id 
Inner Join 
@tbl_master  C on a.ref_id= c.ref_id
Where
master_number = 000465787