I have to load the below data set onto a dimensional table, I need to check for Slowly Changing Dimension type 2 on Contribution column. There's historical data on the below dataset, I'm required to load all records including the historical data and then mark the last record active (RowIsCurrent = Y) and mark the others not active (RowIsCurrent = N, RowEndDate = GetDate()). The ClientID is the business key. What is the best way of handling this historical data?
Table structure:-
ClientID int,
Name varchar(20),
Contribution money,
EffectiveDate date,
RowisCurrent varchar(1),
RowEndDate datetime
Dataset
ClientID, Name, Contribution, EffectiveDate
1, John, 1000, 2012-12-31
1, John, 1100, 2013-01-31
1, John, 1300, 2013-03-31
1, John, 1400, 2013-10-31
