T-SQL Syntax for SCD Type 2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQL Syntax for SCD Type 2

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2012-02-13 : 14:28:25

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

any ideas?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-14 : 07:03:30

looks very familiar. what happens if there is a break between ValidFrom and ValidTo? What if there is overlap? Which one will be IsCurrent?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 10:19:36

based on your primary key you can write a logic like this


UPDATE d
SET d.ValidTo=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
FROM DestTable d
INNER JOIN SourceTable s
ON s.PK = d.PK
WHERE ISNULL(d.Column1,'') <> ISNULL(s.Column1,'')
...


INSERT INTO DestTable(Column1,Column2,...,ValidFrom,ValidTo,...)
SELECT s.Column1,s.Column2,...,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0),NULL,..
FROM SourceTable s
LEFT JOIN DestTable d
ON  s.PK = d.PK
WHERE ISNULL(d.Column1,'') <> ISNULL(s.Column1,'')

PK will represent primary key combination
DestTable is table in your destination DB where you want to capture SCD 2 info

SourceTable is table containing deltas (data value changes) for the day

column1,column2 etc are other columns of tables
ISNULL() is required only for NULL columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sauce1979
Starting Member

47 Posts

Posted - 2012-02-14 : 17:58:20

I actually managed to get the desired result using a recursive cte 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', ''


SELECT * FROM  #tstDimPortfolio


SELECT * FROM #tstDimPortfolio



SELECT  ROW_NUMBER() OVER (PARTITION BY UPI ORDER BY UPI, DateofChange) Sequence,
		UPI, 
		CASE WHEN LookupMF_CODE <> '' THEN LookupMF_CODE ELSE MF_CODE END MF_CODE, 
		CASE WHEN LookupMF_CODE <> '' THEN LookupBH_Code ELSE BH_Code END BH_Code, 
		CASE WHEN LookupCR_Code <> '' THEN LookupCR_Code ELSE CR_Code  END CR_Code, 
		ValidFrom, ValidTo, IsCurrent, DateofChange,
		LookupMF_CODE,
		LookupBH_CODE,
		LookupCR_CODE
INTO #Dimension_Table
FROM #tstDimPortfolio


;WITH AddedDim AS
(SELECT * FROM 

(
SELECT 
ROW_NUMBER() OVER (PARTITION BY UPI ORDER BY UPI, DateofChange) Sequence,
UPI,
CASE WHEN LookupMF_CODE <> '' THEN LookupMF_CODE ELSE MF_CODE END MF_CODE, 
CASE WHEN LookupBH_CODE <> '' THEN LookupBH_Code ELSE BH_Code END BH_Code, 
CASE WHEN LookupCR_Code <> '' THEN LookupCR_Code ELSE CR_Code  END CR_Code, 
DateofChange AS ValidFrom, 
ValidTo, 
IsCurrent 
FROM #tstDimPortfolio
)A
WHERE SEQUENCE = 1

UNION ALL
SELECT  
DT.Sequence
, DT.UPI
,CASE WHEN DT.LookupMF_CODE <> '' THEN DT.LookupMF_CODE ELSE DM.MF_CODE END MF_CODE
,CASE WHEN DT.LookupBH_CODE <> '' THEN DT.LookupBH_Code ELSE DM.BH_Code END BH_Code 
,CASE WHEN DT.LookupCR_Code <> '' THEN DT.LookupCR_Code ELSE DM.CR_Code  END CR_Code
,DT.DateofChange AS ValidFrom 
,DT.ValidTo 
,DT.IsCurrent 
FROM #Dimension_Table dt inner join
AddedDim dm
on DT.UPI = DM.UPI
AND dt.Sequence = DM.Sequence + 1 
)



SELECT * 
INTO #temp
FROM AddedDim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources