Thanks for your interes Prabhu, and sorry if my example is not clear (it's hard for me to explain this in English).
As you can see in the first table, records are ordered by ID and StartDate but there are records with StartDate greater than the previous StartDate but with EndDate prior to the previous record's EndDate. These should be added to counter (NofRecords) and delete from the output table.
The records are periods of service and I need to summarize services given without a time gap between them in a single record.
I can't explain it better... Hope you understand now.
Thanks Martin and anbu,
This helps, but it's not exactly what I need cause there are more than one record to remove following a given valid record and the proposed 'WHERE' clause only removes the first one. I'm using your solution combined with a loop, but as my file has 1,2 million records, it takes very long to complete the proccess (>600 loops an 30 mins ).
Hope someone could give me a hint to solve it
Load ID,StartDate,EndDate,RecNo() as RecNo where ID <> Peek(ID) Or (ID = Peek(ID) And EndDate > Peek(EndDate));
Load ID,Date#(StartDate,'D/M/YYYY') As StartDate,Date#(EndDate,'D/M/YYYY') As EndDate Inline [
3,20/06/2013,20/06/2013 //Add an extra row
Load * Where ID <> 3; //Remove extra row
Load ID,StartDate,EndDate,If(IsNull(Previous(RecNo)),RecNo,Previous(RecNo) - RecNo ) Resident Tab Order by RecNo desc;
Drop table Tab;