I need to process a table which has records likes below into SD2 table. This will require reading the value of previous row and decide it its a match and it then process. If there are no changes in the score record is updated else its deleted and hence mark it terminated in SCD2.
NAME Score Deletion/Updation TIME Hemant 100 2012-08-01 Hemant 200 2012-08-10 Hemant 100 2012-08-11 Rahul 100 2012-08-01 Rahul 100 2012-08-02 Rahul 200 2012-08-03
Data need to loaded into table like this :
Name Value Valid From Valid TO Hemant 100 - 2012-08-01 Hemant 200 2012-08-01 2012-08-10 Hemant 100 2012-08-10 2012-08-11 Rahul 100 - 2012-08-02 Rahul 200 2012-08-02 2012-08-03
Cant use the group by function as same key value pair can be repeated on later dates or mutiple dates. Any suggestions will be highly appreciated.
Regards