Help with time calculation

[priyanthan]

I need to create a report to find out the total down time for a Machine for a particular time frame


ErrorCode SetDateTime ClearDateTime

001 08/11/2010 11:23:00 08/11/2010 11:52:00
023 08/11/2010 12:59:00 08/11/2010 13:05:00
025 08/11/2010 12:58:00 08/11/2010 13:00:00

The issue here is that the total down time would be from 08/11/2010 11:23:00 to 08/11/2010 13:00:00, which is 97 mins. What ever the time in between
The start time and the end time need to omitted. (practically make sense.)
Can anyone help me how to calculate this.

Working DB2 and crystal 8.5.

 

[Turkbear]

Hi,
Actually, this:

What ever the time in between The start time and the end time need to omitted. (practically make sense.)

rerally did not make enough sense to help..

What times do you need to calculate? In other words, why is 97 mins not correct?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[priyanthan]

Thanks for concern,

what if meant was the down time starts with the errorcode 001 at 08/11/2010 11:23:00 and finishes at 08/11/2010 11:52:00 then another down time starts at 08/11/2010 12:59:00 with error code 023 and ends at 08/11/2010 13:05:00 and finally another down time starts with error code 025 at 08/11/2010 12:58:00 and ends in 08/11/2010 13:00:00. So the total down time started at 08/11/2010 11:23:00 and ended at 08/11/2010 13:05:00 which is 102 mins (my total time in my first post is incorrect)
The down time caused by error code 25 should be discarded in the calculation since the time period falls within the start downtime and end downtime ( 08/11/2010 11:23:00 - 08/11/2010 13:05:00)

hope this helps.

Thanks.

 

[lbass]

Do you need this per some group, e.g., on machine? Then create a formula like this:

datediff("n",minimum({table.setdatetime},{table.machine}),maximum({table.cleardatetime},{table.machine}))

-LB

 

[priyanthan]

Thanks LB,


i just realize the time between error 001 and 023 must subtracted since its not a downtime.
(Sorry i should have mention this before)
it has to be something like this
08/11/2010 11:52:00 - 08/11/2010 11:23:00 = 29
08/11/2010 13:05:00 - 08/11/2010 12:59:00 = 6

error 25 is excluded since its time falls within the time of other error codes,

Please help

 

[lbass]

Try a formula like this placed in the detail section:

whileprintingrecords;
numbervar mins;
if onfirstrecord or
{table.machine} <> previous({table.machine}) then
mins := mins + datediff("n",{table.setdatetime},{table.cleardatetime}) else
if {table.cleardatetime} > previous({table.cleardatetime}) then
mins := mins + datediff("n",previous({table.cleardatetime}),{table.cleardatetime})) else
mins := mins;
if {table.setdatetime} > previous({table.cleardatetime}) then
mins := mins + datediff("n", {table.setdatetime},previous({table.cleardatetime})) else
mins := mins;
mins

Add a reset formula in the machine group header:

whileprintingrecords;
numbervar mins;
if not inrepeatedgroupheader then
mins := 0;

If you need to show the results in the machine group footer, use the following to display the results:

whileprintingrecords;
numbervar mins;

-LB

 

[priyanthan]

thanks LB. I was stuck with another project all these days.
Coming back to this issue,

Your solution partially works. the issue is, its only comparing the previous records, not the other records on top. for example,

A. 10-06-2010 04:00:00AM 10-06-2010 04:30:00AM 30 Min
B. 10-07-2010 12:15:00AM 10-07-2010 12:45:00AM 30 Min
C. 10-06-2010 04:15:00AM 10-06-2010 05:45:00AM 75 Min
D. 10-07-2010 12:00:00AM 10-07-2010 01:00:00AM 15 Min

the total down time should be 150 Mins.
If you take the record C, it should be compared with A, but now we're only comparing with B. I thing we should set the records accordingly.

Hope you understand my issue.

Thank you very much for the Help.

 

[lbass]

You need to sort the records by setdatetime and then by cleardatetime.

-LB

 

[priyanthan]

Once again thanks LB.

I did sort by SetdateTime and the by ClearDateTime. But i have a scenario where this is blowing up.

Reporting Period
10/06/2010 4:00:00AM to 10/07/2010 3:59:59AM

A: 10/6/2010 10:15:00AM 10/6/2010 12:15:00PM
B: 10/6/2010 11:15:00AM 10/7/2010 2:00:00AM
C: 10/6/2010 12:30:00PM 10/6/2010 2:30:00PM
D: 10/6/2010 2:45:00PM 10/6/2010 4:45:00PM
E: 10/6/2010 5:00:00PM 10/6/2010 7:00:00PM
F: 10/6/2010 7:15:00PM 10/6/2010 9:15:00PM
G: 10/6/2010 9:30:00PM 10/6/2010 11:30:00PM
H: 10/7/2010 12:00:00AM 10/7/2010 4:00:00AM

In the above set everything after B should be 0 till G and add the H to the total,but i'm not getting that.
I'm getting 0 for C and then it compares with c onwards, and adding the time to the total.
I really need to get this done. Please let me know how can i do this.

Thanks a lot.

 

[lbass]

Replace the detail formula with this one:

whileprintingrecords;
numbervar mins;
datetimevar cldt;
datetimevar prevdt := cldt;
datetimevar cldt;
if {table.cleardatetime} > cldt then
cldt := {table.cleardatetime};
if onfirstrecord or
{table.machine} <> previous({table.machine}) then
mins := mins + datediff("n",table.setdatetime},{table.cleardatetime}) else
if {table.cleardatetime} >= cldt then
mins := mins + datediff("n",prevdt,cldt) else
mins := mins;
if {table.setdatetime} > prevdt then
mins := mins - datediff("n", prevdt,{table.setdatetime}) else
mins := mins;
mins;

Change the reset formula to:

whileprintingrecords;
numbervar mins;
datetimevar cldt;
if not inrepeatedgroupheader then (
mins := 0;
cldt:= {table.cleardatetime}
);

-LB

 

[priyanthan]

Thansk a lot LB. I really owe you a big thanks.

One more thing that i need to consider is that i need to only calculate the time between the reporting period. I think my sample data didn't cover this scenario.

A: 10/6/2010 12:15:00AM 10/6/2010 12:15:00PM
B: 10/6/2010 11:15:00AM 10/7/2010 2:00:00AM

Say if the reporting period is between 10/6/2010 04:00:00AM and 10/7/2010 03:59:59AM.

Here the Set time (A start before the reporting start time, therefor the time that we should consider would be 4AM - 12:15PM + the time for B

It also possible that the Clear time is NULL(Which means that the problem is continues). in this case the clear time would be the report end time.

Hope i've explained the requirements.

I really appreciate the help LB. I just cannot get this done.
Please help.

Many Thanks.