snap to scd using sql

Database

snap to scd using sql

Hi

  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?

 EMPNO ENAME JOB DW_EFF_DT

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

 EMPNO ENAME JOB DW_EFF_DT DW_EXPR_DT CURR_IN RCV_IN

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

4 REPLIES
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?

Dieter

Enthusiast

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,

Raja

KVB
Enthusiast

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..

KVB

Re: snap to scd using sql

yes you are right!!