# how to find gaps between range

• rajemessage 14195

Hall of Fame

Points: 3125

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 means 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.

yours sincerely

• Jonathan AC Roberts

SSCoach

Points: 17166

I can't say that I understand your question. What do you mean by: "--ex "t"  has 1,200 , "t1"  1,100 means i want to see 100 to 99 t2 does not have any thing."

why would you want to see 100 to 99?

Can you explain your question more clearly?

• rajemessage 14195

Hall of Fame

Points: 3125

sorry it was 100 to 200

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);

result ( pls find where t has range and t1 does not)

100 200

600 800

SSChasing Mays

Points: 655

How does table t join to table t1?  Are the rows intended to be in sequential 1-to-1 correspondence?  If yes, why not create only 1 table instead of 2?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• rajemessage 14195

Hall of Fame

Points: 3125

1) two tables store range data related to two different activities, but for simplicity, I have removed other cols.

2) any range could be entered as long as decimal(13,4) supports.

3) I have to find out the range where  "t1" activity is not done but "t" activity is done?

SSChasing Mays

Points: 655

rajemessage 14195 wrote:

1) two tables store range data related to two different activities, but for simplicity, I have removed other cols.

Currently there's no way to join rows from table t to rows in table t1.  Are any of the columns you've removed present in both of the two tables?

rajemessage 14195 wrote:

2) any range could be entered as long as decimal(13,4) supports.

In your example "t" has 1,200 , "t1" 1,100 [and the expected answer is] "100 to 200".  Doesn't the value 100 overlap?  If any range could be entered then wouldn't you have to consider possible decimal values?  Why isn't the expected range 100.0001 to 200?

rajemessage 14195 wrote:

3) I have to find out the range where  "t1" activity is not done but "t" activity is done?

What if the "t" range completely encompasses the "t1" range?  Doesn't it result in 2 non-contiguous ranges?  Suppose "t" is 100,400 and "t1" is 200,300 what is/are the expected range(s)?  Would it be both 100 to 199.9999 and 300.0001 to 400?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• drew.allen

SSC Guru

Points: 76707

I believe that this solves your problem.  It's a variation on interval packing as discussed by Itzik Ben-Gan.

create table #t ( b decimal(13,4) not null, c decimal(13,4) not null);

Insert into #T (B,C)
values
(1, 200)
, (200, 400)
, (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)
, (200, 500)
, (800, 1000);

/****
This method uses half-closed intervals.
****/
WITH Prices AS
(
SELECT p.price
FROM #t AS t
CROSS APPLY (VALUES(t.b), (t.c)) p(price)

UNION

SELECT p.price
FROM #t1 AS t
CROSS APPLY (VALUES(t.b), (t.c)) p(price)
)
, intervals AS
(
SELECT LAG(p.price, 1) OVER(ORDER BY p.price) AS interval_start, p.price AS interval_end
FROM Prices AS p
)
SELECT i.interval_start AS b, i.interval_end AS c
FROM intervals AS i
INNER JOIN #t AS t
ON i.interval_start < t.c
AND t.b < i.interval_end
LEFT OUTER JOIN #t1 AS t1
ON i.interval_start < t1.c
AND t1.b < i.interval_end
WHERE t1.b IS NULL

NOTE: This uses half-closed intervals (the start point is included in the range, but the end point is not).

Drew

J. Drew Allen

• rajemessage 14195

Hall of Fame

Points: 3125

thank u , i am working on it.

-- b and c col can get reversed range like insted of 2 to 5 one can enter 5 to 2 , so i have added some code for it.

similarly from overlap we have to show only one tid the last one so have added some code for it also.

-- b and e could be reversed

-- and show only latest tid from #t table in case of overlap

DECLARE

@decBeginStation DECIMAL(13,4)=-2,

@decEndStation DECIMAL(13,4)=10

CREATE table #t ( tid int, b decimal(13,4) not null, E decimal(13,4) not null);

Insert into #T (tid,B,E)

VALUES (1, -12, -2),

(2, -10, 0),

(3, -2, 8),

(4, 10, 0),

(5, 8, 18),

(6, 10, 20),

(7, 21, 11),

(8, 0, 3),

(9, 2, 4),

(10, 3, 10),

(11, -1, 10),

(12, -1, 11),

(13, 0, 11)

;

create table #t1 (t1id int,b decimal(13,4) not null, E decimal(13,4) not null);

Insert into #t1 (t1id,B,E)

VALUES (1, -12, -2),

(2, -10, 0),

(3, 5, -2)

--(4, 0, 10),

--(5, 8, 18),

--(6, 10, 20),

-- (7, 11, 21),

-- (8, 0, 3),

-- (9, 2, 4),

-- (10, 3, 10),

-- (11, -1, 10),

-- (12, -1, 11),

-- (13, 0, 11)

;

/****

This method uses half-closed intervals.

****/

select * into

#tt from #t AS t

WHERE (

NOT (

@decBeginStation >= t.b

AND @decBeginStation >= t.E

AND @decEndStation >= t.b

AND @decEndStation >= t.E

)

AND NOT (

@decBeginStation <= t.b

AND @decBeginStation <= t.E

AND @decEndStation <= t.b

AND @decEndStation <= t.E

)

)

SELECT * into #tt1

FROM #t1 AS t

WHERE (

NOT (

@decBeginStation >= t.b

AND @decBeginStation >= t.E

AND @decEndStation >= t.b

AND @decEndStation >= t.E

)

AND NOT (

@decBeginStation <= t.b

AND @decBeginStation <= t.E

AND @decEndStation <= t.b

AND @decEndStation <= t.E

)

)

; WITH Prices AS

(

SELECT p.price

FROM #tt AS t

CROSS APPLY (VALUES(t.b), (t.E)) p(price)

UNION

SELECT p.price

FROM #tt1 AS t

CROSS APPLY (VALUES(t.b), (t.E)) p(price)

UNION

SELECT @decBeginStation price

UNION

SELECT @decEndStation price

)

, intervals AS

(

SELECT LAG(p.price) OVER(ORDER BY p.price) AS interval_start, p.price AS interval_end

FROM Prices AS p WHERE P.price >=@decBeginStation AND P.price <=@decEndStation

)

--SELECT * FROM INTERVALS ORDER BY INTERVAL_START

SELECT * FROM (

SELECT

ROW_NUMBER() OVER(PARTITION BY I.interval_start ORDER BY t.tid desc ) RN, T.TID,

i.interval_start AS b, i.interval_end AS E

FROM intervals AS i

INNER JOIN #tt AS t

ON i.interval_start < t.E

AND t.b < i.interval_end

LEFT OUTER JOIN #tt1 AS t1

ON i.interval_start < t1.E

AND t1.b < i.interval_end

WHERE t1.b IS NULL

) T

WHERE T.RN =1

drop table #t

drop table #t1

drop table #tt

drop table #tt1

• rajemessage 14195

Hall of Fame

Points: 3125

-- in following "#t" table one can enter b as 10 and e as 2 that means e can be smaller than b and b can be smaller than e

-- for that i have used following logic in where clause i want to known is there any short way to do it with out putting small value in "b" and bigger in "e"

DECLARE

@decBeginStation DECIMAL(13,4)=-2,

@decEndStation DECIMAL(13,4)=10

CREATE table #t ( tid int, b decimal(13,4) not null, E decimal(13,4) not null);

Insert into #T (tid,B,E)

VALUES (1, -12, -2),

(2, -10, 0),

(3, -2, 8),

(4, 10, 0),

(5, 8, 18),

(6, 10, 20),

(7, 21, 11),

(8, 0, 3),

(9, 2, 4),

(10, 3, 10),

(11, -1, 10),

(12, -1, 11),

(13, 0, 11)

;

select * from #t AS t

WHERE (

NOT (

@decBeginStation >= t.b

AND @decBeginStation >= t.E

AND @decEndStation >= t.b

AND @decEndStation >= t.E

)

AND NOT (

@decBeginStation <= t.b

AND @decBeginStation <= t.E

AND @decEndStation <= t.b

AND @decEndStation <= t.E

)

)

drop table #t

Viewing 9 posts - 1 through 9 (of 9 total)