How to get the registers in the highest time of period


I have 2 tables all with relations and i want to get the contracts in the highest time of period in order to do a hystorical report wheter if the tima of period is closed or active

This is the code that i have or that ive been trying

Select a.id, a.ordinalperiod,b.name, a.fecin,a.fecend,b.dtend,a.contrato

From historiccontrat a Left join periodo b On a.id=b.id And a.ordinalperiod=b.ordinalperiod Where a.fecend in(select max(b.dtend)where a.id=b.id and a.ordinalperiod=b.ordinalperiod and b.dtend <>’4000-01-01′)

Th thing is that the employee have 5 period of times taht works in the company liket this

Id período dt_start dt_end 000 1 2012-01-01 2012-04-01

000 2 2013-05-01 2016-04-03

…. …. ….

And in each of these períods of time have diferente type of contrats

With this say what i want is that the filter show me Just the contrats in the Last period

But the result with my query is the row with the data of the period 2 but no the contrats

Thank for your help