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.
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
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.
i am trying to understand what you are trying to do ???
hope this helps
drop table #temp
create table #temp
Previous_Policy_Renewal_Key int ,
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
select 'sample data',* from #temp
-- 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