CODEWITHSUNDEEP
sqlsql-server-2008sequence
Lev
Lev

Reputation: 65

SQL Server 2008 -Missing number in sequence

I saw related questions but I really don't understand how to do this.

I have a table name "tbl_image"

id       keys       image_count   
1        0001            1
2        0001            3
3        0001            5
4        0003            6
5        0003            9

I want my output to look like this If I select where the keys = '0001'

output
2
4

And when I select where the keys = '0003'

output
7
8

Thanks in advance.

Upvotes: 1

Views: 86

Answers (3)

DineshDB
DineshDB

Reputation: 6193

Try this:

DECLARE @tbl_image TABLE(ID int, Keys VARCHAR(10),Image_Count INT)
INSERT INTO @tbl_image VALUES (1,'0001',1)
INSERT INTO @tbl_image VALUES (2,'0001',3)
INSERT INTO @tbl_image VALUES (3,'0001',5)
INSERT INTO @tbl_image VALUES (4,'0003',6)
INSERT INTO @tbl_image VALUES (5,'0003',9)

SELECT DISTINCT n = number 
FROM Master.dbo.[spt_values] 
WHERE number BETWEEN (SELECT MIN(Image_Count) FROM @tbl_image WHERE Keys='0001') 
    AND (SELECT MAX(Image_Count) FROM @tbl_image WHERE Keys='0001')
    AND number NOT IN(SELECT Image_Count FROM @tbl_image WHERE Keys='0001')

OutPut:

2
4

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

One method is to use recursive cte 

;with cte as
(
    select id, image_count,min(image_count) over (partition by Keys) cm, keys from table
    union all
    select id, image_count, cm+1, keys from cte c
    where c.image_count > c.cm
)
select distinct c.cm as Missing from cte c
left join table t on t.keys = c.keys and t.image_count = c.cm
where c.keys = '0003' and t.image_count is null

Result :

Missing
7
8

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

Use recursive query

with result as
(
   select min(image_count) + 1 output, 
          (select max(image_count) - 1 from tbl_image where keys = '0003') max_output
   from tbl_image 
   where keys = '0003'
   union all
   select output + 1, max_output
   from result
   where output < max_output
)
select output 
from result
where not exists (select 1 from tbl_image where image_count = output)

dbfiddle demo

Upvotes: 0

Related Questions