I have a table date, store code, do_no. some stores have missing sequence number. Data looks like Source. I'm not able to find missing doc_no in all stores. Each store doc_no start digit is different. STO1 starts wth 1, ST02 start with 2,ST03 3 etc..
I'm expecting results to find which store have missing doc_no. example expected results. Expected Results
One option is to create list of all doc_no values (span from min to max value per store_code) and then use the minus set operator to extract missing values.
Sample data:
SQL> with test (store_code, doc_no) as
2 (select 'ST03', 32000077 from dual union all
3 select 'ST03', 32000079 from dual union all
4 select 'ST03', 32000080 from dual union all
5 --
6 select 'ST02', 22000130 from dual union all
7 select 'ST02', 22000131 from dual union all
8 select 'ST02', 22000133 from dual
9 ),
Query begins here:
10 temp as
11 (select store_code, min(doc_no) min_no, max(doc_no) max_no
12 from test
13 group by store_code
14 ),
15 all_docs as
16 (select store_code,
17 min_no + column_value - 1 as doc_no
18 from temp cross join
19 table(cast(multiset(select level from dual
20 connect by level <= max_no - min_no + 1
21 ) as sys.odcinumberlist))
22 )
23 select store_code, doc_no from all_docs
24 minus
25 select store_code, doc_no from test;
STORE_CODE DOC_NO
------------ ----------
ST02 22000132
ST03 32000078
SQL>