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
