Derived OLD Values
-
The Problem I have is:
New| Old
B| A
C| B
E| D
______________________________________
1 Table
2 Columns
Value "C" (from NEW) was "B" (from OLD) Which was "A" (From OLD)
Value "E" (from NEW) was "D" (From OLD)
--"C" is a known value and Need to return Values for "A" & "B"
(or all prior values of "C" NEW)
-- "C" is current value of a slowly changing Dimension and "A"&"B" are prior "OLD" Values.
--"C" can also change over time but will always be known
What would be the best way to solve this via a 2 query UNION/ CASE etc?
TIA
Sartre144
-
If I understand the problem correctly, how many levels can this go one?
After ?? time periods F -> E -> D -> C -> B-> A etc.
-
I would suggest a recursive Function or Stored Procedure i.e. The Function calls itself to build a string of previous values.
-
[Hi,
This can have the potential to grow to a theoretical max of 26 levels contrained by time period changes. Actual max changes recorded over the past 30 years have been 5 ]
TIA
Sartre144
If I understand the problem correctly, how many levels can this go one?
After ?? time periods F -> E -> D -> C -> B-> A etc.
[/quote]
-
Will it be always a single branch relationship (one to one)?
-
Forgotten, will it always be a one to one relationship?
-
Forgotten, will it always be a one to one relationship?
[Yes Always]
-
Feels a bit slow, maybe you should try it in a stored procedure
Create Table NewOld(New Char(1),Old Char(1))
Go
Insert NewOld values('B','A')
Insert NewOld values('C','B')
Insert NewOld values('F','B')
Insert NewOld values('G','F')
Insert NewOld values('H','G')
Insert NewOld values('E','D')
Go
Create Function ClimbDownTheTree(@Start Char(1)) Returns Varchar(27) as
Begin
Declare @Answ varchar(27)
-- Init for recursion
Set @Answ=@Start
-- Expand the tree
While Right(@Answ,1)<>','
Select @Answ=@Answ+IsNull((Select Old from NewOld Where New=Right(@Answ,1)),',')
-- Strip start parameter and termination character ','
Return Right((Left(@Answ,DataLength(@Answ)-1)),DataLength(@Answ)-2)
End
Go
Select dbo.ClimbDownTheTree('H'), -- Multi level
dbo.ClimbDownTheTree('Z'), -- Branch does not exist at all
dbo.ClimbDownTheTree('A'), -- None existing tree
dbo.ClimbDownTheTree('E') -- One level
Go
Drop Function ClimbDownTheTree
Drop Table NewOld
Go
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply