Database Administrators Asked by Wilson Eduardo Millan Vargas on November 14, 2021
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.
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.
Answered by Doug B on November 14, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP