CODEWITHSUNDEEP
sql
Sarthak Grover
Sarthak Grover

Reputation: 121

SQL to Return missing Row

I have one Scenario where I need to find missing records in Table using SQL - without using Cursor, Views, SP.

Following population of data will explain it better. 

CustID  Start_Date  End_Date
1   19000101    20121231
1   20130101    20130831
1   20130901    20140321
1   20140321    99991231

Basically I am trying to populate data like in SCD2 scenario.

Now I want to find missing record (or CustID).

Like below we don’t have record with CustID = 4 with Start_Date = 20120606 and End_Date = 20140101

CustID  Start_Date  End_Date
4   19000101    20120605
4   20140102    99991231

Code for Creating Table

CREATE TABLE TestTable
(
  CustID int,
  Start_Date int,
  End_Date int
)

INSERT INTO TestTable values (1,19000101,20121231)
INSERT INTO TestTable values (1,20130101,20130831)
INSERT INTO TestTable values (1,20130901,20140321)
INSERT INTO TestTable values (1,20140321,99991231)

INSERT INTO TestTable values (2,19000101,99991213)

INSERT INTO TestTable values (3,19000101,20140202)
INSERT INTO TestTable values (3,20140203,99991231)
INSERT INTO TestTable values (4,19000101,20120605)
--INSERT INTO TestTable values (4,20120606,20140101)   --Missing Value
INSERT INTO TestTable values (4,20140102,99991231)

Now SQL should return CustID = 4 as its has missing Value.

Upvotes: 0

Views: 121

Answers (3)

Jason Goemaat
Jason Goemaat

Reputation: 29194

You need rows if the one of the following conditions is met:

  1. Not a final row (99991231) and no matching next row
  2. Not a start row (19000101) and no matching previous row

You can left join to the same table to find previous and next rows and filter the results where you don't find a row by checking the column values for null:

SELECT t1.CustID, t1.StartDate, t1.EndDate
FROM TestTable t1
LEFT JOIN TestTable tPrevious on tPrevious.CustID = t1.CustID
    and tPrevious.EndDate = t1.StartDate - 1
LEFT JOIN TestTable tNext on tNext.CustID = t1.CustID
    and tNext.StartDate = t1.EndDate + 1
WHERE (t1.EndDate <> 99991231 and tNext.CustID is null) -- no following
    or (t1.StartDate <> 19000101 and tPrevious.CustID is null) -- no previous

Upvotes: 0

Izaaz Yunus
Izaaz Yunus

Reputation: 2828

My idea is based on this logic. Lets assume 19000101 as 1 and 99991231 as 10. Now for all IDs, if you subtract the End_date - start_date and add them up, the total sum must be equal to 9 (10 - 1). You can do the same here

SELECT ID, SUM(END_DATE - START_DATE) as total from TABLE group by ID where total < (MAX_END_DATE - MIN_START_DATE)

You might want to find the command in your SQL that gives the number of days between 2 days and use that in the SUM part.

Lets take the following example

1 1900 2003
1 2003 9999
2 1900 2222
2 2222 9977
3 1900 9999

The query will be executed as follows

1 (2003 - 1900) + (9999 - 2003) = 1 8098
2 (2222 - 1900) + (9977 - 2222) = 2 9077
3 (9999 - 1900) = 3 8098

The where clause will eliminate 1 and 3 giving you only 2, which is what you want.

Upvotes: 2

T I
T I

Reputation: 9933

If you just need the CustID then this will do

SELECT t1.CustID
FROM TestTable t1
LEFT JOIN TestTable t2
ON DATEADD(D, 1, t1.Start_Date) = t2.Start_Date
WHERE t2.CustID IS NULL
GROUP BY t1.CustID

Upvotes: 0

Related Questions