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.
Best Answer
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.