All Forums Database
Ashok.Pentapati 16 posts Joined 06/09
02 Dec 2011
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.

 

 

Adeel Chaudhry 773 posts Joined 04/08
05 Dec 2011

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

 

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
05 Dec 2011

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

Dieter

karthik_2244 36 posts Joined 12/10
05 Dec 2011

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

Ashok Pentapati 20 posts Joined 07/11
05 Dec 2011

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.

 

You must sign in to leave a comment.