Crystal Reports - overlapping datetimes 1

[CiviCest]

I would like to create formula, that finds overlapping times within one date interval for excample:
datetime_in - datetime out
11.07.2017 08:00 - 11.07.2017 09:00
11.07.2017 08:20 - 11.07.2017 10:00
11.07.2017 10:10 - 11.07.2017 14:20
11.07.2017 12:00 - 11.07.2017 14:25
12.07.2017 08:00 - 12.07.2017 09:00
12.07.2017 08:20 - 12.07.2017 08:40
12.07.2017 10:00 - 12.07.2017 14:30
12.07.2017 12:00 - 12.07.2017 14:45

Formula corrected datetime_in - corrected datetime_out
11.07.2017 08:00 - 11.07.2017 09:00
11.07.2017 09:00 - 11.07.2017 10:00
11.07.2017 10:10 - 11.07.2017 14:20
11.07.2017 14:20 - 11.07.2017 14:25
12.07.2017 08:00 - 12.07.2017 09:00
12.07.2017 09:00 - 12.07.2017 09:00
12.07.2017 10:00 - 12.07.2017 14:30
12.07.2017 14:30 - 12.07.2017 14:45

I have grouped data first by date(month) and second by employee id so ill get a report that displays record for each employee in separate page. Now i need total time that employee recorded within a day without any overlapping times.

 

[lbass]

Replace {table.datetime_in} with a formula {@datetimein}:

If onfirstrecord or
{table.employeeID} <> previous({table.employeeID}) then
{table.datetime_in} else
If {table.datetime_in}<previous({table.datetime_out}) then
Previous({table.datetime_out}) else
{table.datetime_in}

To tally the time per day, I would insert another group on datetime on change of day. Then create three formulas:

//{@reset} to be placed in the day group header:
Whileprintingrecords;
Numbervar daysum;
Daysum := 0

//{@accum} to be placed in the detail section:
Whileprintingrecords;
Numbervar daysum;
Daysum := daysum + datediff("n",{@datetime_in},{table.datetime_out});//n=minutes

//{@display} to be placed in the day group footer:
Whileprintingrecords;
Numbervar daysum;

This would give you the sum in minutes. You could then convert the sum to hours, if you wish by adding a final line to the display formula with:

Daysum/60

-LB

 

[CiviCest]

Thank you for the answer.
Ive tried those formulas, but theres 1 problem - this formula limits to only 1 row, if i have overlap in more than one row then it doesnt get changed:
datetime in - datetime out
12.07.2017 08:00 - 12.07.2017 14:00
12.07.2017 09:00 - 12.07.2017 10:00 - this will get changed
12.07.2017 11:00 - 12.07.2017 14:00 - this will not, because theres no overlap with this row datetimes vs. previous row datetimes

In excel i did an array formula that looked if emp_id=emp_id and date_in=date_in and then i looked for overlapping times for date range and if
overlapping occured("TRUE") then i found min(ie. start) and max(ie. end) and printed the results.
(well, sumproduct formula that i used isnt perfect because if Time_in was same time as Time_out, then it also marked that record "TRUE", i have to limit the formula +-1minute)
*attached sumproduct formula picture*

now back to this formula
Lets say well have about 20 overlapping datetimes in a day then i have to create 20 different datetimes_in's and datetimes_out's to find all overlaps,
but is it possible to not only look "previous" but to look all datetimes withing a range (day)?

 

[lbass]

Okay, I see the issue: Try this to replace the datetime_in:

//{@correcteddatein};
whileprintingrecords;
datetimevar dtin;
datetimevar dtout;

If onfirstrecord or
{table.Employee_ID} <> previous({table.Employee_ID}) or
date({table.datetime_in})<>date(previous({table.datetime_in})) then (
dtin := {table.datetime_in};
dtout := {table.datetime_out}
) else
If {table.datetime_in} < dtout then (
dtin := dtout;
if {table.datetime_out}>=dtout then
dtout := {table.datetime_out} else
dtout := dtout
) else
(
dtin :={table.datetime_in};
dtout := {table.datetime_out}
);
dtin

If you want to display a corrected datetime_out, then use a formula like to replace the out field.

evaluateafter({@correcteddatein});
datetimevar dtout;

Change {@accum} to:
Whileprintingrecords;
Numbervar daysum;
datetimevar dtin;
datetimevar dtout;

Daysum := daysum + datediff("n",dtin,dtout);//n=minutes

-LB

 

[CiviCest]

Thank you lbass, you are a genius!
Your formula is perfect! Thank you again!