Skip to Content
0
Former Member
Apr 19, 2007 at 09:14 PM

Finding Duplicate / Overlapping DateTimes

59 Views

Post Author: Dana

CA Forum: Data Connectivity and SQL

I am pulling a report to try and find duplicate billing entries, or entries that overlap (double-billing).

I have one DateTime field with the start DateTime, and another field with the end DateTime.

I have created a comparon formula that will check and see if the current row's End field is between the previous field's start-end times, as well as the next field's start-end times.

This is great for a small set of information, but I am being asked to run this for all staff, which will be hundreds of pages of records. I would love for the report to only pull the rows that have something displayed in that comparison formula field.

Unfortunately, since the comparison field is evaluating while reading/printing records, I can't supress rows without it breaking the formula.

Does anyone have any suggestions on a better way to make this happen?

Here are the data elements available to me:

Start DateTimeEnd DateTimeDuration (diff between start and end time, in minutes)