2evZrqvS

1id | start_block | end_block
2-----------------------------
301 | 00000000001 | 00000000005
402 | 00000000006 | 00000000011
503 | 00000000012 | 00000000018
604 | 00000000025 | 00000000031
705 | 00000000032 | 00000000043
8	
9if ((arry(i,1) + 1) != (arry(i+1),1)( {
10  print("Bad Row!n");
11}
12	
13CREATE TABLE #t (startz INT, zend INT)
14insert into #t (startz, zend) values (1,5)
15insert into #t (startz, zend) values (6,11)
16insert into #t (startz, zend) values (12,18)
17insert into #t (startz, zend) values (25,31)
18insert into #t (startz, zend) values (32,43)
19
20select * from #t ta
21LEFT OUTER JOIN #t tb ON tb.startz - 1 = ta.zend
22WHERE tb.startz IS NULL
23	
24SELECT t.ID, t.Start_Block, t.End_Block
25FROM [TableName] t
26JOIN [TableName] t2 ON t.ID = t2.ID+1
27WHERE t.Start_Block - t2.End_Block > 1
28	
29SELECT
30     T1.end_block + 1 AS start_block,
31     T2.start_block - 1 AS end_block
32FROM
33     dbo.My_Table T1
34INNER JOIN dbo.My_Table T2 ON
35     T2.start_block > T1.end_block
36LEFT OUTER JOIN dbo.My_Table T3 ON
37     T3.start_block > T1.end_block AND
38     T3.start_block < T2.start_block
39WHERE
40     T3.id IS NULL AND
41     T2.start_block <> T1.end_block + 1
42	
43Select * From Table O
44   Where 
45      (Exists
46         (Select * From Table
47          Where End_Block < O.Start_Block)
48       And Not Exists 
49         (Select * From Table
50          Where End_Block = O.Start_Block - 1)) 
51    Or
52      (Exists
53         (Select * From Table
54          Where Start_Block > O.End_Block)
55       And Not Exists 
56         (Select * From Table
57          Where Start_Block = O.End_Block + 1 ))
58	
59CREATE TABLE #t (startz INT, zend INT)
60insert into #t (startz, zend) values (1,5)
61insert into #t (startz, zend) values (6,11)
62insert into #t (startz, zend) values (12,18)
63insert into #t (startz, zend) values (25,31)
64insert into #t (startz, zend) values (32,43)
65insert into #t (startz, zend) values (45,58)
66insert into #t (startz, zend) values (60,64)
67insert into #t (startz, zend) values (70,98)
68
69
70select tab1.zend+1 as MissingStartValue,
71       (select min(startz-1) from #t where startz > tab1.zend+1) as MissingEndValue
72 from #t as tab1 where not exists (select 1 from #t as tab2 where tab1.zend + 1 = tab2.startz)
73and (select min(startz-1) from #t where startz > tab1.zend+1) is not null
74	
75select * from blocks a
76where not exists (select * from blocks b where b.start_block = a.end_block + 1)
77	
78select a.end_block, min(b.start_block)
79from blocks a,
80     blocks b
81where not exists (select * from blocks c where c.start_block = a.end_block + 1)
82and b.start_block > a.end_block
83group by a.end_block
84	
85SELECT t1.End_Block + 1 as Start_Block,
86       t2.Start_Block - 1 as End_Block,
87  FROM Table as t1, Table as t2
88 WHERE t1.ID + 1 = t2.ID
89   AND t1.End_Block + 1 <> T2.Start_Block
90	
91select e1.end_block + 1 as start_hole,
92    (select min(start_block) 
93     from extent e3 
94     where e3.start_block > e1.end_block) - 1 as end_hole
95from extent e1 
96left join extent e2 on e2.start_block = e1.end_block + 1
97where e2.start_block is null 
98and e1.end_block <> (select max(end_block) from extent);