I am given the following table with the following problem:
Create a Slowly Changing Dimension Type 2 from the dataset. EMPLOYEE table has daily records for each employee. Type 2 - Will have effective data and expire date.
Employee ID | Date | Name | Manager ID |
---|---|---|---|
123 | 1-Mar | John Smith | 1 |
123 | 2-Mar | John Smith | 1 |
123 | 3-Mar | John Smith | 2 |
123 | 4-Mar | John Smith | 3 |
123 | 5-Mar | John Smith | 3 |
I believe my target table is supposed to look like this:
Employee ID | Name | Manager ID | Effective Date | Expiration Date |
---|---|---|---|---|
123 | John Smith | 1 | 1-Mar | 3-Mar |
123 | John Smith | 2 | 3-Mar | 4-Mar |
123 | John Smith | 3 | 4-Mar | Null |
I attempted the following query:
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
My resulting table is as follows:
Employee ID | Name | Manager ID | Effective Date | Expiration Date |
---|---|---|---|---|
123 | John Smith | 1 | 1-Mar | Null |
123 | John Smith | 1 | Null | 3-Mar |
123 | John Smith | 2 | 3-Mar | 4-Mar |
123 | John Smith | 3 | 4-Mar | Null |
123 | John Smith | 3 | Null | Null |
Does anyone know of any way that I can alter my query to achieve my target table, based on what I've achieved thus far? I somehow need to only result in the 3 Manager ID's but distinct will not work. Also, I need to find a way to combine the effective date and expiration date for each manager ID. Any help at all would be greatly appreciated.