how to find gap in a range.

rky

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

DerFredo

Off the top of my head, that sounds like a variation of the "gaps and islands" problem. In T-SQL you can use window functions (or other ways) to solve the basic problem, maybe you can extend that approach. Look here, for example: https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

johnwilliam23

You need to identify any ranges not included between 0 and 1. Note that some ranges do overlap. The exclusivity of the endpoints is not relevant for use case; You only need to identify what the endpoints of the gaps.

Here is the table structure:

DECLARE @ CheckRegister TABLE

(

   AccountNumber Varchar(10),

   CheckNumber Varchar(10)

)

John,
Essay Writer