5 Replies Latest reply: Jun 7, 2014 10:21 AM by EdStevens RSS

    How to find missing sequence no

    user4487805

      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
          Ramin Hashimzadeh

          Hi,

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

           

          ----

          Ramin Hashimzade

          • 2. Re: How to find missing sequence no
            Ramin Hashimzadeh

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

             

             

            ----

            Ramin Hashimzade

            • 3. Re: How to find missing sequence no
              Frank Kulash

              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

              ;

               

              I hope this answers your question.

              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
                Solomon Yakobson

                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
                  EdStevens

                  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?