Hi,

All you have to do is to rank them on start_dt and self join with rank - 1 and you will get what you desire.

Regards, Adeel

or

sel ... 
min(End_Dt)
over (partition by empid
      order by start_dt
      rows between 1 following and 1 following) - 1 as end_dt
from tab

Dieter

Thank You Dieter, It helped me as well :)

small change in the query,

it worked as expected by using min(start_dt) instead of min(end_dt).

Thanks,

Karthik

Thank you All. I was able to sort out the issue in below 2 ways:

SQL1:

--------

UPDATE M
FROM
EMP M,
(Select
A.EMP_ID,A.Dept_No,A.STRT_DT ,CAST(B.STRT_DT AS DATE)-1
FROM
(Sel
EMP_NO,
DEPT_NO,
STRT_DT ,
END_DT ,
RANK() OVER  (PARTITION BY EMP_NO,DEPT_NO  ORDER BY STRT_DT  )
from EDW_STG.TEST_ITM_PRC
)A(EMP_NO,DEPT_NO,STRT_DT ,END_DT ,RANK1),
(Sel
EMP_NO,
DEPT_NO,
STRT_DT ,
END_DT ,
RANK() OVER  (PARTITION BY EMP_NO,DEPT_NO  ORDER BY STRT_DT  )
from EDW_STG.TEST_ITM_PRC
)B(EMP_NO,DEPT_NO,STRT_DT ,END_DT ,RANK2)
WHERE
A.EMP_NO=B.EMP_NO AND
A.DEPT_NO=B.DEPT_NO AND
A.RANK1=B.RANK2-1)N(EMP_NO,DEPT_NO,STRT_DT ,END_DT)
SET
END_DT=N.END_DT
WHERE
M.EMP_NO=N.EMP_NO AND
M.DEPT_NO=N.DEPT_NO AND
M.STRT_DT =N.STRT_DT ;
 

SQL 2:

--------

UPDATE M
FROM
EMP M,
(
 SELECT
V1.EMP_NO
,V1.DEPT_NO
,V1.STRT_DT
,(SELECT CAST(MIN ( V2.STRT_DT) AS DATE) -1  FROM EMP V2
WHERE V1.EMP_NO = V2.EMP_NO
AND V1.DEPT_NO = V2.DEPT_NO
AND V1.STRT_DT < V2.STRT_DT) 
FROM
EMP V1
WHERE V1.END_DT IS NULL
)N(EMP_NO,DEPT_NO,STRT_DT,END_DT)
SET END_DT = N.END_DT
WHERE M.STRT_DT = N.STRT_DT AND
   M.EMP_NO=N.EMP_NO AND
   M.DEPT_NO=N.DEPT_NO;

Thanks,

Ashok.