sql - Find the missing number group by category -

i want find missing batchno group each category try , work missing number categories how group data ?

 create table #tmp  (batchno int, category varchar(15))     insert #tmp     select 94, 'a01'     union     select 97, 'a01'     union     select 100, 'a02'     union     select 105, 'a02' 

---

  declare @valmax int, @valmin int, @i int;    select @valmax=max(batchno) #tmp;   select @valmin=min(batchno) #tmp;     set @i=@valmin;   while (@i<@valmax) begin     if (not exists(select * #tmp batchno=@i)) begin        --   select @i, category #tmp group category          select @i      end;     set @i=@i+1   end; 

the out put shold

95      a01 96      a01  101     a02 102     a02 103     a02 104     a02 

you can joining number table. query uses thespt_valuestable , should work:

;with cte (     select category , min(batchno) min_batch, max(batchno) max_batch     #tmp     group category ) select number, category master..spt_values cross join cte type = 'p'   , number > min_batch   , number < max_batch group category, number 

sample sql fiddle

note table has sequence of numbers 0-2047so if yourbatchnocan higher need source query (could table or recursive cte); work:

;with      cte (category, min_batch, max_batch) (        select category , min(batchno), max(batchno)        #tmp        group category     ),      numbers (number, max_number) (        select 1 number, (select max(batchno) #tmp) max_number        union        select number + 1, max_number        numbers        number < max_number     )  select number, category numbers cross join cte number > min_batch   , number < max_batch group category, number option (maxrecursion 0)