# how to find gap in a range.

edited January 12, 2020 7:18AM

I use sql 2012 but, one can answer up to 2019

create table t ( b decimal(13,4) not null, c decimal(13,4) not null);
Insert into T (B,C) values ( 1,200);
Insert into T (B,C) values (   200   ,400);
Insert into T (B,C) values (  600,   900);

create table t1 (b decimal(13,4) not null, c decimal(13,4) not null);
Insert into t1 (B,C) values ( 1,100);
Insert into t1 (B,C) values (  200   ,500);
Insert into t1 (B,C) values (  800,   1000);

-- "t" and "t1" table holds ranges which could be any thing where b<c, i have to find out where "t" has range but "t1" does not?
--ex "t"  has 1,200 , "t1"  1,100  so i want to see 100 to 200 t2 does not have any thing.

Q) i have to find out the range where "t" has got range but "t1" does not? that means start range and end range will be passed

and i have to find out the range where "t1" is not done but "t" is done

-- case 1 table "t" and "t1" will have no overlap  with in the table

-- case 2 they will have overlap with in the table.

result

100 -200

600 - 800

yours sincerley