How to avoid summing simultaneous times

[mikkom]

I try to calculate downtime for machines. I know starting time and ending time for repairing works.

But problem is that there can be multiple repairing works same time for same machine. I should not sum simultaneous repairing times.

Is it possible to make a formula that sums repairing time only once if there is simultaneous repairing works for same machine?

 

[Madawc]

Group by machine, get minimum and maximum times for that machine, these are Summary totals.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[mikkom]

But if there are gaps between repairing periods and those gaps should reduce from calculation?

 

[IanWaterman]

YOu will need to use variables

Eg you have 3 repair times

12 pm - 2pm
1pm to 3pm
4pm-5pm

create variable formula

@Reset place in machine header
whileprintingrecords;

Datetimevar start:= starttimefield;
Datetimevar end:= endtimefield;
numbervar repair:=0
numbervar repaircnt:=1

@eval//place in detail

whileprintingrecords;

Datetimevar start;
Datetimevar end;
Datetimevar start2;
Datetimevar end2;
numbervar repair;
numbervar repaircnt;

// repair time in seconds change interval to suit
If repaircnt = 1 then repair:=datediff("s", start, end);

//This checks to make sure next start does not overlap //with previous end

If repaircnt <> 1 and starttime < end then start2:=end else
start2:=starttime;

//Makes sure repair time not inside previous repair
If repaircnt <> 1 and endtime > end then end2:=endtime
else end2 = end;

repair:=repair + datediff("s", start2, end2);

Start:= Start2;
end:= end2
repaircnt:= repaircnt+1

In machine footer

@repair

Whileprintingrecords;

numbervar repair;

you can convert seconds into hours and mins.

Ian

 

[mikkom]

Data in table is:

MACHINE START END
MACHINE 1 1.11.2010 10:00 1.11.2010 16:00
MACHINE 1 1.11.2010 11:00 2.11.2010 9:00
MACHINE 1 2.11.2010 10:00 2.11.2010 22:00
MACHINE 2 1.11.2010 9:00 1.11.2010 12:00
MACHINE 2 1.11.2010 11:00 2.11.2010 9:00
MACHINE 2 2.11.2010 10:00 2.11.2010 14:00

I have group by MACHINE and I have four formulas:

@reset (in Group header)
whileprintingrecords;

datetimevar start:={TABLE.START};
datetimevar end:={TABLE.END};
numbervar repair:=0;
numbervar repaircnt:=1;


@eval (in Details)
whileprintingrecords;

datetimevar start;
datetimevar end;
datetimevar start2;
datetimevar end2;
numbervar repair;
numbervar repaircnt;

if repaircnt = 1 then repair:=datediff("s", start, end);

if repaircnt <> 1 and {TABLE.START} < end then start2:=end else
start2:={TABLE.START};

if repaircnt <>1 and {TABLE.END} > end then end2:={TABLE.END}
else end2:=end;

repair:=repair + datediff("s", start2, end2);

start:=start2;
end:=end2;
repaircnt:=repaircnt + 1;


@repair (in Group footer)
whileprintingrecords;

numbervar repair;


@hours (in Group footer)
{@repair}/3600

Hours to MACHINE 1 should be 35, but in report it is 24. And to MACHINE 2 result should be 28, but it is 8 hours. What is wrong in my report?