Formula to determine overlapping time blocks

[SuperTime]

Crystal 9 backend Sql Server 05

The details section of my report shows the following time blocks.And there is a group on Team.

Team 1
07:00 AM-11:00 AM
11:00 AM- 2:00 PM
02:00 PM- 5:00 PM

Team 2
07:00 AM- 5:00 PM
03:30 PM- 4:30 PM

Team 3
07:00 AM-11:30 AM
07:00 AM- 6:00 PM
07:00 AM- 2:30 PM


I want a formula that will determine the overlapping time blocks within a team group so that I can select only the largest of the overlapping time blocks.

So Team1 there is no overlapping time block so I want to see all of them.
For Team2 I want to see only 07:00 AM- 5:00 PM time block because 03:30 PM- 4:30 PM is overlapping.
For Team3 I want to see only 07:00 AM- 6:00 PM cause the other two are overlapping.

Please Advice.

 

[SuperTime]

I could also have something like this, the time blocks are not always sequential.

Team 4
07:00 AM- 2:30 PM
02:30 AM- 6:00 PM
11:00 AM- 2:30 PM
04:00 AM- 6:00 PM

In this case I would see
07:00 AM- 2:30 PM because this is the largest of the overlapping block 11:00 AM- 2:30 PM
AND
02:30 AM- 6:00 PM beacuse this is the largest of the overlapping block 04:00 AM- 6:00 PM

 

[lbass]

Are the start and end times two separate fields? If so, please show their names. Or is the range the result of a formula? If so, please show the content. What are we seeing here?

-LB

 

[SuperTime]

Yes,I am sorry. The range is a result of a complicated formula which based on all the scheduled job finds the open time slots for the team, but following the part of the formula which gives the daterange display.

ToText(Time({WorkOrders.StartDateTime}), "hh:mm tt")& " - " & ToText(Time ({WorkOrders.EndDateTime}), "hh:mm tt")


So yes the start and end times are two seperate fields.

 

[lbass]

You need to clarify your criteria. It appears that you want to suppress those ranges that are included within a larger range, but your example for Team 4 seems to indicate that you are also requiring that either the start or ending time match?

Please also clarify whether you can sort the records so that the time ranges are sequential.

-LB

 

[SuperTime]

Thank you lbass, for helping me with this, really appreciate it.

As you see in my example for Team2 , the starttime and endtime of the overlapping blocks may not match.

I have a report which shows the available time slots for a certain date for the teams. These time blocks that you see in the examples are the jobs that are scheduled for a day. I am taking these work time blocks to determine the openings for the day. So in a simple scenarios where there are no overlapping jobs scheduled for eg. 7:00 AM- 11:00 AM and 2:00 PM-6:00 PM. My report takes the time difference between the two jobs and shows it, the job blocks are acutally suppressed and not shown. So yes I can change the sorting of the report if needed.

Please advice.

 

[lbass]

Your response does not address why you would treat Team4 results differently. All ranges nest within 4AM to 6PM, and yet you say you want to show two sets of ranges. Why?

-LB

 

[SuperTime]

Oh no... I am so sorry.....I mistyped the example of Team4. It's wrong, the start time for some of them are pm not am. My range will always include times between 7AM to 6PM that is the company time.

Here is the correct version of the example:

Team 4
07:00 AM- 2:30 PM
02:30 PM- 6:00 PM
11:00 AM- 2:30 PM
04:00 PM- 6:00 PM

And hence 07:00 AM-2:30 PM is the largest of the overlapping block 11:00 AM-2:30 PM
AND
02:30 PM-6:00 PM will be shown beacuse this is the largest of the overlapping block 04:00 PM- 6:00 PM

I apologize the confusion it may have caused, because of the incorrect example.

Hope this here makes sense. Please let me know if you need any further clarification.

Thanks.

 

[lbass]

Sample:

Team 1
07:00 AM-11:00 AM
11:00 AM- 2:00 PM
02:00 PM- 5:00 PM

Team 2
07:00 AM- 5:00 PM
03:30 PM- 4:30 PM
04:00 PM- 5:00 PM

Team 3
07:00 AM-11:30 AM
07:00 AM- 2:30 PM
07:00 AM- 6:00 PM

Team 4
07:00 AM- 2:30 PM
11:00 AM- 2:30 PM
02:30 PM- 6:00 PM
04:00 PM- 6:00 PM

First add a sort ascending on:

{WorkOrders.StartDateTime}

...and a second sort ascending on:

{WorkOrders.EndDateTime}

Try a detail section suppression formula like this (do not add a checkmark, just use the formula area):

whileprintingrecords;
datetimevar start;
datetimevar end;
if {table.team} = next({table.team}) and
(
Time({WorkOrders.StartDateTime}) <= time(next( {WorkOrders.StartDateTime})) and
Time({WorkOrders.EndDateTime}) >= time(next( {WorkOrders.EndDateTime}))
) then (
start := {WorkOrders.StartDateTime};
end := {WorkOrders.EndDateTime}
);
{WorkOrders.StartDateTime} >= start and
{WorkOrders.EndDateTime} <= end;

Add a reset formula in the Team group header:

whileprintingrecords;
datetimevar start;
datetimevar end;
if not inrepeatedgroupheader then(
start := date(0,0,0,0,0,0);
end := date(0,0,0,0,0,0)
);

-LB

 

[SuperTime]

I did that but as a result:
Team1 it shows me all blocks which is correct
Team2 it suppressed all the time block but I was expecting to see 07:00 AM- 5:00 PM because this is the largest of the overlapping blocks and hence should not be suppressed.

 

[lbass]

Maybe try:

whileprintingrecords;
datetimevar start;
datetimevar end;
if {table.team} = next({table.team}) and
(
Time({WorkOrders.StartDateTime}) <= time(next( {WorkOrders.StartDateTime})) and
Time({WorkOrders.EndDateTime}) >= time(next( {WorkOrders.EndDateTime}))
) then (
start := {WorkOrders.StartDateTime};
end := {WorkOrders.EndDateTime}
);
if
not(
{WorkOrders.StartDateTime} = start and
{WorkOrders.EndDateTime} = end
) then
{WorkOrders.StartDateTime} >= start and
{WorkOrders.EndDateTime} <= end;

-LB