Declare @tbl1 table 
(ID INT,
StartNo varchar(50),
EndNo varchar(50),
SkipNo varchar(255)
)

INSERT INTO @tbl1
SELECT 1, 'ABCD00010001','ABCD00010005',NULL

INSERT INTO @tbl1
SELECT 2, 'EFGH00010006','ABCD00010015',NULL

--INSERT INTO @tbl1
--SELECT 3, 'ABCD00010050','ABCD00010101',NULL
--
--INSERT INTO @tbl1
--SELECT 4, 'ABCD00010500','ABCD00010600',NULL
 
DECLARE @Table TABLE (id int,skipno char(12))

declare @skipno char(12)
declare @skipdiff int
declare @ctr int
declare @root int
declare @prefix char(4)
declare @id  int

set @id = 1


WHILE @id < 5
BEGIN
	   SET @ctr = 1
	   SET @skipdiff = (select convert(int,right(endno,8)) -  convert(int,right(startno,8))
						from @tbl1
						where id = @id)
	   SET @prefix = (select left(startno,4) 
						from @tbl1
						where id = @id)
	   SET @root = (select  convert(int,right(startno,8)) 
						from @tbl1
						where id = @id)
  WHILE @ctr < @skipdiff
  BEGIN
     --select @ctr,@skipdiff,@prefix,@root,@skipno
     set @skipno =  @prefix + RIGHT('00000000'+convert(varchar(8),@root+@ctr) ,8)
  
     set @ctr = @ctr + 1
     INSERT INTO @Table SELECT @id,@Skipno
    
  END --@ctr < @skipdiff
	 

   SET @id = @id + 1
 END --@id < 5

 
select distinct 
   t1.id
   ,[List] =  substring( 
      ( select ', ' + skipno-- as [SkipNumber]
         from @table t2
         where t2.id = t1.id
         for xml path(''), elements ),2,8000)
     
from @table t1
   

SET NOCOUNT ON;
DECLARE @i AS INT;
  SET @i = (SELECT MAX(ID) FROM @tbl1);
DECLARE @min AS INT;  
  SET @min = (SELECT MIN(ID) FROM @tbl1);
-- update the SkipNo column to '' instead of NULL to work with code later on...
UPDATE @tbl1 SET SkipNo = '';
WHILE @i >= @min
BEGIN
IF (SELECT CONVERT(INT,RIGHT(EndNo,8)) - CONVERT(INT,RIGHT(StartNo,8)) FROM @tbl1 WHERE ID = @i) IS NOT NULL
 
  BEGIN  
    DECLARE @ii AS INT;
      SET @ii = (SELECT MAX(CONVERT(INT,RIGHT(EndNo,8)) - CONVERT(INT,RIGHT(StartNo,8))-1) FROM @tbl1 WHERE ID = @i);
    WHILE @ii > 0
    BEGIN
      UPDATE @tbl1 
      SET SkipNo = LEFT(StartNo,4)+RIGHT('00000000'+CONVERT(varchar,(convert(int,right(StartNo,8) + @ii))),8) + ';' + SkipNo 
      WHERE ID = @i;
    SET @ii = @ii - 1;
    END 
    SET @i = @i - 1;
  END

ELSE
	SET @i = @i - 1;
END

quote:

---

Originally posted by jimf

Probably done in about 20 lines or less.

---

DECLARE	@Sample TABLE 
	(
		ID INT,
		StartNo CHAR(12),
		EndNo CHAR(12),
		SkipNo VARCHAR(MAX)
	)

INSERT	@Sample
SELECT	1, 'ABCD00010001', 'ABCD00010005', NULL UNION ALL
SELECT	2, 'ABCD00010006', 'ABCD00010015', NULL UNION ALL
SELECT	3, 'ABCD00010050', 'ABCD00010101', NULL UNION ALL
SELECT	4, 'ABCD00010500', 'ABCD00010600', NULL

SELECT		s.ID,
		s.StartNo,
		s.EndNo,
		f.g AS SkipNo
FROM		@Sample AS s
CROSS APPLY	(
			SELECT	LEFT(s.StartNo, 4) + REPLACE(STR(1 * RIGHT(s.StartNo, 8) + v.Number, 8), ' ', '0') + ';'
			FROM	master..spt_values AS v 
			WHERE	v.Type = 'P'
				AND v.Number >= 1
				AND v.Number < 1 * RIGHT(s.EndNo, 8) - 1 * RIGHT(s.StartNo, 8)
			FOR XML	PATH('')
		) AS f(g)

SELECT		s.ID,
		s.StartNo,
		s.EndNo,
		s.Items,
		s.Intro,
		f.g AS SkipNo
FROM		(
			SELECT	ID,
				StartNo,
				EndNo,
				LEFT(StartNo, 4) AS Prefix, 
				1 * RIGHT(StartNo, 8) AS Intro,
				1 * RIGHT(EndNo, 8) - 1 * RIGHT(StartNo, 8) - 1 AS Items
			FROM	@Sample
		) AS s
CROSS APPLY	(
			SELECT	s.Prefix + REPLACE(STR(s.Intro + v.Number + 1, 8), ' ', '0') + ';'
			FROM	master..spt_values AS v 
			WHERE	v.Type = 'P'
				AND v.Number < s.Items
			FOR XML	PATH('')
		) AS f(g)

quote:

---

Originally posted by jimf

Geez, took you long enough!

---