By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- 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
7 rows affected
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 |