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: Code: 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
