SQL Query for SCD Type 2

Question

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.

Answer 1

Set up schema

Use the following query to setup an employees table in sql server (or RDBMS of your choice with some syntax mods)

create table employees (EmployeeID int, [Date] date, [Name] varchar(32), ManagerID int);

insert into employees (EmployeeID, [Date], [Name], ManagerID)
values
(123, '1 Mar 2021', 'John Smith', 1),
(123, '2 Mar 2021', 'John Smith', 1),
(123, '3 Mar 2021', 'John Smith', 2),
(123, '4 Mar 2021', 'John Smith', 3),
(123, '5 Mar 2021', 'John Smith', 3)

Walkthrough of Solution

Step 1: Ascertain the Start Dates

Transform the Employees table to show the start date for each manager an employee has worked under.

    SELECT EmployeeID, Name, ManagerID, MIN(Date) AS start_date
    FROM test e
    GROUP BY EmployeeID,Name,ManagerID

This returns:

EmployeeID	Name	ManagerID	start_date
123	John Smith	1	2021-03-01
123	John Smith	2	2021-03-03
123	John Smith	3	2021-03-04

Step 2: Adding end_date

Use this table to calculate a column that displays the end date for the given employee - manager pair. Using the concept that the start_date of an employee working with one manager, is the end date of the employees tenure with their previous manager.

Use the LEAD function and create partitions over Employee, making sure to order by start_date to create a column that represents the end_date of an employee - manager pair.

See below how we use the first query to generate a CTE called t1, then build the end_date column in a query carried out against t1.

WITH t1 AS (
    SELECT EmployeeID, Name, ManagerID, MIN(Date) AS start_date
    FROM test e
    GROUP BY EmployeeID,Name,ManagerID
)

SELECT *, 
LEAD(start_date) OVER (PARTITION BY EmployeeID ORDER BY start_date) AS end_date
FROM t1

Final rowset matches desired ouput:

EmployeeID	Name	ManagerID	start_date	end_date
123	John Smith	1	2021-03-01	2021-03-03
123	John Smith	2	2021-03-03	2021-03-04
123	John Smith	3	2021-03-04	NULL

Further Test

To be safe, test the case where there is more than one employee in input dataset.

insert into employees (EmployeeID, [Date], [Name], ManagerID)
values
(133, '1 Mar 2021', 'Sean Smith', 1),
(133, '2 Mar 2021', 'Sean Smith', 2),
(133, '3 Mar 2021', 'Sean Smith', 3),
(143, '4 Mar 2021', 'Mark Smith', 3),
(143, '5 Mar 2021', 'Mark Smith', 4)

The output shows our solution passed the test:

EmployeeID	Name	ManagerID	start_date	end_date
123	John Smith	1	2021-03-01	2021-03-03
123	John Smith	2	2021-03-03	2021-03-04
123	John Smith	3	2021-03-04	NULL
133	Sean Smith	1	2021-03-01	2021-03-02
133	Sean Smith	2	2021-03-02	2021-03-03
133	Sean Smith	3	2021-03-03	NULL
143	Mark Smith	3	2021-03-04	2021-03-05
143	Mark Smith	4	2021-03-05	NULL

Answer 2

from pyspark.sql import SparkSession
spark=SparkSession.builder.appName('test').getOrCreate()

l1=[[123,'John Smith','1-March-2022',1],[123,'John Smith','2-March-2022',1],[123,'John Smith','3-March-2022',2],[123,'John Smith','4-March-2022',3],[123,'John Smith','5-March-2022',3]]
col=['empid','name','date','mgr']

df=spark.createDataFrame(l1,col)

df.createOrReplaceTempView('tempview')

spark.sql("""select empid,name,mgr,effective_from, lead(effective_from) over (partition by empid order by effective_from ) as effective_to from  
                (select empid,name,mgr ,min(date) as effective_from,max(date) as effective_to from tempview group by mgr,empid,name)""").show()

Answer 3

Use this, this will be simpler.

Explanation: The nested query will give the rows where there is change in managers, filter out rest of the rows as it is redundant information.

Once the data is filtered, find the next date when the manager got changed, mark that data as end date

SELECT 
   EmployeeID, 
   Name, 
   ManagerID, 
   [Date] StartDate, 
   Lead([Date]) OVER (ORDER BY [Date])  EndDate  
FROM 
    (SELECT *, lag(ManagerID,1,-1) OVER (ORDER BY [Date]) p_mgid FROM #temp ) s  
WHERE ManagerID <>p_mgid;
    ;

Answer 4

create table test  (EmployeeID int, [Date] date, [Name] varchar(32), ManagerID int);

insert into Test (EmployeeID, [Date], [Name], ManagerID)
values
(123, '1 Mar 2021', 'John Smith', 1),
(123, '2 Mar 2021', 'John Smith', 1),
(123, '3 Mar 2021', 'John Smith', 2),
(123, '4 Mar 2021', 'John Smith', 3),
(123, '5 Mar 2021', 'John Smith', 3)

select a.employeeid,a.name,a.managerid,a.effective as effective_date,
lead(effective) over(partition by employeeid order by maximum) as expiration_date 
from (select employeeid,min(date) as effective,max(date)as maximum,name,managerid from test
group by employeeid,name,managerid) a

Answer 5

declare @Test table (EmployeeID int, [Date] date, [Name] varchar(32), ManagerID int);

insert into @Test (EmployeeID, [Date], [Name], ManagerID)
values
(123, '1 Mar 2021', 'John Smith', 1),
(123, '2 Mar 2021', 'John Smith', 1),
(123, '3 Mar 2021', 'John Smith', 2),
(123, '4 Mar 2021', 'John Smith', 3),
(123, '5 Mar 2021', 'John Smith', 3);
--(123, '6 Mar 2021', 'John Smith', 2);


;WITH CTE AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY NAME,MANAGERID ORDER BY DATE) RW FROM @TEST
)
SELECT EmployeeID,NAME,ManagerID,DATE AS FROMDATE ,(SELECT DATE FROM CTE B WHERE A.RW =B.RW AND B.ManagerID = A.ManagerID +1) AS ENDDATE
FROM CTE A 
WHERE RW=1

Answer 6

The following does what you require, and shows how to add DDL+DML as well. Its probably a bit convoluted but I can't see an obvious way to simplify it.

This solution takes into account the possibility that the manager could repeat. And it doesn't assume that ever day will exist, so if a day is missing it will still work.

declare @Test table (EmployeeID int, [Date] date, [Name] varchar(32), ManagerID int);

insert into @Test (EmployeeID, [Date], [Name], ManagerID)
values
(123, '1 Mar 2021', 'John Smith', 1),
(123, '2 Mar 2021', 'John Smith', 1),
(123, '3 Mar 2021', 'John Smith', 2),
(123, '4 Mar 2021', 'John Smith', 3),
(123, '5 Mar 2021', 'John Smith', 3);
--(123, '6 Mar 2021', 'John Smith', 2);

select EmployeeId, [Name], ManagerId, MinDate
  -- Use lead to get the last date of the next grouping - since it could in theory be more than one day on
  , lead(MinDate) over (partition by EmployeeId, [Name] order by Grouped) MaxDate
from (
  -- Get the min and max dates for a given grouping
  select EmployeeId, [Name], ManagerId, min([Date]) MinDate, max([Date]) MaxDate, Grouped
  from (
    select *
       -- Sum the change in manager to ensure that if a manager is repeated they form a different group
       , sum(Lagged) over (order by Date asc) Grouped
    from (
      select *
        -- Lag the manager to detect when it changes
        , case when lag(ManagerId,1,-1) over (order by [Date] asc) <> ManagerId then 1 else 0 end Lagged
      from @Test
    ) X
  ) Y
  group by EmployeeId, [Name], ManagerId, Grouped
) Z
order by EmployeeId, [Name], Grouped;

Returns:

EmployeeId	Name	ManagerId	MinDate	MaxDate
123	John Smith	1	2021-03-01	2021-03-03
123	John Smith	2	2021-03-03	2021-03-04
123	John Smith	3	2021-03-04	NULL