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

  • scdecade

    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?

  • scdecade

    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
    Business Intelligence Analyst
    Philadelphia, PA

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

You must be logged in to reply to this topic. Login to reply