Using variable to return diff between 2 dates in hh:mm:ss

[tkaz]

I am using CR XI and SQL Server DB.

Table structure gives plant_nbr,vehicle_nbr,ticket_number and ticket_date. During a business day, we may have one vehicle that picks up product from our plants and delivers to job sites several times, creating a record in the TicketTable each time.

The report I have is grouped on plant_nbr, vehicle_nbr and in the details, I have the plant_nbr,product, ticket_number, Qty_del,and ticket_date. To determine the time between pickups, I have searched the site and found FAQ767-3543 to be helpful in displaying the difference between 2 dates in HH:MM:SS.

However, I do not want to see Cycle Time on the first pickup of the day. Can someone help me with the formula to correct this?

I placed this formula in the details section and I get the correct cycle times between pickups, but it displays on first pickup:

whileprintingrecords;
numberVar dur := datediff("s",{vwAMS_TCKH_TLDH_CUST_PLNT.TICKET_DATE}, Previous({vwAMS_TCKH_TLDH_CUST_PLNT.TICKET_DATE}));
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");

hhmmss

OUTPUT EXAMPLE:

Plant Prod VehicleID Qty Ticket Time Cycle Time
04114 507 60496 11.75 15021 08:16:09 -2:27:18
04114 507 60496 25.45 15023 10:42:52 2:25:53
04114 509 60496 17.35 15030 12:57:02 2:14:10
04117 511 60496 10.00 15032 02:57:02 2:00:00


I have this problem on every group of vehicles except the first one. I hope I have provided enough information. Thank you for your help.

 

[IanWaterman]

Try

whileprintingrecords;
numberVar dur := datediff("s",{vwAMS_TCKH_TLDH_CUST_PLNT.TICKET_DATE}, Previous({vwAMS_TCKH_TLDH_CUST_PLNT.TICKET_DATE}));
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");

If not onfirstrecord then
hhmmss
else ''

Ian

 

[tkaz]

I copied and pasted your code and it still shows that first record. Thanks for the suggestion though.

 

[lbass]

Change the last of Ian's formula to:

if onfirstrecord or
{table.groupfield} <> previous({table.groupfield}) then
"" else
hhmmss

-LB

 

[tkaz]

Thank you so much...that worked fine.