CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Chis
Chis

Reputation: 131

Print the range between 2 numbers

I have this code to find the Gap between 2 numbers : 

;WITH
cte AS (
SELECT
    BSEG_BELNR, 
    RowNum = ROW_NUMBER() OVER (ORDER BY BSEG_BELNR)
FROM dbo.QLIK_JOURNAL GROUP BY BSEG_BELNR),
cte2 AS (
SELECT *, DENSE_RANK() OVER (ORDER BY BSEG_BELNR - RowNum) AS Series 
FROM cte),
cte3 AS (
SELECT *, COUNT(*) OVER (PARTITION BY Series) AS SCount 
FROM cte2),
cte4 AS (
SELECT
    MinID = MIN(BSEG_BELNR), 
    MaxID = MAX(BSEG_BELNR), 
    Series
FROM cte3
GROUP BY Series)
 SELECT a.MaxID,b.MinID
 FROM cte4 a
INNER JOIN cte4 b
    ON a.Series+1 = b.Series
    ORDER BY a.MaxID

and this code gives 2 columns but I need to print the different I mean the gap not the start and the end. 

MaxID          MinID    
-----------------------
0000015010    0000015012
0000015018    0000015020
0000015020    0000015022
0000015041    0000015043
0000015062    0000015065
........      ........

and I want to print the numbers in between

  Gap
---------
0000015011
0000015019
0000015021
0000015042
0000015063
0000015064

how I can do that in SQL SERVER

Upvotes: 0

Views: 153

Answers (2)

Mat
Mat

Reputation: 313

I You want to return the gaps between values from columns MaxID and MinID, try this:

declare @res table (ID int, varID varchar(10))
declare @cr_MIN int
declare @cr_MAX int


declare cr cursor as
select cast(MinID as int) + 1
    ,cast(MaxID as int)
from ...

open cr
fetch next from cr
into @cr_MIN, @cr_MAX

while @@FETCH_STATUS <> 0
begin

    while @cr_MIN < @cr_MAX
    begin
        insert into @res (ID, varID)
        select @cr_MIN, STUFF(@cr_MIN,1,0,'00000')
        set @cr_MIN + 1
    end

    fetch next from cr
    into @cr_MIN, @cr_MA
end
close cr
deallocate cr

select * from @res

Upvotes: 0

gofr1
gofr1

Reputation: 15977

You can use CTE and convertion to int:

;WITH cte AS (
SELECT  MIN(CAST(MaxID as int)) as Mi,
        MAX(CAST(MinID as int)) as Ma
FROM YourResultSet c
UNION ALL
SELECT  Mi + 1,
        Ma
FROM cte
WHERE Mi < Ma
)

SELECT STUFF(c.Mi,1,0,'00000')
FROM cte c
INNER JOIN YourResultSet  y
    ON c.Mi > CAST(MaxID as int) and c.Mi < CAST(MinID as int)

Output:

0000015011
0000015019
0000015021
0000015042
0000015063
0000015064

Upvotes: 1

Related Questions