Identify Records with Overlapping Times 1

[jemsmom]

I’m using Crystal XI with Oracle 10g.
The goal of this report is to identify case records that have overlapping times and I’ve been unable to get anything to work.

My data looks like this:
Log OR IN OR Out
------------------------------------------------------------
Log 1 12/21/2009 7:13:00 AM 12/21/2009 1:30:00 PM
Log 2 12/21/2009 10:55:00 AM 12/21/2009 6:03:00 PM
Log 3 12/21/2009 2:24:00 PM 12/21/2009 8:43:00 PM

Log 2 is the record I want to display.

I am using two copies of one table {OR_LOG_CASE_TIMES) aliased as {OR_LOG_CASE_TIMES_in} and {OR_LOG_CASE_TIMES_out} to get the in and out times on one line for each record. In the selection criteria I have specified the {OR_LOG_CASE_TIMES_in.EVENT_TYPE} = OR_IN and {OR_LOG_CASE_TIMES_out.EVENT_TYPE} = OR_OUT.

I’ve grouped by Date and OR Room, and sorted the records by {OR_LOG_CASE_TIMES_in.TRACKING_TIME_IN}.

Any and all suggestions will be appreciated?

Thank you.

 

[lbass]

You could highlight records by going into the section expert->detail->color tab->x+2 and entering something like this:

if
(
(
{table.date} = previous({table.date}) and
{table.ORrm} = previous({table.ORrm}) and
{@timein} <= previous({@timeout})
) or
(
{table.date} = next({table.date}) and
{table.ORrm} = next({table.ORrm}) and
{@timeout} >= next({@timein})
)
) then
cryellow else
crnocolor

-LB

 

[jemsmom]

This worked perfect. Thanks lbass for your quick response.