Capture Multiple changes
Posted: Wed Dec 17, 2014 8:52 am
Hi
This is more like fixing the existing data.
Source: This is the data I am getting from source.
rno, bk, active_frm, active_to ,flag,org ,unit
1, 28023 29/08/2003 05/12/2003 N 25598 25598
2, 28023 06/12/2003 10/12/2003 N 25598 25598
3, 28023 11/12/2003 03/03/2004 N 1697959 1697959
4, 28023 04/03/2004 08/03/2004 N 1697959 1697959
5, 28023 09/03/2004 11/03/2004 N 1697959 1697959
6,28023 12/03/2004 29/12/2004 N 1697959 1697959
7, 28023 30/12/2004 07/03/2007 N 26427 5683717
8, 28023 08/03/2007 02/04/2007 N 26427 26427
9, 28023 03/04/2007 18/04/2007 N 26427 5683717
10, 28023 19/04/2007 04/12/2009 N 26427 26427
11, 28023 05/12/2009 31/12/2999 Y -3 -3
Rows 1, 2 have no changes for org and unit columns so I need to pick the latest active_to and close the first record.
ie., sk, bk, active_frm , active_to , flag, org, unit
100 28023 29/08/2003 10/12/2003 N 25598 25598
Rows 3, 4,5,6 have no changes for org and unit columns so I need to pick the latest active_to and close the first record.
101 28023 11/12/2003 29/12/2004 N 1697959 1697959
The rows 7,8,9,10,11 are correct. So I just have to insert them as they are.
My target should look like:
sk, bk, active_frm , active_to , flag, org, unit
100 28023 29/08/2003 10/12/2003 N 25598 25598
101 28023 11/12/2003 29/12/2004 N 1697959 1697959
102 28023 30/12/2004 07/03/2007 N 26427 5683717
103 28023 08/03/2007 02/04/2007 N 26427 26427
104 28023 03/04/2007 18/04/2007 N 26427 5683717
105 28023 19/04/2007 04/12/2009 N 26427 26427
106 28023 05/12/2009 31/12/2999 Y -3 -3
What I did:
1. Created checksum for bk, org , unit and used stage variables.
I was able to achieve if the group of same records are next to each other. But using this approach for rows 7,9 also get same checksum and spoils my approach.
Any advise is greatly appreciated.
Thanks in advance.
This is more like fixing the existing data.
Source: This is the data I am getting from source.
rno, bk, active_frm, active_to ,flag,org ,unit
1, 28023 29/08/2003 05/12/2003 N 25598 25598
2, 28023 06/12/2003 10/12/2003 N 25598 25598
3, 28023 11/12/2003 03/03/2004 N 1697959 1697959
4, 28023 04/03/2004 08/03/2004 N 1697959 1697959
5, 28023 09/03/2004 11/03/2004 N 1697959 1697959
6,28023 12/03/2004 29/12/2004 N 1697959 1697959
7, 28023 30/12/2004 07/03/2007 N 26427 5683717
8, 28023 08/03/2007 02/04/2007 N 26427 26427
9, 28023 03/04/2007 18/04/2007 N 26427 5683717
10, 28023 19/04/2007 04/12/2009 N 26427 26427
11, 28023 05/12/2009 31/12/2999 Y -3 -3
Rows 1, 2 have no changes for org and unit columns so I need to pick the latest active_to and close the first record.
ie., sk, bk, active_frm , active_to , flag, org, unit
100 28023 29/08/2003 10/12/2003 N 25598 25598
Rows 3, 4,5,6 have no changes for org and unit columns so I need to pick the latest active_to and close the first record.
101 28023 11/12/2003 29/12/2004 N 1697959 1697959
The rows 7,8,9,10,11 are correct. So I just have to insert them as they are.
My target should look like:
sk, bk, active_frm , active_to , flag, org, unit
100 28023 29/08/2003 10/12/2003 N 25598 25598
101 28023 11/12/2003 29/12/2004 N 1697959 1697959
102 28023 30/12/2004 07/03/2007 N 26427 5683717
103 28023 08/03/2007 02/04/2007 N 26427 26427
104 28023 03/04/2007 18/04/2007 N 26427 5683717
105 28023 19/04/2007 04/12/2009 N 26427 26427
106 28023 05/12/2009 31/12/2999 Y -3 -3
What I did:
1. Created checksum for bk, org , unit and used stage variables.
I was able to achieve if the group of same records are next to each other. But using this approach for rows 7,9 also get same checksum and spoils my approach.
Any advise is greatly appreciated.
Thanks in advance.