All Forums Database
jsatish2008 3 posts Joined 09/13
27 Sep 2013
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

 

dnoeth 4628 posts Joined 11/04
27 Sep 2013

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

Dieter

Raja_KT 1246 posts Joined 07/09
30 Sep 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

KVB 124 posts Joined 09/12
02 Oct 2013

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

jsatish2008 3 posts Joined 09/13
02 Oct 2013

yes you are right!!

You must sign in to leave a comment.