T-SQL query - iterate a row without a cursor

Question

I have a table

T (variable_name, start_no, end_no) 

which contains values ​​such as:

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

All intervals are guaranteed not to intersect.

I want to write a query in T-SQL that calculates the intervals at which a variable is not searched:

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

Is it possible to do this without a cursor?

I thought about splitting variable_name and then ordered start_no. But how to proceed further? Given the current row in the rowset, how do I access the "next"?

Answer 1

Since you did not specify which version of SQL Server, I have several solutions. If you are still downloading SQL Server 2005, then Giorgi uses CROSS APPLY very well.

Note. . For both solutions, I use the where clause to filter out invalid values, so even if the data is bad and the lines overlap, they will ignore these values.

My version of the 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 higher

 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 higher

 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 tv, t.en + 1, c.sn - 1 FROM @tt CROSS APPLY(SELECT TOP 1 * FROM @t WHERE v = tv AND sn > t.sn ORDER BY sn)c WHERE t.en + 1 < c.sn 

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

Answer 3

For each end_no you should find the nearest start_no > end_no , and then exclude the lines without the nearest start_no (last lines for variable_name )

 WITH A AS ( SELECT variable_name, end_no+1 as x1, (SELECT MIN(start_no)-1 FROM t WHERE t.variable_name = t1.variable_name AND t.start_no>t1.end_no) as x2 FROM t as t1 ) SELECT * FROM A WHERE x2 IS NOT NULL ORDER BY variable_name,x1 

SQLFiddle demo

And here is my old answer to a similar question:

Allen Allen Algorithms in SQL

Answer 4

This is very portable since it does not require CTE or analytic functions. I could also easily chat without a view if it was ever needed.

 select * from ( select variable_name, end_no + 1 as start_no, ( select min(start_no) - 1 from T as t2 where t2.variable_name = t1.variable_name and t2.start_no > t1.end_no ) as end_no from T as t1 ) as intervals where start_no <= end_no 

The number of supplemented intervals will be a maximum of one less than you start. (Some of them will be eliminated if the two ranges were actually consecutive.) Thus, it is easy to take each individual interval and calculate it just to the right (or left if you want to cancel part of the logic).

Answer 5

This uses a version other than CTE that works: http://sqlfiddle.com/#!9/4fdb4/1

Given the guaranteed disjoint ranges, I simply joined T to myself, calculated the next range as an increment / decrement of the adjacent range, and then ensured that the new range did not overlap the existing ranges.

 select t1.variable_name, t1.end_no+1, t2.start_no-1 from t t1 join t t2 on t1.variable_name=t2.variable_name where t1.start_no < t2.start_no and t1.end_no < t2.end_no and not exists (select * from t where ((t2.start_no-1< t.end_no and t1.end_no+1 > t.start_no) or (t1.end_no + 1 < t.end_no and t2.start_no-1 > t.end_no)) and t.variable_name=t1.variable_name)