CODEWITHSUNDEEP
sqlsql-servert-sql
Sarthak Grover
Sarthak Grover

Reputation: 121

SQL finding Missing Record

I have a scenario where I have to find out missing record.

--Code for Creating Source Table
CREATE TABLE [dbo].[NaTarget](
    [BillKey] [int] NULL,
    [StartDate] [date] NULL,
    [EndDate] [date] NULL
) 
GO

--Code for Creating Target Table
CREATE TABLE [dbo].[NaSource](
    [BillKey] [int] NULL,
    [StartDate] [date] NULL,
    [EndDate] [date] NULL
) 
GO

--Inserting Records in Source
INSERT INTO [dbo].[NaSource]  ([BillKey],[StartDate],[EndDate])
     VALUES('1','2014-01-13','2014-03-27')
GO

INSERT INTO [dbo].[NaSource]([BillKey],[StartDate],[EndDate])
     VALUES('2','2014-02-14','2014-04-20')
GO

INSERT INTO [dbo].[NaSource]([BillKey],[StartDate],[EndDate])
     VALUES('3','2013-11-13','2014-01-18')
GO

--Inserting records In Target
INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('1','2014-01-13' , '2014-01-31' )        

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('1','2014-02-01' , '2014-02-28'     )     

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('1','2014-03-01' , '2014-03-27'     )     

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('2','2014-02-14' , '2014-02-28'     )     

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('2','2014-03-01' , '2014-03-31'     )     

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('2','2014-04-01' , '2014-04-20'     )     

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('3','2013-11-13' , '2013-11-30'     )    

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('3','2013-12-01' , '2013-12-31'     )    

INSERT INTO [dbo].[NaTarget] ([BillKey] ,[StartDate],[EndDate]) 
VALUES ('3','2014-01-01' , '2014-01-18'     )

Now for any BillKey, StartDate in target will be StartDate from Source and EndDate will be last date of month and now for same Billkey, next record will have 1st date of next month and EndDate will be last date, until last date of same BillKey is reached.

I have to find any record if it gets deleted.

Example if BillKey = 3 

StartDate= 2013-12-01   EndDate = 2013-12-31 is

not present in target we need to find it

Example will explain it better

Upvotes: 1

Views: 127

Answers (4)

SQLChao
SQLChao

Reputation: 7847

Here's my solution using recursive CTE. Build what the natarget table should look like and compare it to the actual natarget. I started getting confused on the dates piece so it may be simplified but this does work.

;with targetCte
as
(
 select  billkey, 
   startdate, 
   CAST(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, startdate) + 1, 0)) as DATE) as enddate
 from nasource
 union all
 select t.billkey, 
   cast(DATEADD(month, DATEDIFF(mm, 0, dateadd(mm, 1, t.startdate)), 0) as DATE) , 
   case
     when cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, DATEADD(month, DATEDIFF(mm, 0, dateadd(mm, 1, t.startdate)), 0)) + 1, 0)) as DATE) < n.enddate then cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, DATEADD(month, DATEDIFF(mm, 0, dateadd(mm, 1, t.startdate)), 0)) + 1, 0)) as DATE)
     else n.enddate
   end
      as enddate 
 from targetCte t
 join nasource n on n.billkey = t.billkey 
 where t.enddate < n.enddate
)


select * from targetcte t
where not exists
  (select * 
   from natarget nt 
   where t.billkey = nt.billkey
   and t.startdate = nt.startdate
   and t.enddate = nt.enddate)

Upvotes: 2

Siyual
Siyual

Reputation: 16917

Here is an attempt at this: If I understand your question correctly, you're looking to check to see if any expected values in the Target table based on the Start and End Dates in the Source table aren't actually there.

You'll need to essentially recreate the results table with what you are expecting from the NaSource table's StartDate and EndDate, and check that against the NaTarget table.

I'm positive there's a more efficient way of doing this (preferably without using cursors and while loops), but this should give you the results you're looking for:

Declare @Results Table
(
    BillKey Int,
    StartDate Date,
    EndDate Date
)

Declare @BillKey Int 
Declare @EndDate Date 
Declare @Cur Date 

Declare cur Cursor Fast_Forward For
Select  BillKey, StartDate, EndDate 
From    NaSource
Open    cur

While   1 = 1
Begin
    Fetch Next From cur Into @BillKey, @Cur, @EndDate
    If @@FETCH_STATUS <> 0 Break

    While (@Cur < @EndDate)
    Begin
        Insert  @Results
        Select  @BillKey, @Cur, 
            Case When DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Cur) + 1, 0)) > @EndDate 
            Then Convert(Date, @EndDate) 
            Else Convert(Date, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Cur) + 1, 0))) 
            End As EndDate

        Set @Cur = DATEADD(m, DATEDIFF(m, -1, @Cur), 0)
    End
End
Close cur
Deallocate cur

Select  R.* 
From    @Results R
Where Not Exists
(
    Select  1
    From    NaTarget T
    Where   R.BillKey = T.BillKey
    And     R.StartDate = T.StartDate
    And     R.EndDate = T.EndDate
)

Upvotes: 2

ArtK
ArtK

Reputation: 1185

It's filtering by reconciliation. LEFT Join both tables on StartDate AND EndDate pairs WHERE RIGHT keys are NULL. Google SQL Joins and you can find a very useful diagram on issues like that.

Upvotes: 0

Bob
Bob

Reputation: 153

Insert all records into one table with a unique ID (call this main table)

Take the table with deleted records then run a SELECT * on the Main table where ID NOT IN ID column of the deleted records table

Upvotes: 0

Related Questions