declare @a table (ID int, Parent_ID int)
	

insert into @a
values
	(1, 2	),
	(2, 3	),
	(3, 4	),
	(4, 5	),
	(7, 8	),
	(8, 9	),
	(9, 10	)

select * from @a	

;with cte
as
(
	select
		Start_ID = ID,
		ID, 
		Parent_ID, 
		Level = 1 
	from @a
	where ID not in (select Parent_ID from @a)

	union all

	select 
		Start_ID = cte.Start_ID, 
		a.ID, 
		a.Parent_ID, 
		Level = cte.Level + 1
	from 
		cte
		inner join @a a on a.ID = cte.Parent_ID

)

select 
	s = min(ID), e = max(Parent_ID) 
from cte 
group by Start_ID
order by 1

declare @a table (ID int, Parent_ID int)
	

insert into @a
values
	(1, 2	),
	(2, 3	),
	(3, 4	),
	(4, 5	),
	(7, 8	),
	(8, 9	),
	(9, 10	)

select * from @a	


;with cte
as
(
	select 
		RN = ROW_NUMBER() over (order by a.ID),
		a.*
	from @a	a
	left join @a _prev on _prev.ID  = a.ID - 1
	left join @a _next on _next.ID = a.ID + 1
	where _prev.ID is null or  _next.ID is null 
)


select 
	s = min(ID), e = max(Parent_ID) 
from cte
group by RN / 3

declare @num table(a int,b int)

insert @num 
values
	(1, 2	),
	(2, 3	),
	(3, 4	),
	(4, 5	),
	(7, 8	),
	(8, 9	),
	(9, 10	)

select min(a) a,max(b) b
from(
select *
,sum(n) over(order by a) s
from(
select a,b
,case when isnull(lag(b) over(order by a),a)=a or isnull(lag(a) over(order by a),a)=b  then 0 else 1 end n
from @num
) w
) z
group by s

declare @num table(a int,b int)

insert @num 
values
	(1, 2	),
	(2, 3	),
	(3, 4	),
	(4, 5	),
	(7, 8	),
	(8, 9	),
	(9, 10	)

select a, min(b) 
from
(
select a from @num t1 where not exists (select 1 from @num t2 where  t1.a > t2.a and t1.a <= t2.b)
) n
join
(
select b from @num t1 where not exists (select 1 from @num t2 where t1.b >= t2.a and t1.b < t2.b)
) e
on n.a < e.b
group by  n.a

declare @a table (ID int, Parent_ID int)
	

insert into @a
values
	(1, 2	),
	(2, 3	),
	(3, 4	),
	(4, 5	),
	(7, 8	),
	(8, 9	),
	(9, 10	)



select 
		a.ID
		,b.Parent_ID
from	

	(select 
			a1.ID
			,rn		=row_number()over(order by a1.ID)
	from		@a a1 
	left join	@a a2	on	a1.ID=a2.Parent_ID 
	where a2.ID is null) a 

inner join

	(select 
			a1.Parent_ID
			,rn		=row_number()over(order by a1.Parent_ID)
	from		@a a1 
	left join	@a a2	on	a1.Parent_ID=a2.ID 
	where a2.ID is null) b
	
on	a.rn=b.rn

select
		ID			=max(ID)
		,Parent_ID	=max(Parent_ID) 
from
	(select 
			a1.ID
			,a2.Parent_ID
			,i		=row_number()over(partition by case when a1.ID is null then 0 when a2.Parent_ID is null then 1 end order by isnull(a1.ID,a2.Parent_ID))
	from		@a a1 
	full join	@a a2	on	a1.ID=a2.Parent_ID 

	where a1.ID is null or a2.Parent_ID is null) t

group by 
		i

select MIN(id),MAX(parent_id)
  from(
select *,id-ROW_NUMBER()over(order by id)r
  from @a) a
 group by r 

create table #t (
	id int primary key,
	rn int not null identity(1,1),
	unique (rn)
);

insert	#t (id)
select
	id
from	(
	values
		(1),
		(2),
		(3),
		(4),
		(5),
		(7),
		(8),
		(9),
		(10)
) a(id)
order by
	id;

; with
cte
as
(
	select
		id,
		_begin = id,
		_end = id,
		rn
	from	#t
	where
		rn = 1

	union all

	select
		n.id,
		_begin = case when n.id - p._end > 1 then n.id else p._begin end,
		_end = n.id,
		n.rn
	from	cte p
	join	#t n on p.rn + 1 = n.rn
)
/*select	*
from	cte*/
select
	_begin,
	max(_end) as _end
from	cte
group by
	_begin;

drop table #t
go

declare @t table (i int);
insert into @t(i) values (1),(2),(3),(4),(5),(7),(8),(9),(10);

select b, e
from (select b, lead(b,1,0) over (order by b) as e, p
      from (select i as b, lag(i,1,i) over (order by i)+1-i as p, lead(i,1,i) over (order by i)-1-i as n 
            from @t
           ) t
      where not (n=0 and p=0) 
     ) t
where e!=0
and p!=0;