SQL Server Performance

Script to return string of contiguous id blocks

Discussion in 'Contribute Your SQL Server Scripts' started by Chappy, Oct 23, 2004.

  1. Chappy New Member

    I was asked to write a script which showed blocks of ids, in the form of
    '1-3,5-7,10-11'. It uses a nice trick I learnt from someone on these forms a few weeks ago

    -- create test data
    SET NOCOUNT ON
    drop table test2
    create table test2 (id int)
    insert into test2 (id) values (1)
    insert into test2 (id) values (2)
    insert into test2 (id) values (3)
    insert into test2 (id) values (5)
    insert into test2 (id) values (6)
    insert into test2 (id) values (7)
    insert into test2 (id) values (10)
    insert into test2 (id) values (11)

    -- script starts here
    declare @a varchar(8000)
    select @a = ''
    declare @in bit
    select @in = 0

    declare @isprev bit
    select @isprev = 0
    declare @isnext bit
    select @isnext = 0
    declare @maxid int
    select @maxid = MAX(id) from test2

    select
    @isprev= CASE
    WHEN EXISTS (select a.id from test2 a where a.id = baseT.id-1)
    THEN 1
    ELSE 0
    END,
    @isnext= CASE
    WHEN EXISTS (select a.id from test2 a where a.id = baseT.id+1)
    THEN 1
    ELSE 0
    END,
    @a = @a +
    CASE
    WHEN @isprev=0 and @isnext=1
    THEN CONVERT(varchar,baseT.id)
    WHEN @isprev=1 and @isnext=0
    THEN '-' + CONVERT(varchar,baseT.id) + CASE WHEN baseT.id = @maxid THEN '' ELSE ',' END
    ELSE ''
    END
    from
    test2 baseT

    select @a
  2. Chappy New Member

    And a more conventional way which returns a resultset..

    -- create test data
    SET NOCOUNT ON
    drop table test2
    create table test2 (id int)
    insert into test2 (id) values (1)
    insert into test2 (id) values (2)
    insert into test2 (id) values (3)
    insert into test2 (id) values (5)
    insert into test2 (id) values (6)
    insert into test2 (id) values (7)
    insert into test2 (id) values (10)
    insert into test2 (id) values (11)

    -- script starts here
    select
    "start"=MIN(a.id),
    "end"=(select MIN(b.id) from test2 b where b.id>a.id and
    NOT EXISTS (select c.id from test2 c where c.id = b.id+1))
    from
    test2 a
    where
    NOT EXISTS (select c.id from test2 c where c.id = a.id-1)
    group by
    a.id
  3. derrickleggett New Member

    Well, since we're just throwing convoluted code out there. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><pre><br />-- create test data<br />SET NOCOUNT ON<br /><br />DECLARE @test2 TABLE(id INT)<br />INSERT @test2 (id)<br />SELECT 1 UNION ALL<br />SELECT 2 UNION ALL<br />SELECT 3 UNION ALL<br />SELECT 5 UNION ALL<br />SELECT 6 UNION ALL<br />SELECT 7 UNION ALL<br />SELECT 10 UNION ALL<br />SELECT 11<br /><br />-- script starts here<br />DECLARE @string VARCHAR(2000)<br /><br />SELECT @string = ''<br /><br />SELECT @string = @string +<br />CAST(MIN(a.id) AS VARCHAR(55)) + ' - ' + CAST(<br />(SELECT MIN(b.id) FROM @test2 b WHERE b.id&gt;a.id AND <br />NOT EXISTS (<br />SELECT c.id <br />FROM @test2 c <br />WHERE c.id = b.id+1)) AS VARCHAR(55)) + ','<br />FROM <br />@test2 a <br />WHERE <br />NOT EXISTS (<br />SELECT c.id <br />FROM @test2 c <br />WHERE c.id = a.id-1)<br />GROUP BY<br />a.id<br /><br />SELECT LEFT(@string,LEN(@string)-1)<br /><br /></pre><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  4. Chappy New Member

    <img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page