End Dating Previous Relations

Database
Enthusiast

End Dating Previous Relations

Hi All,

I have a requirement to end date all the previous relations.Can someone please help me with this:

For eg:

Input:

---------

Emp_Id  Dept_No    Start_Dt       End_Dt

9001        10          10-Jan-2011    Null     

9001        10          16-Jan-2011    Null

9001        10          19-Jan-2011    Null

9001        10          25-Jan-2011    Null

9001        10          31-Jan-2011    Null

I want the output to be

Emp_Id  Dept_No    Start_Dt       End_Dt

9001        10          10-Jan-2011    15-Jan-2011

9001        10          16-Jan-2011    18-Jan-2011

9001        10          19-Jan-2011    24-Jan-2011

9001        10          25-Jan-2011    30-Jan-2011

9001        10          31-Jan-2011    Null

Thanks in Advance,

Ashok.

4 REPLIES
Teradata Employee

Re: End Dating Previous Relations

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

Junior Contributor

Re: End Dating Previous Relations

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

Enthusiast

Re: End Dating Previous Relations

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

Enthusiast

Re: End Dating Previous Relations

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.