hi all,

i created table like this..

num Startnum Endnum
1        1             2
2        5             7

3       10           12

4       15            17

5      18             19

you observe the table (3,4) numbers mising.,5-7 means 6 came in that row,8-9 missing,13 -14 missing...like that

so, i want the out put like this ...

Startnum    Endnum

3                  4

8                  9

13                14

what is the query to find out this...

thanks

pradeep

 

This query will produce exactly the output you're looking for. Whether it's the right thing for your application depends on the real problem (which is not completely clear).

DECLARE @t1 TABLE (num smallint, StartNum smallint, EndNum smallint)

INSERT INTO @t1 (num, StartNum, EndNum) VALUES (1,  1,  2)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (2,  5,  7)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (3, 10, 12)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (4, 15, 17)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (5, 18, 19)

--SELECT * FROM @t1

SELECT
    a.EndNum   + 1 AS 'Startnum',
    b.StartNum - 1 AS 'Endnum'
FROM @t1 AS a
JOIN @t1 AS b ON a.num = (b.num - 1)
WHERE (a.EndNum + 1) < (b.StartNum - 1)

hi Garrison,

thanks alot for you giving solution....

 

 

thanks

pradeep

hi,

but  this logic was failed for this inputs ....

num StartNum EndNum
1      10              12
2       17             18
3       1                2
4       5                7
5       15            16
6        21            22

 

actually my requirement is i enter inputs like start Num is 1 and LAst Number 25..in this table not having any identity also..my table having different numbers randomly...then i will get out put as missing Numbers...

 

thanks,

pradeep

Be aware that your second problem is very different from the first. Here's a solution to what I think you're looking for.

Code

DECLARE @t1 TABLE (num smallint, StartNum smallint, EndNum smallint)
DECLARE @t2 TABLE (num smallint IDENTITY, StartNum smallint, EndNum smallint)

INSERT INTO @t1 (num, StartNum, EndNum) VALUES (1, 10, 12)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (2, 17, 18)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (3,  1,  2)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (4,  5,  7)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (5, 15, 16)
INSERT INTO @t1 (num, StartNum, EndNum) VALUES (6, 21, 22)

INSERT INTO @t2 (StartNum, EndNum)
SELECT StartNum, EndNum
FROM @t1
ORDER BY StartNum

SELECT * FROM @t1 ORDER BY num
SELECT * FROM @t2 ORDER BY num

SELECT
    a.EndNum   + 1 AS 'Startnum',
    b.StartNum - 1 AS 'Endnum'
FROM @t2 AS a
JOIN @t2 AS b ON a.num = (b.num - 1)
WHERE (a.EndNum + 1) < (b.StartNum - 1)

Result

   num StartNum EndNum
------ -------- ------
     1       10     12
     2       17     18
     3        1      2
     4        5      7
     5       15     16
     6       21     22

   num StartNum EndNum
------ -------- ------
     1        1      2
     2        5      7
     3       10     12
     4       15     16
     5       17     18
     6       21     22

   Startnum      Endnum
----------- -----------
          3           4
          8           9
         13          14
         19          20