How to show latest status from 3 table

Hello ,
First I create three temporary table for explanation .

create table #tmp1
(
referenceno varchar(10),
statuscode varchar(20),
)
create table #tmp2
(
referenceno varchar(10),
statuscode varchar(20),
)
create table #tmp3
(
referenceno varchar(10),
statuscode varchar(20),
)

--Now insert data 
insert into #tmp1 values('123', 'pending')
insert into #tmp1 values('234', 'pending')
insert into #tmp1 values('345', 'pending')
insert into #tmp1 values('456', 'pending')
insert into #tmp1 values('567', 'pending')
insert into #tmp1 values('678', 'pending')


insert into #tmp2 values('345', 'going')
insert into #tmp2 values('456', 'going')
insert into #tmp2 values('567', 'going')


insert into #tmp3 values('456', 'done')
insert into #tmp3 values('567', 'done')
insert into #tmp3 values('678', 'done')

to get required OP , first make union all of those three tables .
and then use Row_Number with Partition by clause, to sequence them one by one by statuscode and lastly use Common Table Expression to get required OP .

;with CTE
as
(
select ROW_NUMBER() OVER (partition by referenceno  ORDER BY statuscode) AS RowNumber , *
from 
        (
select * from #tmp1 
union all
select * from #tmp2
union all
select * from #tmp3
)xx)

select referenceno , statuscode from cte  where RowNumber=1

OP

referenceno	statuscode
123	pending
234	pending
345	going
456	done
567	done
678	done

Here another solution that may help you without using CTE
First Select only those records from #tmp1 or first table which are not present in other two tables (#tmp2 and #tmp3)
again select only those records from #tmp2 or second table which are not present in
last table (#tmp3)
and lastly make union all between those records sets .

select referenceno , statuscode from #tmp1 where referenceno
not in 
(
select referenceno from #tmp2
union all
select referenceno from #tmp3
)
union all

select referenceno , statuscode  from #tmp2 where referenceno
not in 
(
select referenceno from #tmp3
)
union all
select referenceno , statuscode  from #tmp3

Here also OP is

referenceno	statuscode
123	pending
234	pending
345	going
456	done
567	done
678	done

Thanks