Results 1 to 8 of 8
  1. #1
    Join Date
    May 2010
    Posts
    7

    Unanswered: Split ranges in Database

    I have been wracking my brain on this. Is there a way to write an SQL code that will combine split ranges within a table?

    Table Structure
    SAMPLE_TABLE
    GROUP_ID (PK, int, not null)
    TP_LOW (varchar(50), null)
    TP_HIGH (varchar(50), null)
    TP_LOW and TP_HIGH are numerical values, but sometimes I will have information with an Alphanumeric value (i.e. A1412, i can correct this by using a left function)

    SAMPLE_TABLE
    GROUP_ID TP_LOW TP_HIGH
    1 123 123 (Keep this row but update high value to the one below)
    1 124 3000 (Delete this row, since the row above will be updated to include information in this row)
    1 3002 3005 (Keep this row but update high value to include information from the rows below)
    1 3006 4000* (Delete this row, since it is include above)
    1 3500 3501 (Delete this row, since it is include above)
    1 3006 4500* (Delete this row, since it is include above)

    I would like to combine any ranges that may exist.
    *It is possible that some row ranges may be nested in other rows.

    END_RESULT (Query Result)
    GROUP_ID TP_LOW TP_HIGH
    1 123 3000
    1 3002 4500

    Any help would be appreciated.

    Thanks in advance.
    Last edited by acecommon; 05-05-10 at 16:16.

  2. #2
    Join Date
    May 2010
    Posts
    7
    Since I have not gotten an answer for a while, I will try to restate this in a different way. I am in need of a script in SQL SERVER MANAGEMENT STUDIO that can do the following:

    I am attempting to combine/collapse rows of data together. There is a common identifier called Group_ID for each row I want to combine/collapse together. TP_LOW and TP_HIGH contain mostly numeric values of 3 - 7 characters. (There are some alphanumeric rows in there hence the varchar datatype, but they are negligible)


    CREATE TABLE sample_table(GROUP_ID int, TP_LOW varchar(50), TP_HIGH varchar(50))
    INSERT INTO sample_table(GROUP_ID, TP_LOW, TP_HIGH)
    SELECT '1', '123', '123' UNION ALL
    SELECT '1', '113', '113' UNION ALL
    SELECT '1', '114', '3000' UNION ALL
    SELECT '1', '3001', '3005' UNION ALL
    SELECT '1', '3006', '4000' UNION ALL
    SELECT '1', '3500', '3501' UNION ALL
    SELECT '1', '3006', '4500' UNION ALL
    SELECT '2', '223', '223' UNION ALL
    SELECT '2', '224', '3000' UNION ALL
    SELECT '2', '3002', '3005' UNION ALL
    SELECT '2', '3500', '3502' UNION ALL
    SELECT '2', '3500', '3502' UNION ALL
    SELECT '2', '3006', '4500' UNION ALL


    End result should look like:
    Code:
    GROUP_ID   TP_LOW    TP_HIGH                                                   
    ---------  ---------  ----------------------------------------------------- 
    1            113      3000
    1            3001     4500
    2            223      3000
    2            3002     4500
    Any help would be appreciated. Thanks in advanced.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't understand why the values 3000 and 3001, and 3000 and 3002 result in a split there. Why not:
    Code:
    GROUP_ID  TP_LOW    TP_HIGH
    -----------------------------------
    1         113       4500
    2         223       4500

  4. #4
    Join Date
    May 2010
    Posts
    7
    You are correct in the case of GROUP_ID 1 pootle flump. (I made a mistake in the result set. This is also the reason why I need code to do it instead of manually correcting. nice catch).

    However for GROUP_ID 2, there is a range difference

    SELECT '2', '224', '3000' UNION ALL
    SELECT '2', '3002', '3005' UNION ALL

    So the result set should look something like this:
    Code:
    GROUP_ID   TP_LOW    TP_HIGH                                                   
    ---------  ---------  ----------------------------------------------------- 
    1            113      4500
    2            223      3000
    2            3002     4500

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Right - I understand now the results are correct.

    Have you googled this? I confess I forget the various solutions but this is a requirement that comes up every so often. Often in the form of "gaps in ranges".
    Meanwhile, I'll have a think.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, although a "negligible" number of alphas, they need to be handled. What happens with those?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, TP_HIGH and TP_LOW are marked as nullable - what happens when they are null?

  8. #8
    Join Date
    May 2010
    Posts
    7
    I tried Google with other keywords and was unable to locate a solution, which is why I posted on this board. I will try to search with "gap in ranges".

    The alphas are attached to numeric values which I can correct with a left or right function. I will be able to learn from the SQL code (if it is at all possible) that is posted and adjust accordingly.

    Null values will be deleted from the table (This is handled by another cleanup query I have. There are currently no null values)

    Thanks for your assistance so far pootle flump and I am looking forward to your response.

Posting Permissions

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