Comparing Date Ranges

[twilliamson2]

I am using CR X with an Oracle DB. I am trying to create a identifier for employees that had two or more positions during the same time period. In order to do this, I need to compare dates ranges within a set of records, which is the start date and end date.

For Example:

If Beth Jones worked as a Teacher with a start date of 1/1/2008 and an End date of 12/31/2008. In addition, Beth Jones worked a second job (part-time) as a Counselor with a start date of 2/1/2008 and an End Date of 7/31/2008. In this case, you can see that Beth worked 2 jobs during the same time. How do I create an identifier for these types of records?

Here are the sample records:

Name: Beth Jones

Job Start Date End Date

Teacher 1/1/2008 12/31/2008
Counselor 2/1/2008 7/31/2008

Please help!

 

[CR85user]

If you are grouping by Name and then start date (As shwon in your example) then you can just do a check to flag where the end date of the first job is greater than the start date of the next or the same thing with the start date in the range between previous start date to end date.

Something along the lines of:

//{@check2jobs}
if {end.date} in next({start.date}) to next({end.date}) then 'flag' else
if {start.date} in previous({start.date}) to previous({end.date}) then 'Flag' else
''

This is dependant upon both start and end dates being populated.

You could add to or alter the formula to display suitable messages as to which was the secondary role etc if required.

'J

 

[twilliamson2]

Thank you! I have approximately 100 records for Beth Jones. Is there a way to compare date ranges, not only for the NEXT and PREVIOUS records, but do a comparison for the all 100 records?