• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

How to extract missing records

Hi All,

   I have a small query regarding how to extract missing records using sql query...

   For Ex:

      Cust ID     Cust Name
        1              XXXX
        2              YYYY
        4              ZZZZ
        5              AAAA
        7              BBBB

    So, here the sequence 3 and 6 are missing... Is there any way to find that this sequence numbers are missing from the above result set...

    Missing Cust ID
   --------------------------
        3
        6

   Help Appreciated.

Thanks
0
amankhan2005
Asked:
amankhan2005
  • 2
2 Solutions
 
sdstuberCommented:
try this...
ee.txt
0
 
sdstuberCommented:
another option,  rather than listing them individually, this will list ranges of missing ids as

x - y

so, if your sample data had the last cust_id as 28 rather than 7  it would report results as

3
6 - 27

SELECT missing_ids
  FROM (SELECT CASE
                   WHEN cust_id = prev_id + 2 THEN TO_CHAR(prev_id + 1)
                   WHEN cust_id > prev_id + 2 THEN (prev_id + 1) || ' - ' || (cust_id - 1)
               END
                   missing_ids
          FROM (SELECT cust_id, LAG(cust_id) OVER (ORDER BY cust_id) prev_id FROM yourtable))
 WHERE missing_ids IS NOT NULL;


note, unlike the first query, this one does assume you're only interested in looking at data between the minimum existing id and the maximum.  The first query looks from id 1 to the maximum
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now