Update statement using self join

Database
Enthusiast

Update statement using self join

Hi Experts,

I hava an employee table which effective date and post effective date. post_eff_dt is next eff_dt for that employee.

So if 31-jan-2013 is first row and 28-feb-2013 is second row then row should look like this

EMP    EFFDT          POST_EFFDT

27227    31-jan-2013  28-feb-2013

27227    28-feb-2013  2999-12-31 (High date for latest record)

Now if same effdt has multiple rows, then I am facing problem.

My data is looking like (set A)

27227  1/3/2012   1/3/2012

27227  1/3/2012   31/12/2999

27227 1/10/2011  1/10/2011

27227 1/10/2011  1/3/2012

27227 1/1/2007    1/10/2011

27227 1/1/2007    1/1/2007

27227 21/09/2006 1/1/2007

27227 21/09/2006 21/09/2006

and it should be, (SET B)

27227 1/3/2012     31/12/2999

27227 1/3/2012     31/12/2999

27227 1/10/2011    1/3/2012

27227 1/10/2011    1/3/2012

27227 1/1/2007     1/10/2011

27227 1/1/2007     1/10/2011

27227 21/09/2006  1/1/2007

27227 21/09/2006  1/1/2007

Can you pls provide the update statement to get SET B from SET A.

Thanks in advance,

2 REPLIES
Enthusiast

Re: Update statement using self join

sel id,effdate, max(post_eff_date) over (partition by id, effdate) as postdate from tblname;

Enthusiast

Re: Update statement using self join

Thanks Mohan :-).. it was quick..