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.
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
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)
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
