1

Topic: How to reveal holes in a range of numbers

the task following:
1) I specify a range of numbers for check in presence of "holes" in a column
2) I need to reveal all holes and to write down value:
With which gaps began
Number on which ended
Amount of the passed
And so on all holes in  a range
The column is a lot of
select
t1.f1,
min (t2.f1) as mins;
min (t2.f1)-t1.f1-1 as counts
from #temp1 t1, #temp1 t2
where t2.f1> t1.f1
group by t1.f1
having t1.f1+1 <min (t2.f1)
This request solves a problem but brakes on big  information (to compare for time it is necessary to 10-20 million lines)
Can at  there is other decision, will be glad for the help smile

2

Re: How to reveal holes in a range of numbers

kolyady;
500 thousand  time here at a forum it was considered
https://www.google.ru/search?hl=en&q=gaps and island t-sql&gws_rd=cr,ssl&dcr=0&ei=RHyWWq6iOIWRsgHZ_q34Bw

3

Re: How to reveal holes in a range of numbers

kolyady;
Make the indexed mullions-strong list and find a difference of two dial-ups.

4

Re: How to reveal holes in a range of numbers

Vladislav Kolosov;
you mean to create the similar table with the same range of numbers (only without passes) and to compare to the original?

5

Re: How to reveal holes in a range of numbers

kolyady, passions that what...
If the version is not specified, it is supposed that implementation can be on any version of the server.

create table #temp (id int);
insert into #temp (id)
values (1), (2), (4), (8);
select prev_id + 1 as gap_start, t.id - 1 as gap_end, t.id - t.prev_id - 1 as gap_count
from (select id, lag (id) over (order by id) as prev_id
from #temp) as t
where t.id - t.prev_id> 1;

6

Re: How to reveal holes in a range of numbers

Minamoto;
Thanks like that that are necessary smile

7

Re: How to reveal holes in a range of numbers

kolyady, for the sake of justice it not the best variant, look here at comparing on productivity (from the link which to you above a distance, found):
https://blog.waynesheffield.com/wayne/a … detection/

8

Re: How to reveal holes in a range of numbers

kolyady;
Yes, generation of such table does not occupy a lot of time, and comparing will happen very quickly. Such table is especially useful by the regular search. The same concerns also a calendar.

9

Re: How to reveal holes in a range of numbers

All thanks!
Answer Minamoto, for my specific situation entirely arranges
It is possible to consider a subject closed.
smile