Help with SCD Type 2 Query - 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)

Help with SCD Type 2 Query

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2012-07-19 : 13:12:30

can anyone please help me with SCD type 2 query??? I can do the same with SSIS but i need this in a SQL Query please...

I have a table like below

Employee_ID Employee_Name Marital_Status DateField
111 aaa Single 2011/01/01

Recently employee 111 got married on 2012-03-01. But we want to store that information as type 2...So i need the output as....

Employee_ID Employee_Name Marital_Status Start_Date End_Date
111 aaa Single 2011/01/01 2012-03-01
111 aaa Married 2012-03-01 NULL

If marital_status change, then i need to set the date of update to End_Date and add a new record with the start date of updated time.

Can someone please help me with the query? I am having hardtime to figure out a way for this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 13:20:36

use MERGE

see

http://www.sqlservercentral.com/articles/MERGE/73805/

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

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2012-07-19 : 22:51:59

I am having hard time understanding it. can you please give me an example with the code? Is there anything other than Merge too?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 23:08:03

quote:
Originally posted by sql_server_dba

I am having hard time understanding it. can you please give me an example with the code? Is there anything other than Merge too?

yep you can use INSERT...UPDATE combination instead of MERGE if you want

like


IF EXISTS (SELECT 1 FROM Destination d 
           INNER JOIN Source s
           ON s.Employee_ID = d.Employee_ID
           WHERE d.End_Date IS NULL
           )
BEGIN
UPDATE d
SET d.End_Date = GETDATE()
FROM Destination d 
INNER JOIN Source s
ON s.Employee_ID = d.Employee_ID
WHERE d.End_Date IS NULL
END


INSERT Destination(Employee_ID,	Employee_Name,	Marital_Status, StartDate)
SELECT Employee_ID,Employee_Name,Marital_Status,GETDATE()
FROM Source

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources