snap to scd using sql


snap to scd using sql


  I have a snapshot table like below.I require it to convert it into SCD like the other table.Could you please guide me on this using SQL?


1 1 BOB SE 2013-09-24

2 1 BOB SE 2013-09-27

3 1 BOB SE 2013-09-26

4 1 BOB SE 2013-09-25

5 2 JACK SSE 2013-09-27

6 2 JACK SE 2013-09-24

7 2 JACK SSE 2013-09-26

8 2 JACK SE 2013-09-25

9 3 JILL SSE 2013-09-25

10 3 JILL SSE 2013-09-24


1 1 BOB SE 2013-09-24 9999-12-31 1 1

2 2 JACK SE 2013-09-24 2013-09-25 0 1

3 2 JACK SSE 2013-09-26 9999-12-31 1 1

4 3 JILL SSE 2013-09-24 2013-09-25 0 1

5 3 JILL SSE 2013-09-26 9999-12-31 1 0

Senior Apprentice

Re: snap to scd using sql

Can you alaborate on the rules for combining rows?

Why are there 2 rows for JILL and how are CURR_IN and RCV_IN calculated?



Re: snap to scd using sql

To maintain scd and maintain history of changes, you need to identify what are the column(s) that are changing( It can be  the job). Say an employee changes his job from SE to SSE. You can keep start date and end date of his/her job and or also you can add fields such as indicator(s),  status(es). 

For  it JILL  it is only one row. Not sure how do you want to represent CURR_IN and RCV_IN.

Thanks and regards,



Re: snap to scd using sql

As per my understanding,

the updated records have CURR_IN=0 and RCV_IN=1.

For active records,CURR_IN=1 and RCV_IN=1 and

for deleted records CURR_IN=1 and RCV_IN=0.

I have done this and strucked with a delete logic..


Re: snap to scd using sql

yes you are right!!