I have a table that creates a new row of data each time an employees record is updated.
In the example below, I have an employee that has been hired, terminated, rehired, reterminated, and rehired again.
I would like for my query results to associate the employee terminate row with the appropriate hire row... meaning that the oldest terminate row would be tied to the oldest hire row, the second terminate date would be tied to the second hire row, etc...
Current Table Layout / SQL Results
ID EFFDT E_STATUS ACT
1234567 6/8/2005 A H
1234567 7/30/2005 T T
1234567 12/19/2005 A R
1234567 1/14/2006 T T
1234567 8/15/2006 A R
Desired SQL Results
ID EFFDT E_STATUS ACT EFFDT2 E_STATUS2 ACT2
1234567 6/8/2005 A H 7/30/2005 T T
1234567 12/19/2005 A R 1/14/2006 T T
1234567 8/15/2006 A R
Below is the code I have used to generate the Current SQL Results above. Please advise if there is a way to look for an and E_STATUS of 'T' and associate it with the first 'H' or 'R' EFFDT less than the 'T' EFFDT. Since there isn't a 'T' row for the last hire date, I would like those result to remain empty.
Hopefully this makes some sense.
select
a.id
,a.effdt
,a.e_status
,a.act
from
x_ps_job a
where
a.emplid = '1234567'
and a.act in ('H','R','T')
order by
a.id
In the example below, I have an employee that has been hired, terminated, rehired, reterminated, and rehired again.
I would like for my query results to associate the employee terminate row with the appropriate hire row... meaning that the oldest terminate row would be tied to the oldest hire row, the second terminate date would be tied to the second hire row, etc...
Current Table Layout / SQL Results
ID EFFDT E_STATUS ACT
1234567 6/8/2005 A H
1234567 7/30/2005 T T
1234567 12/19/2005 A R
1234567 1/14/2006 T T
1234567 8/15/2006 A R
Desired SQL Results
ID EFFDT E_STATUS ACT EFFDT2 E_STATUS2 ACT2
1234567 6/8/2005 A H 7/30/2005 T T
1234567 12/19/2005 A R 1/14/2006 T T
1234567 8/15/2006 A R
Below is the code I have used to generate the Current SQL Results above. Please advise if there is a way to look for an and E_STATUS of 'T' and associate it with the first 'H' or 'R' EFFDT less than the 'T' EFFDT. Since there isn't a 'T' row for the last hire date, I would like those result to remain empty.
Hopefully this makes some sense.
select
a.id
,a.effdt
,a.e_status
,a.act
from
x_ps_job a
where
a.emplid = '1234567'
and a.act in ('H','R','T')
order by
a.id