RANK () OVER PARTITION

General

RANK () OVER PARTITION

Hi All,

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_CURR
1 4,057,919,973 11 0 8/31/2015 2 ? ?
2 4,057,919,973 11 1 9/30/2015 1 11 0
Tags (2)