How to get a correct time stamp

[korytnackaruzova]

Good afternoon,

I kinda got stuck writing a report In Crystal report 2008. I have a database with several time stamps for several kategories (names). I'm only looking for Address and Problem Category (which I'm filtering). I'm trying to get a time stamp for the first problem and the time stamp for the last address before the first problem. For example.

Address 00:36:00
Address 00:38:00
Problem 00:40:00
Problem 00:42:00
Address 00:44:00

My results should show/contain time stamps 00:38:00 for the address and 00:40:00 for the problem.

My logic was to create a column {@MIN_Problem} and use Min formula for the problem- "if {Response_Edit.Field_Name} like "Problem" then Minimum ({Response_Edit.Date_Time})". And the formula works, but it's giving me only value for the Problem name. Address name is blank. I believe that is breaking my other logic I wanted to use.

Then I wanted to write a statement {@LessThanMINProblem}:
"if (({Response_Edit.Field_Name} like "Address") and ({Response_Edit.Date_Time}) < {@MIN_Problem}) then "YES"
else "NO""
This would give me "YES"/"NO" value for the Address, which I would plug into the Max formula:
"if {Response_Edit.Field_Name} like "Address" and {@LessThanMINProblem} like "Yes" then Maximum ({Response_Edit.Date_Time}, ({Response_Edit.Field_Name}))"

The results using these formula are correct for the Problem nature, but the address I'm getting 00:44:00, which is not correct. It should be 00:38:00

I hope this makes a sense. Any help in this case would be very, very appreciated.

Thank you!

 

[lbass]

You should change your address formula to:

//{@Addressmeetingcriteria}:
If {response_edit.field_name} like “Address” and
{@LessThankMINProblem}=“Yes” then
{response_edit.date_time}

Then insert a maximum on this formula or write a second formula with a maximum on this formula. Be sure to use your own quote marks as mine will error out.

-LB

 

[korytnackaruzova]

Thank you lbass, I was hoping that you would answer. You've always helped me in the past.

The problem is, that my first formula- {@MIN_Problem} is giving me blank value for the address, so my second formula- {@LessThanMINProblem}- has "YES" value for every address. And my third formula is giving me then the maximum (please, see the attachment, the last column should have a value of 2:49:59).

 

[lbass]

First sort the datetime field in ascending order. Then create a formula:

whileprintingrecords;
datetimevar AddDT;
datetimevar ProbDT;
numbervar i;
numbervar k;

if {table.Field_Name}="Address" and
i=0 and
next ({table.Field_Name}) = "Problem" then
AddDT := {table.datetime};
if {table.Field_Name}="Problem" then
i := i + 1;
if i = 1 then
k := k + 1;
if k = 1 then
ProbDT := {table.datetime};

If the fieldname just contains the words problem and address, instead of only using those words, then continue to use "like" instead of "=" in the formula above.
Then create two display formulas for the report footer:

//{@displayAddDT}:
whileprintingrecords;
datetimevar AddDT;

//{@displayProbDT}:
whileprintingrecords;
datetimevar ProbDT;

If you are using a group, you would have to add reset formulas for each variable to the group header.

-LB

 

[korytnackaruzova]

thank you very much! I see the logic behind it and it make sense and I think it might work!
I'm using a group based on an incident ID. Is it too much to ask how the reset formula would look like? Or do I do running total field?

 

[lbass]

Why would you need a running total—are you planning to count something?

For the resets, add a formula to the group header section:

Whileprintingrecords;
Datetimevar AddDT := datetime(0,0,0,0,0,0);
Datetimevar ProbDT := datetime(0,0,0,0,0,0);
Numbervar i := 0;
Numbervar k := 0;

You can suppress the formula.

-LB

 

[korytnackaruzova]

Thank you so much! I'm getting correct time stamp for the address. However, I'm now getting the latest time stamp (for either field_name- not necessarily just for the problem) for the ProbDT formula (please, see the attachment).

Ultimately, I need to calculate 1. fixed time (a fixed field from the database) to last address before first problem time stamp for every incident AND 2. last address before first problem time stamp to first problem time stamp for every incident.

I was able to calculate the 1st thing using Formula: Time(0,0,0) + (Time({@AddressDT})-Time({table.Time_PickUp})) and it worked perfectly! Thank you! I will have to work on the second piece.

 

[lbass]

I can’t tell how you implemented my suggestion. Maybe you could send the report itself (saved with data) with identifying info removed.

I did test this and it worked here.

-LB

 

[lbass]

PS. I would be careful about using only the time() function, as you could end up ignoring dates that would have an impact in your calculations. You can always DISPLAY only the time, while using datetimes in your calculations.

-LB

 

[korytnackaruzova]

You are correct on the only time() function.
Thank you very much for your help (as always!). I actually made it work and it works perfectly.