Time diff from one field

[gusc999]

I need to get the time difference from the "TranTime" field for each record in sequnce order; record 1 time differnce with record 2 then record 2 the time difference with record 3 so on and so on for all records through out the field. I have a productivty project were I need to identify the idle time.

I hope this posting make sense, any help is greatly appreciated.

-Gus

 

[Xander69]

In Business Objects there's a PREV function which would allow you to compare the value of the field with the previous value of the field:

difference = field_value - prev(field_value)

Not sure if this is applicable to Crystal reports though, but thought I'd mention it.

Jamie Alexander
Web Guru and All Round Good Guy

http://www.jamie-alexander.com

 

[gusc999]

Thanks for the tip I'll look into it.

 

[lbass]

Assuming the field is a datetime and that you want the difference in seconds, you can use:

datediff("s",previous({table.trantime}),{table.trantime})

If it is a time field, and if the times always occur within the same day, you can use:

datediff("s",datetime(currentdate, previous({table.trantime})),datetime(currentdate,{table.trantime}))

-LB

 

[gusc999]

I copied the formula but it's giving me an error, "The ) is missing". Below is a copy of what I copied only change was the field.

datediff("s",datetime(currentdate, previous({@POTIME}),datetime(currentdate,{@POTIME}))

 

[lbass]

You left out a paren:

datediff("s",datetime(currentdate, previous({@POTIME})),datetime(currentdate,{@POTIME}))

-LB

 

[gusc999]

LB,

I added the paren but I then get the following error "This field has no previous or next value". Please assist.

datediff("s",datetime(currentdate, previous({@POTIME}),datetime(currentdate,{@POTIME})))

 

[lbass]

What is the content of {@POTTIME} and of any nested formulas within it?

-LB

 

[gusc999]

Yes it has the following:

WhileReadingRecords;
Local NumberVar ConvertTime := {PORCV.PORTIM};

Local NumberVar MyHours;
Local NumberVar MyMinutes;
Local NumberVar MySeconds;

MyHours := ConvertTime \ 10000;
ConvertTime := ConvertTime mod 10000;
MyMinutes := ConvertTime \ 100;
MySeconds := ConvertTime mod 100;

Time(MyHours,MyMinutes,MySeconds)
-------------------------------------------------

However when I attempt to do it with the original field I get the follow "A time is required here" and it points to the last field in the formula.

datediff("s",datetime(currentdate, previous({PORCV.PORTIM}),datetime(currentdate,{PORCV.PORTIM})))

 

[lbass]

It's because you are using variables. If you instead use a formula like the following for the conversion:

time(truncate({PORCV.PORTIM}/10000),
truncate(remainder({PORCV.PORTIM},10000)/100),
remainder({PORCV.PORTIM},100))

...I think this will work.

-LB

 

[gusc999]

Almost...

"Too many argument have been given to this function" that's the new error.

what do you think?

 

[lbass]

I tested this and it appeared to work. Did you create this formula as {@convert} and then use it in the datediff formula? Or were you having trouble with this formula first? Where was the cursor when you got this error?

-LB

 

[gusc999]

The cursor was on "datetime(currentdate,{@POTIME}".

First formula:
//WhileprintingRecords;
//WhileReadingRecords;
//Local NumberVar ConvertTime := {PORCV.PORTIM};
//
//Local NumberVar MyHours;
//Local NumberVar MyMinutes;
//Local NumberVar MySeconds;
//
//MyHours := ConvertTime \ 10000;
//ConvertTime := ConvertTime mod 10000;
//MyMinutes := ConvertTime \ 100;
//MySeconds := ConvertTime mod 100;
//
//Time(MyHours,MyMinutes,MySeconds)

time(truncate({PORCV.PORTIM}/10000),
truncate(remainder({PORCV.PORTIM},10000)/100),
remainder({PORCV.PORTIM},100))


Second Formula:
datediff("s",datetime(currentdate, previous({@POTIME}),datetime(currentdate,{@POTIME})))

 

[lbass]

You still have the parens wrong:

datediff("s",datetime(currentdate, previous({@POTIME})),datetime(currentdate,{@POTIME}))

-LB

 

[gusc999]

LB,

If you publish a book I'll buy it. It's working like a charm. However I got just one more request if I may.

I need to use the select expert on this field but it does not come up. I'm sure it has to do with the formula. Is there a way we can get it were I can only view the ones with > 15 minutes.

Thank you!!

 

[lbass]

You can't select on a formula like this because it is based on sequential records. All you can do is conditionally suppress the section by using a formula like this:

datediff("n",datetime(currentdate, previous({@POTIME})),datetime(currentdate,{@POTIME})) <= 15

Note that I changed the seconds to minutes (n = minutes in datediff, to distinguish it from months).

If you have groups, you will have to account for them also in your formula--to eliminate comparisons with other group instances.

-LB

 

[gusc999]

I have it grouped by user so how do I account for them in the formula? I just tried it and I'm getting all "TRUE" in the field.

 

[lbass]

You shouuld be creating the formula in the section expert->details->suppress->x+2 area. Make sure you change the "s" to "n".

Change the formula to:

{table.user} = previous({table.user}) and
datediff("n",datetime(currentdate, previous({@POTIME})),datetime(currentdate,{@POTIME})) <= 15

-LB

 

[gusc999]

LB,

Thank you for you help it worked perfectly plus you showed me feature I never knew was available. Your the best....