How to extract missing records

Posted on 2012-09-07
Last Modified: 2012-10-11
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

   Help Appreciated.

Question by:amankhan2005
    LVL 73

    Assisted Solution

    try this...
    LVL 73

    Accepted Solution

    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

    6 - 27

    SELECT missing_ids
                       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)
              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

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now