Display Overlapping Date/Time in Details section CR 11

[thementornetwork]

Trying desperately to figure out how I can retrieve duplicate or overlapping service entries only.

ie:

01/06/2007 10:00am 10:15am BALL, MANDI
01/06/2007 1:20pm 1:40pm BALL, MANDI
01/06/2007 1:20pm 1:40pm BALL, MANDI
01/06/2007 1:30pm 2:30pm BALL, MANDI
01/06/2007 4:00pm 5:00pm BALL, MANDI

To retrieve days with multiple services, I added this to the Report Selection Formulas > Group:

COUNT({billing_tx_charge_detail.date_of_service},{billing_tx_charge_detail.date_of_service}) > 1

Now comes the tricky part. How can I suppress all the records that ARE NOT overlapping or duplicated and display only the ones that are "bad"?

That means, I shouldn't be seeing the first nor the last entry in the example above because they are not overlapping with anything else and are not duplicated services:
01/06/2007 10:00am 10:15am BALL, MANDI
01/06/2007 4:00pm 5:00pm BALL, MANDI


Here's the code I wrote in the Suppress option of Details:

IF (({billing_tx_history.end_time} IN Previous({billing_tx_history.start_time}) to Previous({billing_tx_history.end_time}))
OR
({billing_tx_history.start_time} IN Next({billing_tx_history.start_time}) to Next({billing_tx_history.end_time})))THEN
FALSE
ELSE
TRUE

It still brings me everything... =(. Please help me if you have written a similar report before. Thanks.

 

[CR85user]

If they are always sequential based on start time maybe try a suppression formula like:

IF
((previous({billing_tx_history.end_time}) >{billing_tx_history.start_time}) or ({billing_tx_history.end_time} < next({billing_tx_history.start_time}))) then 1 else 0

'J

 

[thementornetwork]

Hi CR95user, thanks for the suggestion but your solution will not work.

The 1st entry without any time conflict remains on the report:
01/06/2007 10:00am 10:15am BALL, MANDI

And the 2nd entry end up being dropped. Only one of the 2 1:20pm entries remains:
01/06/2007 1:20pm 1:40pm BALL, MANDI

 

[CR85user]

Ok - The above suppression would remove duplicates rather than display and requires a slight adjustment. Simply prefix with a not() or reverse the true/false to reverse the functionality as follows as well as makign the change highlighted in bold.

IF
((previous({billing_tx_history.end_time}) >{billing_tx_history.start_time}) or ({billing_tx_history.end_time} > next({billing_tx_history.start_time}))) then 0 else 1

That should leave you with only the required duplicates.

'J