SCD TYPE 2 Implementation

Question

Needs to implement SCD TYPE 2 Table in SQL server.

Which insert new record and update timestamp if Threshold Values changes for particular Band.

If no changes found then doesn't touch the entry.

Band	Value	StartDt	Enddt
Junior	2000	1-1-2021	31-12-9999
Senior	3000	1-1-2021	31-12-9999

BAND	Value
Junior	2500
Senior	3000

Now I wants to update and insert Junior Record

Band	Value	StartDt	Enddt
Junior	2000	1-1-2021	Getdate() - 1
Junior	2500	Getdate()	31-12-9999
Senior	3000	1-1-2021	31-12-9999

Looking for results.

Answer 1

USE [DBTest]
GO

/****** Object:  StoredProcedure [dbo].[LOAD_IVA_Agent_Master]    Script Date: 24-08-2023 15:31:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LOAD_IVA_Agent_Master]
AS
BEGIN
BEGIN TRY

DECLARE @cmd AS VARCHAR(1000)

PRINT('At 1')
DROP table IF EXISTS #STG_VS_Threshold

Select * into #STG_VS_Threshold from Threshold

Print('Complete 2')

SELECT 
HASHBYTES('MD5', CONCAT(
ISNULL(BAND,'NA') ,
ISNULL(StartVal,0),
ISNULL(EndVal,0) 
) )  AS MD5,
* ,Getdate() as Update_Date, CAST('9999-12-31' AS DATE) AS EFF_END_DT into #V_STG_VS_Agent_Master FROM #STG_VS_Threshold

Print('Complete 3')
DROP table IF EXISTS STG_VS_Agent_Master_BKP 
Select * into STG_VS_Agent_Master_BKP from #V_STG_VS_Agent_Master

UPDATE MAIN   
SET EFF_END_DT = GETDATE()-1
FROM STG_VS_Agent_Master AS MAIN
WHERE EXISTS (SELECT 1 FROM #V_STG_VS_Agent_Master STG WHERE (STG.Band = MAIN.Band) AND STG.MD5 <> MAIN.MD5 AND MAIN.EFF_END_DT = CAST('9999-12-31' AS DATE) )
   

INSERT INTO STG_VS_Agent_Master
SELECT  * FROM #V_STG_VS_Agent_Master STG
WHERE NOT EXISTS (SELECT 1 FROM STG_VS_Agent_Master MAIN WHERE (STG.Band = MAIN.Band) AND MAIN.EFF_END_DT = CAST('9999-12-31' AS DATE)  )

DROP table STG_VS_Agent_Master_BKP

END TRY

BEGIN CATCH
print ('Some error into the code')
END CATCH
END 
GO