SQLTeam.com | Weblogs | Forums

MERGE INTO with SCD Type2 FROM Historical Table


#1

I'm trying to see if we can use merge statement to create a SCD Type2 from the Transactional status history table. This will be a one time Type2 target table and then use the Merge statement to find the delta and load it every day.

Below Query creates the Emp Status History tables

SELECT 5 AS ChangeHistID,5513 AS EmpID, 'Not Applied' AS Status,'2015-03-28 21:47:41.637' AS StatusChangeDate into #EmpStatusHist UNION ALL
    SELECT 30 AS ChangeHistID,5513 AS EmpID, 'Applied' AS Status,'2015-03-29 21:47:41.637' AS StatusChangeDate UNION ALL
    SELECT 9951 AS ChangeHistID,5513 AS EmpID, 'Listed' AS Status,'2015-03-30 22:47:41.637' AS StatusChangeDate UNION ALL
    SELECT 1978 AS ChangeHistID,5513 AS EmpID, 'Phone Screen' AS Status,'2015-03-30 23:47:41.637' AS StatusChangeDate 

insert into #EmpStatusHist
SELECT 210 AS ChangeHistID,11233 AS EmpID, 'Not Applied' AS Status,'2015-04-23 21:47:41.637' AS StatusChangeDate UNION ALL
SELECT 21432 AS ChangeHistID,11233 AS EmpID, 'Applied' AS Status,'2015-04-28 21:47:41.637' AS StatusChangeDate UNION ALL
SELECT 34754 AS ChangeHistID,11233 AS EmpID, 'Not Listed' AS Status,'2015-04-28 22:47:41.637' AS StatusChangeDate UNION ALL
SELECT 35172 AS ChangeHistID,11233 AS EmpID, 'Completed' AS Status,'2015-04-29 21:47:41.637' AS StatusChangeDate 

insert into #EmpStatusHist
SELECT 311 AS ChangeHistID,1249 AS EmpID, 'Not Applied' AS Status,'2015-02-16 21:47:41.637' AS StatusChangeDate UNION ALL
SELECT 327 AS ChangeHistID,1249 AS EmpID, 'Applied' AS Status,'2015-02-20 21:47:41.637' AS StatusChangeDate UNION ALL
SELECT 14510 AS ChangeHistID,1249 AS EmpID, 'Listed' AS Status,'2015-03-13 21:47:41.637' AS StatusChangeDate UNION ALL
SELECT 25507 AS ChangeHistID,1249 AS EmpID, 'Background' AS Status,'2015-03-15 21:47:41.637' AS StatusChangeDate 

The below query helps me to set the SCD Type2 for a first time.

WITH EmpStatusHist AS 
(
SELECT *,
	ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY StatusChangeDate ASC) AS StatusChangeFTL
FROM #EmpStatusHist
)
 SELECT 
	 Curr.ChangeHistID,
	 Curr.EmpID,
	 Prev.Status AS PreviousStatus,
	 Curr.Status AS CurrentStatus,
	 Curr.StatusChangeDate As StatusChangeDate,
	 CASE WHEN Prev.Status IS NULL THEN CONVERT(DATETIME,'19000101') ELSE Curr.StatusChangeDate END [EffectiveStartDate],
	 COALESCE(Nxt.StatusChangeDate,CONVERT(DATETIME,'99991231')) AS [EffectiveEndDate],
	 CONVERT(BIT,CASE WHEN Nxt.StatusChangeDate IS NULL THEN 1 ELSE 0 END) AS CurrentRecordFlg
FROM EmpStatusHist Curr
			LEFT JOIN EmpStatusHist Nxt ON
							Curr.EmpID = Nxt.EmpID AND
							Curr.StatusChangeFTL + 1 = Nxt.StatusChangeFTL
			LEFT JOIN EmpStatusHist Prev ON
							Curr.EmpID = Prev.EmpID AND
							Curr.StatusChangeFTL - 1 = Prev.StatusChangeFTL

Now Day2 if I get the below records is there any way to use the MERGE INTO Statement. The challenge is : there will be multiple records for the same EmpID with the same date but different timestamp.Also the last record should be only turned to 1 for CurrentRecordFlag

Insert into #EmpStatusHist

SELECT 3425 AS ChangeHistID,5513 AS EmpID, 'Not Selected' AS Status,'2015-04-29 14:47:41.637' AS StatusChangeDate UNION ALL
SELECT 29816 AS ChangeHistID,5513 AS EmpID, 'Completed' AS Status,'2015-04-29 21:47:41.637' AS StatusChangeDate UNION ALL
SELECT 46664 AS ChangeHistID,1249 AS EmpID, 'Phone Screen' AS Status,'2015-04-28 21:47:41.637' AS StatusChangeDate UNION ALL  
SELECT 43715 AS ChangeHistID,1249 AS EmpID, 'Selected' AS Status,'2015-04-28 22:47:41.637' AS StatusChangeDate UNION ALL
SELECT 49715 AS ChangeHistID,1249 AS EmpID, 'Interview' AS Status,'2015-04-29 21:47:41.637' AS StatusChangeDate

#2

I tried MERGE INTO but finding difficulty to get the minimum date of the next record of the Day2 to expire the CurrentRecordFlag. Please help if there are any other approaches using the MERGE statement