How to loop through table using while loop and create another table with values needed

Question

I have two tables: MainTable and MyTable. MyTable has unique ControlNo and ID. I need to add very first EffDate from MainTable to MyTablebased 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

Answer 1

Here is a working example of the CTE approach with the table provided

;with recur_cte(ControlNo, ID, PreviousID, EffDate, HLevel) as (
    select mt.ControlNo, cast(null as varchar(100)), mt.PreviousID, mt.EffDate, 1
    from MainTable mt
    where not exists(select 1 
                     from MainTable mt_in
                     where mt.ID=mt_in.PreviousID)
    union all
    select rc.ControlNo, rc.ID, mt.PreviousID, mt.EffDate, rc.HLevel+1
    from recur_cte rc
         join MainTable mt on rc.PreviousID=mt.ID and rc.EffDate>mt.EffDate)
select * from recur_cte;

Results

ControlNo   ID  PreviousID  EffDate HLevel
3333    NULL    QuoteID2    2020-08-25  1
7777    NULL    QuoteID5    2020-02-10  1
7777    NULL    NULL        2019-02-10  2
3333    NULL    QuoteID1    2019-08-25  2
3333    NULL    NULL        2018-08-25  3

Answer 2

using CTE like below you can get the desired results. See live demo

Learn more about recursive CTEs here

 ; with cte as 
   (
       select EffDate, ControlNo, ID, Level=1 from MainTable
       where PreviousID is NULL
       union all
       select C.EffDate, M.ControlNo, M.ID, Level=Level+1 from MainTable AS M
       join cte as C on C.ID=M.PreviousID
     )
     
    select MyTable.*,cte.EffDate from cte join MyTable on Mytable.ID=cte.ID

Answer 3

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)

Demo on DB Fiddle:

controlno | id       | effdate   
--------: | :------- | :---------
     3333 | QuoteID3 | 2018-08-25
     7777 | QuoteID6 | 2019-02-10

Answer 4

You can use a recursive CTE for this:

WITH cte 
AS
(

SELECT m.ID,m.PreviousID
FROM MainTable m
JOIN MainTable m2
     ON m.previousID = m2.ID
WHERE m2.previousID IS NULL

UNION ALL

SELECT m2.ID,cte.previousID
FROM cte
JOIN MainTable m2
     ON m2.previousID = cte.ID

)

SELECT *
FROM cte;