[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • 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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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