How to implement scd type 2 for following

Question

We have data as following:

Fact table :

id | key   | name    | salary | loaction | start_date
1  | emp_1 | Shubham | 10000  | Delhi    | 1998-02-01
2  | emp_2 | Ashish  | 12000  | Pune     | 2000-01-01
3  | emp_3 | Shubham | 15000  | Mumbai   | 2002-01-01

Expected output:

id | key   | name    | salary | loaction | start_date | end_date   | inactive
1  | emp_1 | Shubham | 10000  | Delhi    | 1998-02-01 | 2002-01-01 | 0
2  | emp_2 | Ashish  | 12000  | Pune     | 2000-01-01 | null       | 1
3  | emp_3 | Shubham | 15000  | Mumbai   | 2002-01-01 | null       | 1

I want to implement this .

We have start date in fact table and emp_key which is unique and want to create dim table in which we have to show isactive flag and start date and end date. End date will be same as of start date of next location. Like in example for shubham 2002-01-01 is end date for delhi and start date for mumbai..

Please give solution for sql-server.

I have tried solution with Declaring variable and passing value but it is not feasible when we have thousands of rows..

You have no column named "emp_key". And you equate emp_3 with emp_1 based on name alone - you assume that there is only one person named Shubham? Rarely is that a safe assumption. What happens when a name changes (and yes - that does happen)? — SMor, Dec 31 '20 at 12:33

Tim Biegeleisen · Answer 1 · 2020-12-31T05:31:37.387

2

You may use the LEAD analytic function here:

SELECT
    id,
    [key],
    name,
    salary,
    location,
    start_date,
    LEAD(start_date) OVER (PARTITION BY [key] ORDER BY start_date) end_date,
    CASE WHEN LEAD(start_date) OVER
              (PARTITION BY [key] ORDER BY start_date) IS NOT NULL
         THEN 0 ELSE 1 END isactive
FROM yourTable
ORDER BY id;

Demo

Edit:

To save the above select into a new table, use:

SELECT (id, [key], name, salary, location, start_date,
        end_date, isactive) INTO yourNewTable
FROM (
    -- the exact select from above
) t;

edited Dec 31 '20 at 05:31

answered Dec 31 '20 at 05:18

Tim Biegeleisen

502,043
27
286
360

Sir i want to save this table also how this table will be saved??? – Dec 31 '20 at 05:28
@Street Use a select into, q.v. my updated answer above. – Tim Biegeleisen Dec 31 '20 at 05:31

How to implement scd type 2 for following

1 Answers1

Demo