Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to avoid summing simultaneous times

Status
Not open for further replies.

mikkom

Technical User
Jul 23, 2010
29
FI
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?
 
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.


[yinyang] Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP [yinyang]
 
But if there are gaps between repairing periods and those gaps should reduce from calculation?
 
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




 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top