I have a table without dates and wish to join on the table with dates.I am doing a left join on id and bn\_number. The id can have more than one dates , i obviously want the latest date from the other tables as it has more than one date for each id. i am not sure how to get all the dates at least then i can be able to choose the latest one. 

```
select Reg_Property_id,a.Bnd_nbr,account_balance,abs(account_balanc‌​e) as Bond_Balance,a.Bnd_regDate 
into #Jan2014ValidFin 
from #Jan2014Valid aa 
left join Pr_analytics..bond a 
    on aa.Reg_Property_id=a.Prop_id 
    and aa.bnd_nbr=a.Bnd_nbr 
where aa.reg_property_id is not null 

```

SQL 

Please assist.