SCD TYPE2 SQL SCRIPT

12. SCD TYPE2  SQL SCRIPT

-- CREATE SOURCE TABLE
USE DATABASE_NAME
GO

CREATE TABLE [dbo].[Customer_SCD_TYPE2_Source](
[dDate] [date] NOT NULL,
[IFO_UD_LIFESTYLE_IND] [numeric](7, 0) NULL,
[IFO_SCOR_ONE_NR] [numeric](7, 0) NULL,
[IFO_STATE] [varchar](2) NULL,
[IFO_CITY] [varchar](18) NULL,
[IFO_ZIP_CODE] [numeric](9, 0) NULL,
[Card_Key] [bigint] NULL
) ON [PRIMARY]

GO

-- INSERT DUMMY VALUES INTO SOURCE

INSERT INTO dbo.Customer_SCD_TYPE2_Source (dDate, IFO_UD_LIFESTYLE_IND, IFO_SCOR_ONE_NR, IFO_STATE, IFO_CITY, IFO_ZIP_CODE, Card_Key)
VALUES ('2019-04-16', '79', 491, 'MD', 'SOLDOTNA', '99669', 1)

INSERT INTO dbo.Customer_SCD_TYPE2_Source (dDate, IFO_UD_LIFESTYLE_IND, IFO_SCOR_ONE_NR, IFO_STATE, IFO_CITY, IFO_ZIP_CODE, Card_Key)
VALUES ('2019-04-16', '89', 439, 'CO', 'DENVER', '80210', 2)

INSERT INTO dbo.Customer_SCD_TYPE2_Source (dDate, IFO_UD_LIFESTYLE_IND, IFO_SCOR_ONE_NR, IFO_STATE, IFO_CITY, IFO_ZIP_CODE, Card_Key)
VALUES ('2019-04-16', '109', 483, 'NC', 'CLAYTON', '27527', 3)

INSERT INTO dbo.Customer_SCD_TYPE2_Source (dDate, IFO_UD_LIFESTYLE_IND, IFO_SCOR_ONE_NR, IFO_STATE, IFO_CITY, IFO_ZIP_CODE, Card_Key)
VALUES ('2019-04-16', '199', 389, 'GA', 'CARTERSVILLE', '30120', 4)

INSERT INTO dbo.Customer_SCD_TYPE2_Source (dDate, IFO_UD_LIFESTYLE_IND, IFO_SCOR_ONE_NR, IFO_STATE, IFO_CITY, IFO_ZIP_CODE, Card_Key)
VALUES ('2018-09-16', '99', 489, 'AZ', 'PHOENIX', '85020', 5)

-- CREATE TARGET TABLE

CREATE TABLE [dbo].[Customer_SCD_TYPE2](
[Customer_Key] [bigint] IDENTITY(1,1) NOT NULL,
[Card_Key] [bigint] NOT NULL,
[City_Name] [varchar](80) NULL,
[Zip_Code] [varchar](10) NULL,
[State_Code] [varchar](10) NULL,
[Score_One_Nbr] [int] NULL,
[Lifestyle_Index_Code] [varchar](10) NULL,
[Effective_From_Date] [date] NULL,
[Effective_Thru_Date] [date] NULL,
[Latest_Ind] [char](1) NULL,
[Version_Nbr] [int] NULL,
[Last_Modify_Date] [datetime] NULL,
 CONSTRAINT [pk_Customer__Customer_Key] PRIMARY KEY CLUSTERED
(
[Customer_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE dbo.Customer_SCD_TYPE2 ADD  CONSTRAINT [df_Customer_SCD_TYPE2__Effective_From_Date]  DEFAULT ('1900-01-01') FOR [Effective_From_Date]
GO

ALTER TABLE dbo.Customer_SCD_TYPE2 ADD  CONSTRAINT [df_Customer_SCD_TYPE2__Effective_Thru_Date]  DEFAULT ('9999-12-31') FOR [Effective_Thru_Date]
GO

ALTER TABLE dbo.Customer_SCD_TYPE2 ADD  CONSTRAINT [df_Customer_SCD_TYPE2__Latest_Ind]  DEFAULT ('Y') FOR [Latest_Ind]
GO

ALTER TABLE dbo.Customer_SCD_TYPE2 ADD  CONSTRAINT [df_Customer_SCD_TYPE2__Version_Nbr]  DEFAULT ((1)) FOR [Version_Nbr]
GO


SELECT * FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2_Source
SELECT * FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2


-- TYPE 2 SCRIPT START FROM HERE



USE DATABASE_NAME
GO

DECLARE @Message_Text VARCHAR(2500)
DECLARE @dDate DATE ='2018-04-16'
DECLARE @Last_Modify_Date DATETIME = GETDATE();


-- UPDATE EXISTING RECORDS WHEN CHANGE VALUES IN SOURCE AND TARGET

UPDATE DATABASE_NAME.dbo.Customer_SCD_TYPE2
SET Effective_Thru_Date = DATEADD(DD, - 1, @dDate)
,Latest_Ind = 'N'
,Last_Modify_Date = @Last_Modify_Date
FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2_Source SRC
WHERE Customer_SCD_TYPE2.Card_Key = SRC.Card_Key
AND Customer_SCD_TYPE2.Latest_Ind = 'Y'
AND (
ISNULL(LTRIM(RTRIM(Customer_SCD_TYPE2.City_Name)),0) <>  ISNULL(LTRIM(RTRIM(SRC.IFO_CITY)),0)
OR  ISNULL(LTRIM(RTRIM(Customer_SCD_TYPE2.Zip_Code)),0) <> ISNULL(LTRIM(RTRIM(SRC.IFO_ZIP_CODE)),0)
OR  ISNULL(LTRIM(RTRIM(Customer_SCD_TYPE2.State_Code)),0) <>  ISNULL(LTRIM(RTRIM(SRC.IFO_STATE)),0)
OR  ISNULL(LTRIM(RTRIM(Customer_SCD_TYPE2.Score_One_Nbr)),0) <>  ISNULL(LTRIM(RTRIM(SRC.IFO_SCOR_ONE_NR)),0)
OR  ISNULL(LTRIM(RTRIM(Customer_SCD_TYPE2.Lifestyle_Index_Code)),0) <>  ISNULL(LTRIM(RTRIM(SRC.IFO_UD_LIFESTYLE_IND)),0)
)

--- INSERT NEW RECORDS INTO TARGET TABLE


INSERT INTO DATABASE_NAME.dbo.Customer_SCD_TYPE2
(
       Card_Key
      ,City_Name
      ,Zip_Code
      ,State_Code
      ,Score_One_Nbr
      ,Lifestyle_Index_Code
      ,Effective_From_Date
      ,Effective_Thru_Date
      ,Latest_Ind
      ,Version_Nbr
      ,Last_Modify_Date
)

SELECT
LTRIM(RTRIM(SRC.Card_Key)) Card_Key,
LTRIM(RTRIM(SRC.IFO_CITY)) IFO_CITY,
LTRIM(RTRIM(SRC.IFO_ZIP_CODE)) IFO_ZIP_CODE,
LTRIM(RTRIM(SRC.IFO_STATE)) IFO_STATE,
LTRIM(RTRIM(SRC.IFO_SCOR_ONE_NR)) IFO_SCOR_ONE_NR,
LTRIM(RTRIM(SRC.IFO_UD_LIFESTYLE_IND)) IFO_UD_LIFESTYLE_IND,
@dDate Effective_From_Date,
'31-DEC-9999' Effective_Thru_Date,
'Y' Latest_Ind,
CASE
WHEN vers.Card_Key IS NULL THEN 1 ELSE vers.Version_Nbr + 1 END Version_Nbr,
@Last_Modify_Date Last_Modify_Date
FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2_Source SRC
LEFT JOIN (
SELECT TGT.Card_Key,
MAX(TGT.Version_Nbr) Version_Nbr
FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2 TGT
GROUP BY TGT.Card_Key
) AS vers
ON SRC.Card_Key = vers.Card_Key

EXCEPT

SELECT
LTRIM(RTRIM(TGT.Card_Key)) Card_Key,
LTRIM(RTRIM(TGT.City_Name)) City_Name,
LTRIM(RTRIM(TGT.Zip_Code))  Zip_Code,
LTRIM(RTRIM(TGT.State_Code)) State_Code,
LTRIM(RTRIM(TGT.Score_One_Nbr)) Score_One_Nbr,
LTRIM(RTRIM(TGT.Lifestyle_Index_Code)) Lifestyle_Index_Code,
@dDate Effective_From_Date,
'31-DEC-9999' Effective_Thru_Date,
'Y' Latest_Ind,
TGT.Version_Nbr + 1 Version_Nbr,
@Last_Modify_Date Last_Modify_Date
FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2 TGT
WHERE @dDate BETWEEN TGT.Effective_From_Date AND TGT.Effective_Thru_Date
ORDER BY 1

-- VALIDATE DATA
SELECT * FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2_Source
SELECT * FROM DATABASE_NAME.dbo.Customer_SCD_TYPE2