Maybe one of you SQL gurus can help me out

  • I need to query a list of numbers and find out how much space is between them. e.g.:

    1

    10, 8 blanks

    12, 1 blank

    17, 4 blanks

    They are changing our Account numbers at work from numbers with implied meaning to just straight numbers from here on out, but they don't want to renumber existing accounts, so they asked me to find an unused block of numbers, and I'm not coming up with a good way to do it just in T-SQL.

  • -- Prepare sample data

    DECLARE @Sample TABLE (Number INT)

    INSERT @Sample

    SELECT 1 UNION ALL

    SELECT 10 UNION ALL

    SELECT 12 UNION ALL

    SELECT 17

    -- Try 1

    SELECT  LowLimit.Number + 1 AS FromNumber,

      (

       SELECT TOP 1 Number

       FROM  @Sample AS HighLimit

       WHERE  NOT EXISTS (

            SELECT B.Number

            FROM @Sample AS b

            WHERE HighLimit.Number - 1 = B.Number

          &nbsp

         AND HighLimit.Number > LowLimit.Number

       ORDER BY Number

     &nbsp - 1 AS ToID

    FROM  @Sample AS LowLimit

    WHERE  NOT EXISTS (

         SELECT b.Number

         FROM @Sample AS b

         WHERE LowLimit.Number + 1 = B.Number

       &nbsp

      AND LowLimit.Number < (SELECT MAX(Number) FROM @Sample)

    ORDER BY LowLimit.Number

    -- Try 2

    SELECT s.Number,

     s.Number - (SELECT MAX(z.Number) FROM @Sample AS z WHERE z.Number < s.Number) - 1

    FROM @Sample AS s


    N 56°04'39.16"
    E 12°55'05.25"

  • Or this:

    drop table MyTable

    create table MyTable

    ( myid int

    )

    go

    insert mytable select 1

    insert mytable select 10

    insert mytable select 12

    insert mytable select 17

    insert mytable select 21

    select a.MyID

    , (b.myid - a.myid - 1) 'blanks'

    from MyTable a, MyTable B

    where b.MyID = (select min(myid)

    from MyTable c

    where c.MyID > a.MyID)

    blanks offset by 1, so 1 has 8 following blanks, 10 has 1, etc.

  • Sean

    Something like this?  Not tested, so syntax may not be perfect.

    CREATE TABLE #Gaps (RowNo int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

                                  AccNo int, Blanks int)

    INSERT INTO #Gaps (AccNo) SELECT AccNo FROM YourTable

    UPDATE g1

    SET Blanks = g1.AccNo - g2.AccNo - 1

    FROM #Gaps g1 JOIN #Gaps g2

    ON g1.RowNo = g2.RowNo - 1

    SELECT AccNo, Blanks

    FROM #Gaps

    ORDER BY Blanks DESC

    John

  • I was going to suggest John's method, however I'd recommend that you probably want to make sure that on the insert you use an ORDER BY clause to make sure that they're put in the correct ascending order.

  • Thanks guys, I did it John's way, with the added Order By Aaron suggested, and I changed this one line;

    SET Blanks = g1.AccNo - g2.AccNo - 1

    to

    SET Blanks = Abs(g1.AccNo - g2.AccNo + 1)

    to get a positive number of blanks.

    Thanks alot everybody.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply