How to get the registers in the highest time of period

Question

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 historical report whether if the time of period is closed or active.

This is the code that I have or that I've 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'
    )

The thing is that the employee have 5 period of times that works in the company like 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 periods of time have different type of contracts.

What I want is that the just the contracts in the last period.

But the result with my query is the row with the data of the period 2 but not the contracts.

Thank for your help.

Answer 1

You are doing a left join that then includes a where clause that is filtering against the b table in the where clause. I'm guessing you want to say OR b.id IS NULL or something to that affect.

To confirm, comment out the where clause.