SCD / Change Capture Logic

[adityavarma]

Hi,

I have an requirement where i have to implement as below

Source:

ID Name Sin Sex DOB Martial Status Last Updated Field change
1001 ABC 12344 M 19320825 S 19500610 NAME
1001 CAB 12344 M 19320825 S 19510610 NAME
1001 ABC 12344 M 19320825 M 19520610 MARITAL STATUS
1001 ABC 92344 M 19320825 M 19940101 SIN



Target:

ID Field name Old Value New Value
1001 Name ABC CAB
1001 MARITAL STATUS S M
1001 SIN 12344 92344

can you please help me on this ?

[vamsi.4a6]

please tell me your requirement in the form of sentences.

[adityavarma]

We have source records with columns: ID, Name,Address, DateofBirth, SIN, marital Status and also one columns to indicate field change ( which field is changed)

source records contains as below

ID Name SIN Sex DOB Martial Status Last Updated Field
1001 CAB 12344 M 19320825 S 19510610 NAME
1001 ABC 12344 M 19320825 M 19520610 MARITAL STATUS
1001 ABC 92344 M 19320825 M 19940101 SIN

For any particular ID , there will be an change in address or SIN or Marital Status data and that column is specified in the column field

In the output we need to capture the results for the particular ID, the OLD value and new value along with the field

ID Field name Old Value New Value
1001 Name ABC CAB
1001 MARITAL STATUS S M
1001 SIN 12344 92344

[chulett]

So where are you supposed to get the old value for the first change that comes along? Your example output isn't making sense to me as it seems to show values derived from going... both directions... through the list of changes.

ps. Don't use "BBCode" in your post and then disable that option at the same time. I edited your post to remove the check-mark from the disable option which is why your two field values are showing up in bold now. FYI.

[adityavarma]

Sorry my bad.. will keep the BBCode in mind

For the result, the old value for the first record will be Null
ID Field name Old Value New Value
1001 Name Null CAB
1001 MARITAL STATUS S M
1001 SIN 12344 92344