History Expiry issue
Posted: Tue Sep 27, 2011 2:42 am
History Expiry issue
Hi All
My input is sequtial file and the data is like this.
SK/natural key , Effective Date , Balance
20031 2015-09-07 1.33
20031 2015-09-08 4.2
20031 2015-09-09 6.9
I would like to have my target in the following table Assume History ( Composit Primary Key is 1) SK/natural key , 2) Record Arrival Date)
SK/natural key, Record Arrival Date, Record_Timestanp , Effective Date, Record End Date, Balance
20031 2015-09-07 CurrentTimestamp 2015-09-07 2015-09-08 1.33
20031 2015-09-08 CurrentTimestamp 2015-09-08 2015-09-09 4.2
20031 2015-09-09 CurrentTimestamp 2015-09-09 9999-12-31 6.9
1) Where I am assigning Effective DT = Record Arrival Date
2) When I recieve same SK/natural key in new reocrd Latest(different) "Effective Date" then
I need to Expire previous "Record End Date" with New record's "Effective Date"
3) At any time lastest record will have end date 9999-12-31
Can Any body Suggest solution for this issue ?
Hi All
My input is sequtial file and the data is like this.
SK/natural key , Effective Date , Balance
20031 2015-09-07 1.33
20031 2015-09-08 4.2
20031 2015-09-09 6.9
I would like to have my target in the following table Assume History ( Composit Primary Key is 1) SK/natural key , 2) Record Arrival Date)
SK/natural key, Record Arrival Date, Record_Timestanp , Effective Date, Record End Date, Balance
20031 2015-09-07 CurrentTimestamp 2015-09-07 2015-09-08 1.33
20031 2015-09-08 CurrentTimestamp 2015-09-08 2015-09-09 4.2
20031 2015-09-09 CurrentTimestamp 2015-09-09 9999-12-31 6.9
1) Where I am assigning Effective DT = Record Arrival Date
2) When I recieve same SK/natural key in new reocrd Latest(different) "Effective Date" then
I need to Expire previous "Record End Date" with New record's "Effective Date"
3) At any time lastest record will have end date 9999-12-31
Can Any body Suggest solution for this issue ?