Find overlapping time periods between records?

[sagobogger]

Could I get some suggestions how to do this?

Suppose I have a set of data that looks like

Employee Job     Start Date/Time    End Date/Time

Joe      Thingy  2005/06/01 13:00   2005/06/01 14:00
Joe      Widget  2005/06/01 15:00   2005/06/01 16:30
Joe      Stuff   2005/06/01 16:00   2005/06/01 17:00

and I want to report records where the time period overlaps the time period in another record, in this case that would be the Widget and Stuff ones.

I have done something similar before by sorting and converting the records to a format like

Employee Job1 Start1 End1 Job2 Start2 End2 etc.

and then looking for Start(x) < end (x-1)

but I'm thinking there is probably a better way.

 

[craigiep]

If the data is sorted in Employee / Start Date order you could try something like this

DEFINE FILE X
OVERLAP/A1 = IF EMPLOYEE EQ LAST EMPLOYEE AND START_DATE LT LAST END_DATE THEN 'Y' ELSE 'N';
END

TABLE FILE X
PRINT xxx OVERLAP
END