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.

 

[lbass]

I'm assuming you are evaluating this per some group, so use a formula like this:

datediff("s",minimum({incilog.timeinsecs},{table.groupfield}), maximum({incilog.timeinsecs},{table.groupfield}))

Then convert this formula to the string display.

-LB

 

[Zbrojovka]

Thank you LB, I had in fact tried something similar to this, but the datediff function wants a date/time value. {incilog.timeinsecs} is just a number field so the formula errors out.

You were correct, I am grouping the data by {incilog.unitcode}. I suppose I can convert the timeinsecs to date/time for datediff, but I was hoping for a more ingenious method.

 

[Zbrojovka]

Allright, so I have most of this working, but I have a quagmire. For the data evaluated, there is the possibility the same person could clear the incident and then return to the incident the following day. Essentially respond to the same call twice. So they would end up with two Dispatched times, two EnRoute times, two Arrived times, and two Cleared times. When grouping by Unit this list the aforementioned activity under that unit. Evaluating the minimum of the timestamp and the maximum of the timestamp skews the comparison since it sees a rollover to the next day.

Here's my formula for displaying total time per group:
Local NumberVar Days;
Local NumberVar Hours;
Local NumberVar Minutes;
Local NumberVar Remainder_Seconds;

Days := Int({@ManHoursCalc} / (24*60*60));
Remainder_Seconds := {@ManHoursCalc} Mod (24*60*60);

Hours := Int(Remainder_Seconds / (60*60));
Remainder_Seconds := Remainder_Seconds Mod (60*60);

Minutes := Int(Remainder_Seconds / 60);
Remainder_Seconds := Remainder_Seconds Mod 60;

ToText(Days,0) & ' Days, ' &
ToText(Hours,0) & ' Hours, ' &
ToText(Minutes,0) & ' Minutes, '&
ToText(Remainder_Seconds,0) & ' Seconds';

I've also linked to a screen shot so you can see how the data is being presented. I'm open to different grouping suggestions if you think it would help me, but if I can insert a "break" per se that would stop evaluating after the first day and then re evaluate for the second occurrence that would be fantastic.

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

 

[lbass]

Sorry, I wasn't paying attention. I should have said to use:

maximum({incilog.timeinsecs},{table.groupfield})- minimum({incilog.timeinsecs},{table.groupfield}))

Then convert the number into the string.

I am unclear what you want the result to be in the instance where there are two sets of activities for the same incident. Do you want to show these separately? If so, why not insert a group on date within incident, and then use the date groupfield as the group condition in your formula?

-LB

 

[Zbrojovka]

LB, I did as you suggested and inserted a group based on date. That allowed me to separate the occurrences and get total time on call per day. What I didn't think about was when someone remained on scene through the next day. So I have both scenarios in play here. Some people remained on scene over night and some people didn't.

When I group by date those that stayed over night can not be calculated since they don't have a comparison for their cleared time.

 

[lbass]

How do you want to display the time when then there are two sets of data for one incident because the person returns on the second day? Do you want the results for the first day below the first day and then the results for the second day at the end of the second day?

-LB

 

[Zbrojovka]

LB, I could do it as you describe or even lump them all together as one value. That part isn't as important. The end result is just to show the total man hours spent per unit and then I will total every unit to show a grand total of man hours for the incident. It would be a luxury if it were broken down per unit per day, but not as necessary.

 

[lbass]

What is your group structure? I'm not following how unit relates to incident. Is there more than one incident per report so that you are grouping on incident and then on unit, with one or two days per unit? Are you looking for totals at the unit, incident, and grand total level?

-LB

 

[lbass]

Try a formula like this (this assumes the field keeps accumulating secs if it crosses days):

whileprintingrecords;
numbervar start;
numbervar end;
numbervar secs := 0;
numbervar unit;
numbervar inc;
if {table.transactiontype} = "Dispatched" then
start := {incilog.timeinsecs};
if {table.transactiontype} = "Cleared" then (
end := {incilog.timeinsecs};
secs := end - start;
start := 0;
end := 0
);
unit := unit + secs;
inc := inc + secs;
secs

Insert a second detail section and suppress it conditionally with this formula:

{table.transactiontype} <> "Cleared"

Place the secs formula into this detail_b section.

Place a reset formula in the unit group header:

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

If incident is a group, create a reset formula for that group header also for the variable "inc".

Place a display formula in the unit group footer:

whileprintingrecords;
numbervar unit;

Place a display formula in the incident group or report footer:

whileprintingrecords;
numbervar inc;

Within each display formula, you can convert the results back to a string if you like.

-LB

 

[Zbrojovka]

LB, for some reason I didn't get notified of your more recent post and missed your last question.

The Group structure is based off a selected incident. Within this incident there were multiple units who responded or had activity. The incident lasted a couple of days. So we have one incident with multiple units across multiple days.

So I've just grouped by Unit, then sub grouped by Date as you suggested. I don't need to sub group by date unless the calculations for manhours require it.

I'll look at your most recent suggestions and see what I can make of it. Thanks for you help. Let me know if you still need more info. If photo bucket comes back online I can post another screen shot to give you a better idea if you need.

 

[Zbrojovka]

Oh, and I'm looking for totals at the Unit level with a grand total.

 

[Zbrojovka]

Ok, I've used your suggestions and everything is looking beautiful! Just one more catch....how do I account for the negative time values when a unit is on the incident past midnight?

I found this example and many others dealing with negatives, but I'm not sure how that works into what has already been done:

if time({table.datetime}) > time(17,0,0) then
datetime(date(dateadd("d",1, {table.datetime})),time(7,0,0) else
if time({table.datetime}) < time(7,0,0) then
datetime(date({table.datetime}),time(7,0,0)) else
{table.datetime}

Can I make an addition to the existing @sec formula or will I need to tackle the negatives and then send it through @sec?

 

[lbass]

Try a formula like this (this assumes the field keeps accumulating secs if it crosses days):

Are you saying that seconds starts at zero at midnight? Please show some sample data that demonstrates what happens.

-LB

 

[Zbrojovka]

You are too correct in pointing me to your quote....I somehow missed that last very important segment....my apologies.

Yes, Seconds restart at 0 at 00:00:00. Here's a screen shot of what's currently happening:

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

 

[lbass]

Try changing the first formula to:

whileprintingrecords;
numbervar start;
numbervar end;
datevar startdt;
datevar enddt;
numbervar secs := 0;
numbervar unit;
numbervar inc;
if {table.transactiontype} = "Dispatched" then (
start := {incilog.timeinsecs};
startdt := {table.date}
);
if {table.transactiontype} = "Cleared" then (
enddt := {table.date};
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

-LB

 

[Zbrojovka]

LB, the last one is causing some issues. Here's the first @Time formula of yours I implemented which is working great with the exception of negatives:

whilereadingrecords;
numbervar start;
numbervar end;
numbervar secs := 0;
numbervar unit;
numbervar inc;
if {incilog.transtype} = "D" then
start := {incilog.timeinsecs};
if {incilog.transtype} = "C" then (
end := {incilog.timeinsecs};
secs := end - start;
start := 0;
end := 0
);
unit := unit + secs;
inc := inc + secs;
secs

Here's the second @Time2:

whileprintingrecords;
numbervar start;
numbervar end;
datetimevar startdt;
datetimevar enddt;
numbervar secs := 0;
numbervar unit;
numbervar inc;
if {incilog.transtype} = "D" then (
start := {incilog.timeinsecs};
startdt := {incilog.timestamp}
);
if {incilog.transtype} = "C" then (
enddt := {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

I've linked a screen shot to show the resulting values in seconds. @Time is on the left and @Time2 is on the right. I've left off the conversion to HH MM SS for @Time2 since we can see by the seconds it would be different.

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

Did I break your formula by using the DateTimeVar?

 

[lbass]

Yes, use date, and if your field is a datetime, then wrap it in date().

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

-LB

 

[Zbrojovka]

Ok, I'm with you so far and I plugged the corrected formula in. I've attached a screen shot and you can see there's still a negative number. I'm not sure how it's coming up with -52587. If you follow the logic of the formula we should be coming with 23808 for time in secs on @Time2. The problem seems to be occurring before we get to:

end := 86400-start+{incilog.timeinsecs};

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

 

[lbass]

You will have to test some of this yourself. I would check the value of enddt and stdate one at time, by adding it at the end of the formula to make sure they are returning the correct values, and then also check the values of start and end. I don't see how you could get a negative number here, since your seconds field could never be greater than 86400, so it seems that the date check must not be working.

-LB