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