Thursday, April 18, 2013

[SQL Server Data Warehousing] T-SQL SCD2


Here is the query i am using in case if it helps.


To give you little more scenario  on my request  : Our Data warehouse is very traditional approach of ETL , We have update statments that does lookup and update the Foriegn keys.


My first update statments goes and finds an employee based on a logic of Data Entry



Update dbo.SFWP
Set EmployeeID = we2.EmployeeSK
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
and Src_EntryDate >= we2.EffectiveBeginDTM and
Src_EntryDate <= we2.EffectiveEndDTM

Apparently for some reason some employees cannot be matched because  our employee table has this condition not met because of missing records


and  Src_EntryDate >= we2.EffectiveBeginDTM and
Src_EntryDate <= we2.EffectiveEndDTM


So, my new query i am trying to take a previous or Next record and find employee ID and update it.



wITH CustCTE as
(
select
We2.SK,
We2.NK,
We2.EffectiveBeginDTM,
We2.EffectiveEndDTM,
ROW_NUMBER() OVER (PARTITION BY We2.NK ORDER BY We2.EffectiveBeginDTM) RowNum
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
Where
Src_EntryDate < we2.EffectiveBeginDTM or
Src_EntryDate > we2.EffectiveEndDTM
and a.EmployeeID is NULL
)
,
CustCTE1 as (
Select
a.SK
,a.NK
,a.EffectiveBeginDTM
,a.EffectiveEndDTM
,a.RowNum

From CustCTE a
Union All
Select
Curr.SK
,Curr.NK
,Curr.EffectiveBeginDTM
,Curr.EffectiveEndDTM
,Curr.RowNum

From CustCTE Curr
Inner Join CustCTE1 Prev
on Curr.NK = Prev.NK
and Curr.RowNum = Prev.RowNum-1
)
select * From CustCTE1

But i am not getting the same thing as i expect..


Any help in this matter will be highly appreciated.


thank you in advance



.

social.technet.microsoft.com/Forums

No comments:

Post a Comment

Search This Blog