SQL Server Performance

T-SQL Syntax for SCD Type 2

Discussion in 'ALL SQL SERVER QUESTIONS' started by sauce1979, Feb 13, 2012.

  1. sauce1979 New Member

    I am currently trying to complete the final part of a query that loads scd type 2 data into a dimension.
    Based on the data provided below I would like to produce an output that can be inserted into a dimension in addition to
    expiring old records and tracking history etc. The data is such that I have the most current records where attributes have changed. The changed values are found in Lookup columns along with and dateOfchange i.e. the date the change took place. This dateOfchange should obviously become the validTo date of the most current record.

    The sample data is as follows:

    CREATE TABLE #tstDimPortfolio
        [ID][INT] IDENTITY (1,1) NOT NULL,
        [UPI] [varchar](20) NOT NULL,
        [MF_CODE] [varchar](10) NULL,
        [BH_Code] [varchar](10) NULL,
        [CR_Code] [varchar](10) NULL,
        [ValidFrom][varchar](10) NOT NULL,
        [ValidTo][varchar](10) NULL,
        [IsCurrent] [CHAR] (1) NULL,
        [DateofChange] [varchar](10) NULL,
        [LookupMF_CODE] [varchar](10) NULL,
        [LookupBH_Code] [varchar](10) NULL,
        [LookupCR_Code] [varchar](10) NULL,
    INSERT INTO #tstDimPortfolio
    SELECT 'B06531','B06531','','B06531','20111230',NULL,'Y','20120101','','B06531', ''UNION ALL
    SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120102','BLI004','', ''UNION ALL
    SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120103','BLI005','', ''UNION ALL
    SELECT 'BLI027','BLI027','L147','BBL_GBN','20111230',NULL,'Y','20120104','','L146', ''
    With this data the new output should be

    UPI MF_CODE BH_Code CR_Code ValidFrom ValidTo IsCurrent
    B06531 B06531 B06531 20111230 20120101 N
    B06531 B06531 B06531 B06531 20120101 NULL Y
    BLI003 BLI003 BBL_WORLD 20111230 20120102 N
    BLI003 BLI004 BLI003 BBL_WORLD 20120102 20120103 N
    BLI003 BLI005 BLI003 BBL_WORLD 20120103 NULL Y
    BLI027 BLI027 L147 BBL_GBN 20111230 20120104 N
    BLI027 BLI027 L146 BBL_GBN 20120104 NULL Y

Share This Page