Compare Time In Seconds for Elapsed Total

[Zbrojovka]

Hello Again!! Using: Crystal Reports 2008 against SQL 2005 DB

I have a Table/Field, {incilog.timeinsecs} that stores the time of a transaction in Seconds format.

I am trying to calculate the total time elapsed from the first transaction to the last. For example:

Dispatched-60154 which is 16:42:34
En Route-60169 which is 16:42:49
Arrived-60849 which is 16:54:09
Cleared-79727 which is 22:08:47

I need to know how much time elapsed from the Dispatched transaction to the cleared transaction. I can convert the seconds into HH:MM:SS format just fine, but I'm lost on how to compare the values for total time spent on the call in second format so it can then be converted.

 

[Zbrojovka]

All right guys and gals...I've pulled my hair out over this one all morning. LB you've been a tremendous help and I wouldn't have gotten this far without you, but I'm still stuck.
Here's what happens: We know I'm getting a negative number on the Unit Group when their activity overlaps midnight and into the following day. As far as I can tell the formula is handling dates correctly.

I've discovered when deleting this last section of the formula that the date overlap is calculated correctly, but then all other groups are only displaying the last "Cleared" time in seconds:

secs := end - start;
start := 0;
end := 0
);
unit := unit + secs;
inc := inc + secs;
secs

I've modified this last section over and over, moved it around for the order of operations in it's parent formula, but I never get the desired results. If we take out the 5th and 6th lines along with the corresponding numbervar everything still works so we can drop those for simplicity. I'm out of ideas...

LB, any idea why this last section would be throwing off everything?

 

[lbass]

I did test this, and it worked properly. I'm wondering whether you might have old formulas or resets that reference these variables somewhere on your report canvas that are interfering with this formula. In design view see if you can identify any old formulas that you are no longer using.

-LB

 

[Zbrojovka]

LB, I did as you suggested and cleared away any and all formatting or selection formulas that weren't relevant to this issue. I've even dropped the Incident Group. So all I've got going now is one group on Unitcode and the @Time2 formula of yours. Still getting the negative number seen in the previous screen shot. Obviously we've got something different if it's working right on your side. Sounds like a reverse engineer is in order.

 

[lbass]

Did you do the checks I suggested in my next to last post to ensure the values were accurate? Please explain the results you found.

-LB

 

[Zbrojovka]

I did test as you said by totext (variance). The date variances display correctly, Start and End both display 0.00 in DetailsB and when placed in DetailsA I get the timeinsecs for Enroute, Arrived, and sometimes Dispatched, but never Cleared. Cleared is always 0.00

 

[lbass]

Please try this formula instead of the previous one:

whileprintingrecords;
numbervar start;
numbervar end;
datevar startdt;
datevar enddt;
numbervar secs := 0;
numbervar unit;
numbervar inc;
if {incilog.transtype} = "D" then (
start := {incilog.timeinsecs};
startdt := date({incilog.timestamp})
);
if {incilog.transtype} = "C" then (
enddt := date({incilog.timestamp});
end := {incilog.timeinsecs}
if enddt = startdt then
secs := end - start else
secs := 86400 - start + end;
start := 0;
end := 0
);
unit := unit + secs;
inc := inc + secs;
secs

-LB

 

[Zbrojovka]

Good morning and thank you for the update LB. I had in fact tried doing that myself. Here's a screen shot of what happens when you switch those components around. The screen shot explains better than I could.

http://i955.photobucket.com/albums/ae33/zbrojovka/ManHours1.jpg

Do you think there might be a better way? This is so close and really a great way to accomplish what I wanted.

 

[lbass]

Please post the exact formula you used (your results are not what should appear with this formula), and then explain what continues to be wrong. I did not "switch" stuff, I made a significant change to the formula.

-LB

 

[Zbrojovka]

Here's @Time2:

whileprintingrecords;
numbervar start;
numbervar end;
datevar startdt;
datevar enddt;
numbervar secs := 0;
numbervar unit;
numbervar inc;
if {incilog.transtype} = "D" then (
start := {incilog.timeinsecs};
startdt := date({incilog.timestamp})
);
if {incilog.transtype} = "C" then (
enddt := date({incilog.timestamp});
if enddt = startdt then
end := {incilog.timeinsecs} else
end := 86400-start+{incilog.timeinsecs};
secs := end - start;
start := 0;
end := 0
);
unit := unit + secs;
inc := inc + secs;
secs

Here's @Time3 with the latest edits:

whileprintingrecords;
numbervar start;
numbervar end;
datevar startdt;
datevar enddt;
numbervar secs := 0;
numbervar unit;
numbervar inc;
if {incilog.transtype} = "D" then (
start := {incilog.timeinsecs};
startdt := date({incilog.timestamp})
);
if {incilog.transtype} = "C" then (
enddt := date({incilog.timestamp});
end := {incilog.timeinsecs};
if enddt = startdt then
secs := end - start else
secs := 86400 - start + end;
start := 0;
end := 0
);
unit := unit + secs;
inc := inc + secs;
secs

When I said "switch" I meant the changing of the 3 lines of formula between the end variance and start := 0.

As can be seen in the screen shot @Time2 accurately gives the time in seconds elapsed from Dispatched to Cleared with the exception of the instance where the activity for Unit 107 occurred on 08/23 past midnight into 08/24.

The @Time3 formula is displaying the Cleared time as it's value with the exception of Unit 107 who's value reads 100203. If you subtract 100203 from Unit 107's Cleared time you get 86400.

 

[lbass]

I'm concerned about your display of the three versions of the formula on the report. The presence of old versions will interfere with the functioning of the current version. Can you verify that you are using version 3 by itself (but of course with the reset and display formulas for inc and unit)?

-LB

 

[Zbrojovka]

Excellent news sir....the third formula works great. I had removed all the extra formulas previously with @Time2, but put them back when I couldn't get rid of the negative value. It didn't dawn on me to remove them again with @Time3. I learned a lot working with this so hopefully I'll be able to put your guidance to good use. Thank you for your patience and time spent helping us all.