T-SQL query - row iteration without cursor

Question

I have a table

T (variable_name, start_no, end_no) 

that holds values like:

(x, 10, 20)
(x, 30, 50)
(x, 60, 70)
(y, 1, 3)
(y, 7, 8)

All intervals are guaranteed to be disjoint.

I want to write a query in T-SQL that computes the intervals where a variable is not searched:

(x, 21, 29)
(x, 51, 59)
(y, 4, 6)

Can I do this without a cursor?

I was thinking of partitioning by variable_name and then ordering by start_no. But how to proceed next? Given the current row in the rowset, how to access the "next" one?

Answer 1

Since you didn't specify which version of SQL Server, I have multiple solutions. If you have are still rocking SQL Server 2005, then Giorgi's uses CROSS APPLY quite nicely.

Note: For both solutions, I use the where clause to filter out improper values so even if the the data is bad and the rows overlap, it will ignore those values.

My Version of Your Table

DECLARE @T TABLE (variable_name CHAR, start_no INT, end_no INT) 
INSERT INTO @T
VALUES  ('x', 10, 20),
        ('x', 30, 50),
        ('x', 60, 70),
        ('y', 1, 3),
        ('y', 7, 8);

Solution for SQL Server 2012 and Above

SELECT *
FROM
(
    SELECT  variable_name,
            LAG(end_no,1) OVER (PARTITION BY variable_name ORDER BY start_no) + 1 AS start_range,
            start_no - 1 AS end_range
    FROM @T
) A
WHERE end_range > start_range

Solution for SQL 2008 and Above

WITH CTE
AS
(
    SELECT  ROW_NUMBER() OVER (PARTITION BY variable_name ORDER BY start_no) row_num,
            *
    FROM @T
)

SELECT  A.variable_name,
        B.end_no + 1 AS start_range,
        A.start_no - 1 AS end_range
FROM CTE AS A
INNER JOIN CTE AS B
ON      A.variable_name = B.variable_name
    AND A.row_num = B.row_num + 1
WHERE A.start_no - 1 /*end_range*/ > B.end_no + 1 /*start_range*/

Answer 2

Here is another version with cross apply:

DECLARE @t TABLE ( v CHAR(1), sn INT, en INT )
INSERT  INTO @t
VALUES  ( 'x', 10, 20 ),
        ( 'x', 30, 50 ),
        ( 'x', 60, 70 ),
        ( 'y', 1, 3 ),
        ( 'y', 7, 8 );

SELECT t.v, t.en + 1, c.sn - 1 FROM @t t
CROSS APPLY(SELECT TOP 1 * FROM @t WHERE v = t.v AND sn > t.sn ORDER BY sn)c
WHERE t.en + 1 < c.sn

Fiddle http://sqlfiddle.com/#!3/d6458/3