Flagging records with overlapping dates

[cbushey1]

First off, let me say I am new to the community and QV in general. I went through the basic training and I have a background with similar applications and programming languages. Now with that said....

My Task is to flag potential overlapping dates based on their ID. Here are my fields:

ID

Start Date

End Date

In my original dataset, the ID field could be duplicated but would have different dates. Take the following example:

Record 1

Id = 1

Start Date = 11/1/2014

End Date     = 11/19/2014

Record 2

Id = 1

Start Date = 11/5/2014

End Date =  11/8/2014

I need to flag the dataset if my start date in record 2 or more is before End Date of record 1 AND ( equal to or later than Start Date of record 1) but only if the Id's are the same. So in the scenario above I would need to set a flag, but if recordset 2 had a different Id then Recordset 1 no flag would be set.

Any thoughts on how I might accomplish this?

Thanks!

[Gysbert_Wassenaar · Accepted solution]

Something like this:

Temp:

LOAD * FROM ...source...;

Result:

NOCONCATENATE

LOAD

     Id,

     [Start Date],

     [End Date],

     if( (Previous(Id)= Id and ([Start Date] >= previous([Start Date]) and [End Date] <= previous([End Date]) ) or

         (Previous(Id)= Id and [Start Date] <= previous([Start Date]) and [End Date] >= previous([End Date]) ) or

         (Previous(Id)= Id and [Start Date] <= previous([Start Date]) and [End Date] >= previous([Start Date]) ) or

         (Previous(Id)= Id and [Start Date] <= previous([End Date]) and [End Date] >= previous([End Date]) ) , 1, 0) as Flag

RESIDENT Temp

ORDER BY Id, [Start Date]

;

DROP TABLE Temp;

[cbushey1]

@Gysbert, Thanks!

I didnt know about the previous command. That is exactly what I was looking to do.