CREATE TABLE #dcc(Low INT NOT NULL PRIMARY KEY CHECK(Low>=0), High INT UNIQUE, Span AS High-Low+1, CHECK (High>Low))
INSERT #dcc(Low,High) VALUES(10,19),(20,49),(100,199),(200,219),(320,359),(400,599)

Low	High	Number
10	19	10
20	49	20
20	49	30
20	49	40
100	199	100
200	219	200
200	219	210
320	359	320
320	359	330
320	359	340
320	359	350
400	599	400
400	599	500

;WITH cte AS
(
	SELECT
		ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
		1 AS lvl,
		*,
		LOW AS Number,
		POWER(10,FLOOR(LOG10(span))) AS interval
	FROM
		#dcc
	UNION ALL
	SELECT
		RN,
		lvl+1,
		c.low,
		c.high,
		c.span,
		Number + Interval,
		Interval
	FROM
		cte c
	WHERE
		number + Interval < HIGH
)
SELECT LOW, HIGH, number FROM cte ORDER BY RN,lvl;

WITH Numbers
AS (SELECT 1              AS  n
    UNION ALL
    SELECT n+1
    FROM   Numbers
    WHERE  n               <  100),
DataWithSpanAndInterval
AS (SELECT Low,
           High,
          (High - Low + 1) AS  Span,
           POWER(10, CEILING(LOG10(High - Low + 2)) - 1)
                           AS  Interval
    FROM   #dcc)
SELECT     Low,
           High,
           Low + (n - 1) * Interval
                           AS  Number
FROM       DataWithSpanAndInterval
INNER JOIN Numbers
      ON   Low + (n - 1) * Interval <= High
ORDER BY   Low, Number;

CREATE TABLE #dcc(Low INT NOT NULL PRIMARY KEY CHECK(Low>=0), High INT UNIQUE, Span AS High-Low+1, CHECK (High>Low))
INSERT #dcc(Low,High) VALUES(10000,10279),(583040,583999)

INSERT #dcc(Low,High) VALUES(12340,12869),(14660,15029);

;WITH cteRawIntervals AS -- Candidate intervals for the starting and ending
(
   SELECT
      LOW,HIGH, span,
      POWER(10,FLOOR(LOG10(span))) AS MaxInterval,
      POWER(10,PATINDEX('%[^0]%', REVERSE(CAST(LOW AS VARCHAR(32))))-1) AS StartInterval,
      POWER(10,PATINDEX('%[^0]%', REVERSE(CAST(HIGH+1 AS VARCHAR(32))))-1) AS EndInterval
   FROM #dcc
),
cteIntervals AS -- Actual intervals at the start and end
(
   SELECT
      LOW,HIGH,span,MaxInterval,
      CASE WHEN StartInterval > MaxInterval THEN MaxInterval ELSE StartInterval END AS StartInterval,
      CASE WHEN EndInterval > MaxInterval THEN MaxInterval ELSE EndInterval END AS EndInterval
   FROM cteRawIntervals
),
cteSpans AS
(
   SELECT
      1 AS lvl,
      LOW, HIGH, StartInterval,EndInterval,MaxInterval,
      LOW AS Number,
      StartInterval AS CurrentInterval -- start with the StartInterval
   FROM
      cteIntervals
   UNION ALL
   SELECT
      lvl+1,
      c.low, c.high,c.StartInterval,c.EndInterval,c.MaxInterval,
      Number + CurrentInterval,
      CASE  
         WHEN CurrentInterval > EndInterval AND HIGH-(Number+CurrentInterval) < CurrentInterval 
            THEN CurrentInterval/10 -- scale down
         WHEN CurrentInterval < MaxInterval AND (Number +CurrentInterval)%(CurrentInterval*10) = 0 
            AND HIGH < (Number+10*CurrentInterval)THEN CurrentInterval -- leave it alone
         WHEN CurrentInterval < MaxInterval AND (Number +CurrentInterval)%(CurrentInterval*10) = 0 
            THEN CurrentInterval*10 -- scale up
         ELSE CurrentInterval -- leave it alone
      End
   FROM
      cteSpans c
   WHERE
      number + CurrentInterval < HIGH
)
SELECT LOW,HIGH,Number FROM cteSpans ORDER BY LOW,lvl;

quote:

---

Originally posted by HugoKornelis

Okay, before I dive into this - what is the output you want from this input:

INSERT #dcc(Low,High) VALUES(12340,12869),(14660,15029);

---

quote:

---

Originally posted by sunitabeck
Rob, I didn't realize it was you who posted the question. If I had noticed, I would not have dared to pretend to answer!!

---

quote:

---

I don't know why you'd say that, your answer was fine. Not your fault I've got flukey data that doesn't want to behave.

---

quote:

---

This would be ideal:

12340
12350
12360
12370
12380
12390
12400 -- interval 100
12500 -- interval 100
12600 -- interval 100
12700 -- interval 100
12800 -- interval 100
12810
12820
12830
12840
12850
12860

14660
14670
14680
14690
14700 -- interval 100
14800 -- interval 100
14900 -- interval 100
15000 -- interval 100
15010
15020

---

quote:

---

Originally posted by robvolk

Thank you both! These are MUCH better than what I had originally.

I did however find some values in the data I'm working with that befuddle these queries somewhat. I can definitely work around it, but if you're up for an additional challenge.

Setup is the same as before with 2 new rows:

CREATE TABLE #dcc(Low INT NOT NULL PRIMARY KEY CHECK(Low>=0), High INT UNIQUE, Span AS High-Low+1, CHECK (High>Low))
INSERT #dcc(Low,High) VALUES(10000,10279),(583040,583999)

The results from your queries compared with what I need are shown below:

---

DROP TABLE #dcc 

GO 

CREATE TABLE #dcc 
  ( 
     low  INT NOT NULL PRIMARY KEY CHECK(low>=0), 
     high INT UNIQUE, 
     span AS high - low + 1, 
     CHECK (high>low) 
  ) 

INSERT #dcc 
       (low, 
        high) 
VALUES(10000, 
       10279), 
       (583040, 
        583999); 

WITH cte 
     AS (SELECT *, 
                span / Power(10, Len(10) - 1) RANGE, 
                Power(10, Len(10) - 1)INTERVAL 
         FROM   #dcc), 
     cte1 
     AS (SELECT cte.low Low, 
                cte.high High, 
                ( ( INTERVAL * NUMBER ) + cte.low - INTERVAL )Number, 
                ( ( INTERVAL * NUMBER ) + cte.low - INTERVAL )%100 / 10 MOD 
         FROM   cte 
                INNER JOIN MASTER..spt_values val 
                  ON val.NUMBER <= range 
         WHERE  TYPE = 'P'), 
     cte2 
     AS (SELECT low, 
                MIN(NUMBER)MN, 
                MAX(NUMBER)MX 
         FROM   cte1 
         WHERE  mod = 0 
         GROUP  BY low) 
         
SELECT c1.Low,
       c1.High,
       c1.Number
FROM   cte1 c1
       INNER JOIN cte2 c2
         ON c1.Low = c2.Low 
WHERE  ( NUMBER NOT BETWEEN MN AND MX 
          OR MOD = 0 ) 
       AND c2.low <= NUMBER  

DROP TABLE #dcc;
DROP TABLE #powers;
go
CREATE TABLE #dcc(Low INT NOT NULL PRIMARY KEY CHECK(Low>=0), High INT UNIQUE, Span AS High-Low+1, CHECK (High>Low))
INSERT #dcc(Low,High) VALUES(10,19),(20,49),(100,199),(200,219),(320,359),(400,599)
INSERT #dcc(Low,High) VALUES(10000,10279),(583040,583999)
INSERT #dcc(Low,High) VALUES(12340,12869),(14660,15029);
INSERT #dcc(Low,High) VALUES(334000,334999); 
INSERT #dcc(Low,High) VALUES(1172, 1340);
go
CREATE TABLE #powers
 (p     int NOT NULL PRIMARY KEY,
  pNext int NOT NULL);

INSERT INTO #powers
SELECT POWER(10, n-1) AS p, POWER(10, n) AS pNext
FROM   dbo.Numbers
WHERE  n >= 1
AND    n <= CEILING(LOG10((SELECT MAX(Span+1) FROM #dcc)));

SELECT    (d.Low / p.p + n - 1) * p.p AS Low,
          (d.Low / p.p + n) * p.p - 1 AS High,
           p.p AS Number,
           d.Low, d.High, d.Span
FROM       #dcc AS d
INNER JOIN #powers AS p
      ON   p.p <= Span
INNER JOIN Numbers AS n
      ON   n.n >= 1
      AND (d.Low / p.p + n - 1) * p.p >= d.Low
      AND (d.Low / p.p + n) * p.p - 1 <= d.High
      AND(((d.Low / p.p + n - 1) * p.p) / p.pNext * p.pNext < d.Low
       OR ((d.Low / p.p + n - 1) * p.p) / p.pNext * p.pNext + pNext - 1 > d.High)
ORDER BY   1, 2;
go

SELECT     d.Low, d.High,
          (d.Low / p.p + n - 1) * p.p AS Number
FROM       #dcc AS d
INNER JOIN #powers AS p
      ON   p.p <= Span
INNER JOIN Numbers AS n
      ON   n.n >= 1
      AND (d.Low / p.p + n - 1) * p.p >= d.Low
      AND (d.Low / p.p + n) * p.p - 1 <= d.High
      AND(((d.Low / p.p + n - 1) * p.p) / p.pNext * p.pNext < d.Low
       OR ((d.Low / p.p + n - 1) * p.p) / p.pNext * p.pNext + pNext - 1 > d.High)
ORDER BY   1, 2, 3;

quote:

---

Shouldnt the number increase in steps of 10 after 583900 till 583990 ??

---