I have two tables: MainTable
and MyTable
. MyTable has unique ControlNo and ID. I need to add very first EffDate
from MainTable
to MyTable
based on ID
and ControlNo
.
For that I need to look at PreviousID
column, then see if that PreviousID
is in ID
column and so on.
Desired output should look like this:
The below is an example with dummy data of getting proper EffDate
by supplying an ID
value. It works, but how can I loop through the whole MainTable
, retrieve ID
‘s and EffDate
into separate table, then join that table to MyTable
?
-- function returns PreviousID based on ID CREATE FUNCTION [dbo].[GetPriorQuoteID](@ID varchar(50)) RETURNS varchar(50) AS BEGIN DECLARE @RetVal varchar(50) SET @RetVal = NULL SELECT TOP 1 @RetVal = MainTable.PreviousID FROM MainTable WHERE MainTable.ID = @ID RETURN @RetVal END -- create sample table IF OBJECT_ID('MainTable') IS NOT NULL DROP TABLE MainTable; select 3333 as ControlNo, 'QuoteID3' as ID, 'QuoteID2' as PreviousID, '2020-08-25' as EffDate into MainTable union all select 2222 as COntrolNo, 'QuoteID2', 'QuoteID1', '2019-08-25' union all select 1111 as COntrolNo, 'QuoteID1', NULL, '2018-08-25' union all select 7777 as COntrolNo, 'QuoteID6', 'QuoteID5', '2020-02-10' union all select 6666 as COntrolNo, 'QuoteID5', NULL, '2019-02-10' select * from MainTable DECLARE @PriorQuote varchar(50) DECLARE @RetVal VARCHAR(50) = '' DECLARE @ControlNo INT DECLARE @ID varchar(50) = 'QuoteID3' SELECT TOP 1 @ControlNo = MainTable.ControlNo FROM MainTable WHERE MainTable.ID = @ID Set @PriorQuote = @ID SELECT TOP 1 @PriorQuote = MainTable.ID FROM MainTable WHERE MainTable.ControlNo = @ControlNo WHILE dbo.GetPriorQuoteID(@PriorQuote) IS NOT NULL AND dbo.GetPriorQuoteID(@PriorQuote)<> @PriorQuote BEGIN SET @PriorQuote = dbo.GetPriorQuoteID(@PriorQuote) END SELECT TOP 1 @RetVal = CONVERT(VARCHAR(10), MainTable.EffDate, 101) FROM MainTable WHERE MainTable.ID = @PriorQuote SELECT @RetVal -- clean up drop table MainTable drop function GetPriorQuoteID
UPDATE: Adding dummy data tables
-- create sample table #MainTable IF OBJECT_ID('tempdb..#MainTable') IS NOT NULL DROP TABLE #MainTable; create table #MainTable (ControlNo int, ID varchar(50), PreviousID varchar(50), EffDate date) insert into #MainTable values (3333,'QuoteID3','QuoteID2', '2020-08-25'), (2222,'QuoteID2','QuoteID1', '2019-08-25'), (1111,'QuoteID1',NULL, '2018-08-25'), (7777,'QuoteID6','QuoteID5', '2020-02-10'), (6666,'QuoteID5',NULL, '2019-02-10') --select * from #MainTable -- create sample table #MyTable IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable; create table #MyTable (ControlNo int, ID varchar(50), EffDate date) insert into #MyTable values (3333,'QuoteID3',NULL), (7777,'QuoteID6',NULL) --select * from #MyTable
Advertisement
Answer
You can use a recursive query to traverse the hierarchy.
I would start by joining the original table with the main table, which restricts the paths to just the rows we are interested in. Then, you can recurse towards the parent. Finally, we need to filter on the top parent per path: top()
and row_number()
come handy for this.
Consider:
with cte as ( select t.controlno, t.id, m.previousid, m.effdate, 1 lvl from #maintable m inner join #mytable t on t.controlno = m.controlno and t.id = m.id union all select c.controlno, c.id, m.previousid, m.effdate, c.lvl + 1 from cte c inner join #maintable m on m.id = c.previousid ) select top(1) with ties controlno, id, effdate from cte order by row_number() over(partition by controlno, id order by lvl desc)
controlno | id | effdate --------: | :------- | :--------- 3333 | QuoteID3 | 2018-08-25 7777 | QuoteID6 | 2019-02-10