Compare date of current row to previous row

Database
Enthusiast

Compare date of current row to previous row

Hi,

 

I have table with SCD logic. So for example i have the table with the following columns and data as below:

 

Party_Id    Party_Status     Start_Date    End_Date

1                Y                       2016-09-01   2016-09-27

1                N                       2016-09-27   9999-12-31

 

Hence this table shows history data being maintainned here.

 

My objective is if i have such data in a table then i want to write a query which checks whether Start_date of the current row = Previous row End_date and if not then how many rows are like that which violates this rule?

 

Can anyone help with the query

 

Thanks


Accepted Solutions
Senior Apprentice

Re: Compare date of current row to previous row

You can use Analytical Functions to compare the previous row's value:

select Party_Id, Start_Date, End_Date,
   max(End_Date)
   over (partition by Party_Id
         order by Start_Date
         rows between 1 preceding and 1 preceding)  as prev_End
from tab qualify Start_Date <> prev_end

 

1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: Compare date of current row to previous row

You can use Analytical Functions to compare the previous row's value:

select Party_Id, Start_Date, End_Date,
   max(End_Date)
   over (partition by Party_Id
         order by Start_Date
         rows between 1 preceding and 1 preceding)  as prev_End
from tab qualify Start_Date <> prev_end

 

Enthusiast

Re: Compare date of current row to previous row

Thanks a lot Deiter :) This perfectly works.

 

I am actually trying to design test cases to test History Loading of data into Tables which is either by DELTA or FULL DUMP load.

 

This is one of the test cases which i wanted to write. Is there anything else you can suggest?

 

Thanks

Lovey