sql - Find missing sequences by category -


i have identify missing records example below.

category    batchno transactionno +++++++++++++++++++++++++++++++++ cat1           1    1 cat1           1    2 cat1           2    3 cat1           2    4 cat1           2    5 cat1           3    6 cat1           3    7 cat1           3    8 cat1           5    12 cat1           5    13 cat1           5    14 cat1           5    15 cat1           7    18 cat2           1    1 cat2           1    2 cat2           3    6 cat2           3    7 cat2           3    8 cat2           3    9 cat2           4    10 cat2           4    11 cat2           4    12 cat2           6    14 

i need script identify missing records below

category    batchno +++++++++++++++++++ cat1         4 cat1         6 cat2         2 cat2         5 

i not need know cat1 8 , cat2 7 not there potentially have not been inserted yet.

you can create temporary result set possible batch no max batch number each category select batch no not available.

create table temp(         category varchar(10),         batchno int,         transactionno int     )     insert temp values     ('cat1', 1, 1),     ('cat1', 1, 2),     ('cat1', 2, 3),     ('cat1', 2, 4),     ('cat1', 2, 5),     ('cat1', 3, 6),     ('cat1', 3, 7),     ('cat1', 3, 8),     ('cat1', 5, 9),     ('cat1', 7, 10),     ('cat2', 1, 1),     ('cat2', 1, 2),     ('cat2', 3, 3),     ('cat2', 4, 4),     ('cat2', 4, 5),     ('cat2', 4, 6),     ('cat2', 6, 7);       batchno (batchid,category,maxbatch) (       select 1, category, max(batchno) maxbatch  temp group category       union       select batchid + 1, category, maxbatch batchno       batchid < maxbatch     )      select          batchno.category,         batchno.batchid              batchno              batchid not in (select batchno temp category = batchno.category)     order         batchno.category,         batchno.batchid       drop table temp 

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -