I am have a table with undergoes CDC type 2. With the column Effective start date used for previous/inactive record.
Now i need a record with columns ACTIVE_PREV and ACTIVE_CURR (examples) along with their previous values as shown below.
ACTIVE_CURR and ACTIVE_PREV for 9/30/2015 with previous values as INCATIVE_CURR and INACTIVE_PREV in the same row as shown.
I have added rank column as below. I want the values of Rank 2 in the row of Rank 1. I cant self join as later i need rank 3 as well.
RANK () OVER (PARTITION BY A.CQNF601_ACCT_NBR ORDER BY A.EFFECTIVE_START_DATE DESC) AS RNK
Let me know if there is a way by which i can acheive this.
Can upload the sample query of you need.
ACCOUNT_NUMBER ACTIVE_PREV ACTIVE_CURR EFFECTIVE_START_DATE RNK INACTIVE_PREV INACTIVE_CURR1 4,057,919,973 11 0 8/31/2015 2 ? ?2 4,057,919,973 11 1 9/30/2015 1 11 0