5 Replies Latest reply on Jun 7, 2014 3:21 PM by EdStevens

How to find missing sequence no

I would to get missing sequence no from set of data using sql.

Here's my scenario --

Table Name: Policy_Location

Policy_noLocation_no
XX1011
XX1012
XX1021
XX1023
XX1024
XX1101
XX1102
XX1103
XX1105
XX2111
XX2112
XX2113
XX2114
XX2116

Each policy no has multiple location numbers. Some of the policies have missing sequence no. I need to find out the policies that has missing sequence.

As per the above table, the following policies have missing sequence -

XX102 has missing location no of 2

XX110 has missing location no of 4

XX211 has missing location no of 5

XX101 is correct as location no has 1 &2, no missing no.

I have over 10000 records with this scenario and I would like to get only the policy numbers that has missing sequence no.

How would I achieve this using sql?

• 1. Re: How to find missing sequence no

Hi,

Explain plz if there is XX212 with location 3. Is it meaning that XX212 missing 1,2 locations?

----

• 2. Re: How to find missing sequence no

--example data----

WITH T (police,seq) AS

(

SELECT 'XX101',1 FROM DUAL UNION ALL

SELECT 'XX101',2 FROM DUAL UNION ALL

SELECT 'XX102',1 FROM DUAL UNION ALL

SELECT 'XX102',3 FROM DUAL UNION ALL

SELECT 'XX102',4 FROM DUAL UNION ALL

SELECT 'XX110',1 FROM DUAL UNION ALL

SELECT 'XX110',2 FROM DUAL UNION ALL

SELECT 'XX110',3 FROM DUAL UNION ALL

SELECT 'XX110',5 FROM DUAL UNION ALL

SELECT 'XX211',1 FROM DUAL UNION ALL

SELECT 'XX211',2 FROM DUAL UNION ALL

SELECT 'XX211',3 FROM DUAL UNION ALL

SELECT 'XX211',4 FROM DUAL UNION ALL

SELECT 'XX212',4 FROM DUAL UNION ALL --pay attention

SELECT 'XX211',6 FROM DUAL

)

---main query-

SELECT q.police,

q.newb missing_sequence

from  t

RIGHT JOIN (select police, column_value newB

from (SELECT police, max(seq) mx FROM T group by police),

table (select collect(rownum)

from dual

connect by level <= mx)

order by 1, 2) q

on q.police = t.police and q.newb = t.seq

where t.seq is null

POLICE              MISSING_SEQUENCE

XX102                   2

XX110                   4

XX211                   5

XX212                   1

XX212                   2

XX212                   3

----

• 3. Re: How to find missing sequence no

Hi,

Here's one way:

SELECT    policy_no

FROM      policy_location

GROUP BY  policy_no

HAVING    MAX (location_no) - MIN (location_no)  >

COUNT (DISTINCT location_no) - 1

;

If not, point out where out where it is getting the wrong results (either with Ramin's sample data, or some usable sample data that you post).  Post the results you want, and explain how you get the right results in the places where the query above is wrong.

If you change the code at all, post your code.

• 4. Re: How to find missing sequence no

WITH t AS (
SELECT 'XX101' policy,1 location_no FROM DUAL UNION ALL
SELECT 'XX101',2 FROM DUAL UNION ALL
SELECT 'XX102',1 FROM DUAL UNION ALL
SELECT 'XX102',3 FROM DUAL UNION ALL
SELECT 'XX102',4 FROM DUAL UNION ALL
SELECT 'XX110',1 FROM DUAL UNION ALL
SELECT 'XX110',2 FROM DUAL UNION ALL
SELECT 'XX110',3 FROM DUAL UNION ALL
SELECT 'XX110',5 FROM DUAL UNION ALL
SELECT 'XX211',1 FROM DUAL UNION ALL
SELECT 'XX211',2 FROM DUAL UNION ALL
SELECT 'XX211',3 FROM DUAL UNION ALL
SELECT 'XX211',4 FROM DUAL UNION ALL
SELECT 'XX211',6 FROM DUAL
)
SELECT  policy,
'[' || (prev_location_no + 1) || ' - ' || (location_no - 1) || ']' gap
FROM  (
SELECT  policy,
location_no,
lag(location_no,1,0) over(partition by policy order by location_no) prev_location_no
FROM  t
)
where location_no != prev_location_no + 1
/

POLICY GAP
------ --------
XX102  [2 - 2]
XX110  [4 - 4]
XX211  [5 - 5]

SQL>

SY.

• 5. Re: How to find missing sequence no

From the application/business point of view, what is the significance of a 'missing' location?

Suppose you have

Policy   location

x101     1

x101     2

x102     1

x103     1

Is it possible there is a missing location number 2 for policy x102?