Self Join onto table until a column is null

Transact-SQL
1

Hi, I have table which is a list of policies and their respective keys (Section_Key).

In the same table I have a column called "Previous_Policy_Renewal_Key", I am looking for a way to go through all the iterations of "previous_policy_renewal_keys" until it finds the first (which will identified as being a null) and then return the "Section_key"

The reason is basically I need to cycle through all the policies on the table until I get to the first section_key (or policy reference)

the table is called DM_Policy and really struggling, obviously working from home means I have limited people to ask.

Many Thanks

Nick

2

Please give us direct access to your sql server or privide sample data?

Create table #spurs(id int) --- all columns

Insert into #spurs
Select 1 id union
Select 2 union. --etc providing near real sample data we can try on

3

Since we don't have a sample table or data - or even expected results it is real hard to figure out a solution. With that said this looks like one of the only places where I would consider using a recursive CTE in an inline-table valued function.

That is just a guess...depending on the table structure and the actual data there could be other (better) options.

4

hi

i am trying to understand what you are trying to do ???
hope this helps :slight_smile:

drop table #temp 
go

create table #temp 
(
Previous_Policy_Renewal_Key int , 
Section_key int 
)
go 
insert into #temp select 1,2
insert into #temp select 2,4
insert into #temp select 3,2
insert into #temp select null,4
insert into #temp select 4,2
insert into #temp select 5,4
insert into #temp select null,2
go 

select 'sample data',* from #temp 
go 

-- why not just get the null values 
select 'SQL Output',case when Previous_Policy_Renewal_Key is null then Section_key else Previous_Policy_Renewal_Key end 
from #temp

image