Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: T-SQl find available ranges

    Hi Everyone,
    I have a t sql question that hopefully someone can help me with.

    I have rows that list taken ranges for a given StudyID. I need to find the available ranges. The total ranges can be from 0 to 99999.
    Example:
    StudyID 4 has 3 rows of taken ranges.

    RowID StudyID From_Range To_Range
    1 4 100 200
    5 4 300 350
    7 4 500 600

    I need to list StudyID with the available ranges. The output would be
    StudyID Available Range
    4 0-99
    4 201-299
    4 351-499
    4 601-99999

    Any suggestions for the code to proccess would be greatly appreciated.

    Thanks

    Rebecca

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What version of SQL Server?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2008
    Posts
    56
    Sorry for not posting that part.
    I am using SQL Server 2005.

    Rebecca

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @studies table (
       row_id     int
     , study_id   int
     , from_range int
     , to_range   int
    )
    
    INSERT INTO @studies (row_id, study_id, from_range, to_range)
          SELECT 1, 4, 100, 200
    UNION SELECT 5, 4, 300, 350
    UNION SELECT 7, 4, 500, 600
    
    SELECT Coalesce(u.study_id, l.study_id) As [study_id]
         , Coalesce(l.boundary + 1, 0)   As [lower]
         , Coalesce(u.boundary - 1, 999) As [upper]
    FROM   (
            SELECT row_id
                 , study_id
                 , to_range As [boundary]
                 , Row_Number() OVER (PARTITION BY study_id ORDER BY row_id) As [n]
            FROM   @studies
           ) As [l]
     FULL
      JOIN (
            SELECT row_id
                 , study_id
                 , from_range As [boundary]
                 , Row_Number() OVER (PARTITION BY study_id ORDER BY row_id) As [n]
            FROM   @studies
           ) As [u]
        ON l.study_id = u.study_id
       AND l.n + 1 = u.n
    ORDER
        BY [study_id]
         , [lower]
    Last edited by gvee; 12-18-08 at 11:48.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ach, scratch, there's a mistake there...
    EDIT: fixed
    Last edited by gvee; 12-18-08 at 11:47.
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2008
    Posts
    56
    Thats it.
    Thanks so much George.
    I'll buy the next round of beers.

    Rebecca

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I accept paypal



    George
    Home | Blog

  8. #8
    Join Date
    Apr 2007
    Posts
    183

    Nice solution

    Georgev provided a nice solution, but you have to take into consideration that there will be problems if you have overlapping ranges in @Studies source table.
    They do not even have to be overlapping. Try Georgev's solution with this perfectly valid sample data...
    Code:
    INSERT	@Studies
          SELECT 1, 4, 100, 299
    UNION SELECT 5, 4, 300, 350
    UNION SELECT 7, 4, 500, 600
    It will return this resultset
    Code:
    study_id	lower	upper
    4	0	99
    4	300	299
    4	351	499
    4	601	999
    I don't think that is what you want. You can of course add an extra WHERE clause to Georgev's solution as
    Code:
       where  Coalesce(l.boundary + 1, 0) <= Coalesce(u.boundary - 1, 99999)
    That WHERE clause will fix most bugs but you still have problem if there are overlapping ranges in @Studies source table.

    This solution is not elegant nor new. But it will get the job done and fix any error in the source table, such as overlapping ranges and so on...

    Use same sample data table @Source as Georgev provided
    Code:
    -- Create complete valid range
    CREATE TABLE	#Range
    		(
    			RangeID INT PRIMARY KEY CLUSTERED
    		)
    
    INSERT		#Range
    		(
    			RangeID
    		)
    SELECT		TOP 100000
    		2048 * v1.Number + v2.Number
    FROM		master..spt_values AS v1
    INNER JOIN	master..spt_values AS v2 ON v2.type = 'P'
    WHERE		v1.Type = 'P'
    ORDER BY	2048 * v1.Number + v2.Number
    
    -- Create staging table for studies and ranges
    CREATE TABLE	#Stage
    		(
    			StudyID INT NOT NULL,
    			RangeID INT NOT NULL,
    			PRIMARY KEY CLUSTERED
    			(
    				StudyID,
    				RangeID
    			),
    			SequenceID INT
    		)
    
    INSERT		#Stage
    		(
    			StudyID,
    			RangeID
    		)
    SELECT		s.Study_ID,
    		r.RangeID
    FROM		(
    			SELECT		Study_ID
    			FROM		@Studies
    			GROUP BY	Study_ID
    		) AS s
    CROSS JOIN	#Range AS r
    LEFT JOIN	@Studies AS w ON w.Study_ID = s.Study_ID
    			AND w.From_Range <= r.RangeID
    			AND w.To_Range >= r.RangeID
    WHERE		w.Study_ID IS NULL
    ORDER BY	s.Study_ID,
    		r.RangeID
    
    -- Prepare sequencing
    DECLARE	@StudyID INT,
    	@RangeID INT,
    	@SequenceID INT
    
    SELECT TOP 1	@StudyID = StudyID,
    		@RangeID = RangeID,
    		@SequenceID = 0
    FROM		#Stage
    ORDER BY	StudyID,
    		RangeID
    
    UPDATE	s
    SET	@SequenceID = SequenceID =	CASE
    						WHEN StudyID = @StudyID AND RangeID = 1 + @RangeID THEN @SequenceID
    						ELSE @SequenceID + 1
    					END,
    	@StudyID = StudyID,
    	@RangeID = RangeID
    FROM	#Stage AS s WITH (INDEX (0))
    
    -- Display the wanted result
    SELECT		StudyID,
    		MIN(RangeID) AS LowLevel,
    		MAX(RangeID) AS HighLevel
    FROM		#Stage
    GROUP BY	StudyID,
    		SequenceID
    ORDER BY	StudyID,
    		SequenceID
    
    -- Clean up
    DROP TABLE	#Stage,
    		#Range

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well spotted Peso, thanks. It was not something that I considered when I wrote my example.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •