0

I'm trying to create a SCD type 2 from the table below using SQL.

enter image description here

click on table for better visibility if neededAs shown, The table has daily records for each employee. I want to create a type 2 that will contain an effective date and expiration date.

Is the easiest way to do this, using SQL, by using a MERGE? if so, how would I go about it in a way that retains the integrity of the data?

Thank you for any help!

EDIT:

I was not given a specified target table however I believe my following table should look like the following:

enter image description here

click on table for better visibility if neededI also think another possibility would be to utilize the LEAD() and LAG() functions to achieve the result but after implementing the following code, I am very close to my desired result, but not quite what I'm needing. My code and resulting table is below:

SELECT employee_id, name, manager_id,
CASE
    WHEN LAG(manager_id) OVER() != manager_id THEN e.date 
    WHEN e.date = FIRST_VALUE(e.date) OVER() THEN e.date
    ELSE NULL
END as "Effective Date",
CASE 
    WHEN LEAD(manager_id) OVER() != manager_id THEN LEAD(e.date) OVER()
    ELSE NULL
END as "Expiration Date"
FROM employee e

enter image description here

(click on table for better visibility if needed)

LukeThomas
  • 75
  • 1
  • 1
  • 6
  • I removed the inconsistent database tags. Please tag only with the database you are really using. – Gordon Linoff Mar 20 '21 at 12:50
  • Hi - please provide the target dataset, based on the source data you have provided, you are trying to achieve (there is more than 1 way to structure an SCD2 table). Also please show what you have already tried - you are likely to get a better response if you can show you have at least attempted to solve this yourself – NickW Mar 20 '21 at 21:29
  • The target table will have an effective date (the date of the new manager ID) and the expiration date (the date the employee changed manager ID's) – LukeThomas Mar 21 '21 at 02:43

0 Answers0