Assign Previous Record Information to New Rows Based on Number Gap

Question

I have a basic temp table called #rowCreate with a gap in the count column. It copies an existing table called dbo.rowSource.

parentID	name	count
1	A	1
1	B	2
1	C	5
1	D	6
1	D	8
2	A	3
2	B	4

My goal is to create new rows in the temo table with the previous "valid" record parentID and name, but with the missing gap count. So the new rows would look like this:

parentID	name	count
1	B	3
1	B	4
1	D	7

My script below works if there's only a 1 count gap. But if the gap is larger, additional rows aren't created. I've tried adjusting nt.count+ 1 BETWEEN tc.FirstMissingId and tc.LastMissingID, but the SELECT is grabbing just the first artificial row to create.

select parentID, name, tc.FirstMissingId
FROM #rowCreate td
LEFT JOIN (select parentID, count+1 as FirstMissingId, nextid - 1 as LastMissingId
from (select t.*, lead(count) over (partition by parentID order by count) as nextid
      from dbo.rowSource t
     ) t
where nextid <> count+1) tc on tc.parentID= td.parentID
LEFT JOIN (select parentID, name, count
        from dbo.rowSource) nt on nt.count+ 1 = tc.FirstMissingId 

Answer 1

See example with recursive query

with r as (
 select parentID, [name], [count]
     ,lead([count],1,[count])over(partition by parentId order by [count]) nextCount
     ,0 fAdded
  from test
  union all
  select parentID, [name], [count]+1,nextCount
     ,1 fAdded
  from r
  where [count]+1<nextCount
)
select * from r order by parentId,[count];

output is

parentID	name	count	nextCount	fAdded
1	A	1	2	0
1	B	2	5	0
1	B	3	5	1
1	B	4	5	1
1	C	5	6	0
1	D	6	8	0
1	D	7	8	1
1	D	8	8	0
2	A	3	4	0
2	B	4	4	0

And without recursion

with r as (
 select parentID, [name], [count]
     ,lead([count],1,[count])over(partition by parentId order by [count]) nextCount
  from test
)
select parentID, [name], [count]+coalesce(n,0) [count],n
from r
left join (values(0),(1),(2),(3),(4),(5),(6),(7))nn(n)
   on ([count]+n)<nextCount

there max gap value is 7

Answer 2

As I wrote in comment that, your output is not correct and not complete. Please post complete output with respect to your input data.

Try my below script,

declare @LaymanNumberTable table(col int)
insert into @LaymanNumberTable values(1),(2),(3),(4),(5)
,(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)

declare @t table(parentid int,name varchar(50),counts int)
insert into @t(parentid ,name ,counts)values(1,'A',1)
insert into @t(parentid ,name ,counts)values(1,'B',2)
insert into @t(parentid ,name ,counts)values(1,'C',5)
insert into @t(parentid ,name ,counts)values(1,'D',6)
insert into @t(parentid ,name ,counts)values(1,'D',8)
insert into @t(parentid ,name ,counts)values(2,'B',4)

;With CTE as
(
select *,
--(lead(counts,1) over(partition by [name] order by counts)-counts)-1 [Missing]
(lead(counts,1) over(partition by [name] order by counts)) [Missing1]
from @t t
)

select * from
(
select c.parentid,c.name,c.counts from CTE C 
union all
select c.parentid,c.name,lt.col from CTE C 
cross apply(
select lt.col from @LaymanNumberTable lt where (lt.col>counts and lt.col<Missing1)   )lt
where (Missing1 is not null)
)t4
order by name ,counts 

In my script you can notice number table which is very small suiting only this example. But as soon as you will post data the correct data and little real input I will change my script accordingly.