How to access Previous Records' Data from within a Query?
440740Apr 7 2005 — edited Apr 7 2005Hi All,
I am making a report in which I need to compare my "Registration Start Date" with "Registration End Date" of previous registration of same customer. This is to check if there are gaps between any customer's registrations.
In scenario shown below, I want to filter out RegID '1' as it comes after a gap from previous registration(RegID '3') of the same customer.
RegID CustID Registration Start Registration End
1 1 01/04/2005 07/04/2005
2 2 01/03/2005 29/05/2005
3 1 21/03/2005 29/03/2005
4 1 01/03/2005 21/04/2005
I created the join of this table with itself to pick previous records. it is something like:
Current_Rec.Cust ID = Prev_Rec.CustID AND
Current_Rec.RegStart > Prev_Rec.RegEnd
But this gives wrong data if customer has 3 back-to-back registrations. i.e. Shows last registration in query results as their is a gap between 3rd and 1st registration. But ofcourse there was another record between these two(which was 2nd registration).
It would be so nice of you guys if you can suggest a solution to this. Any oracle function?
Thanks & Regards,
Ahsan