need update logic

General
Enthusiast

need update logic

My Source table is 

SEQ_NBR           EFF_STRT_DT         EFF_END_DT        M_SYS_DT

     4                        11/26/2015        12/31/9999         11/27/2012

     3                        11/26/2015        12/31/9999         11/3/2009

     2                        11/26/2015        12/31/9999         11/2/2009

     1                        11/26/2015         12/31/9999        7/22/2009

I Need load the data into target as 

SEQ_NBR           EFF_STRT_DT         EFF_END_DT         M_SYS_DT

     4                        11/27/2012        12/31/9999        11/27/2012

     3                        11/3/2009         11/27/2012          11/3/2009

     2                        11/2/2009         11/3/2009            11/2/2009

     1                        7/22/2009          11/2/2009          7/22/2009

i am writing my query as M_SYS_DT as EFF_STRT_DT   and EFF_END_DT  as MAX(SEQ_NBR ) is 12/31/9999

And can you please tell me how to udpate the EFF_END_DT whose SEQ_NBR  is not max ... I need to update logic generic way so that there  may be new records (SEQ_NBR) .. it will be in increasing order.(1,2,3,4,5....)

please send me the logic....

Tags (1)
1 REPLY
Enthusiast

Re: need update logic

The below select query will give you the desired output, let me know if this doesn't work.

SEL 

SEQ_NBR

,M_SYS_DT AS EFF_STRT_DT

,COALESCE(MAX(M_SYS_DT) OVER (ORDER BY SEQ_NBR DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), EFF_END_DT) AS EFF_END_DT

,M_SYS_DT

FROM TableName ORDER BY 1 DESC;