sql loop or self join

Question

I have a dimension table that is a type 2 scd like this:

Empl	job_id	old_job_id
12345	101	98
12345	120	101
12345	130	120

I need to know the current job_id like this:

Empl	job_id	current_job_id
12345	101	130
12345	120	130
12345	130	130

can this be done with a sql query in a view?

I have tried a self join but that doesn't give me the current view for all records and there is not easy way I can think of to identify how many old jobs an employee has.

Also, I found another related issue. |job_id | old_job_id | job_desc| |-----|--------|---------------| |100| NULL| CEO| |110| NULL| Regional Mgr| |120| 110| Regional Manager| |130| NULL| Salesperson|

I'm looking to have this |job_id | Current_job_id| Current_job_desc| |-----|--------|---------------| |100| 100| CEO| |110| 120| Regional Manager| |120| 120| Regional Manager| |130| 130| Salesperson|

thanks for the assistance.

Answer 1

Something like :

WITH 
T AS 
(
 SELECT Empl, job_id, old_job_id, 0 AS DEEP
 FROM   "I have a dimension table" AS TR
 WHERE NOT EXISTS(SELECT * 
                  FROM   "I have a dimension table" AS TP 
                  WHERE TP.old_job_id = TR.job_id)
 UNION ALL
 SELECT T.Empl, TF.job_id, TF.old_job_id, DEEP + 1
 FROM   T  
        JOIN "I have a dimension table" AS TF
           ON T.Empl = TF.Empl AND TF.old_job_id = T.job_id
),
F AS
(
 SELECT Empl, job_id, old_job_id, 
        ROW_NUMBER() OVER(PARTITION BY Empl ORDER BY DEEP DESC) AS N
 FROM   T
)
SELECT J.Empl, J.job_id, F.job_id  
FROM   "I have a dimension table" AS J
       JOIN F ON J.Empl = F.Empl
WHERE  N = 1;

Please respect the post chart by giving table's DDL and some INSERT to reproduce your trouble...