Using a ~DateTime field in a Transaction list - to work out an elapsed even

[Former Member]

Hi all. I do hope you Gurus can help me.

the situation is that we have an event table, with event codes...and event times.

say code 100, occurs for a record on Tuesday at 14:00:00...then the next event we are looking to measure the time between event 100...lets call this code 200...occurs on Tuesday at 16:00:00....i need to extract the elapsed time between the time of event 100 and event 200.

In other words the elapsed time of 2:00:00 hours.........

I have created a time event based on code 100 happening...then a time event based on 200 happening...But they cannot subtract 200 from 100 time, to give me the 2:00:00 elapsed hours........

Can any one give me, guide me on the route to solve this one.

[Former Member · Accepted solution]

Hi, These are getting very helpful, but I am missing a bit of knowledge, and I am not a Basic syntax user....

So this, from my thoughts/testing might help you to help me!!

Formulae for {@R_BothTimes}

whilereadingrecords;

if {ticket_events.te_event_code} = 100 then {ticket_events.te_date}

else

if{ticket_events.te_event_code} = 147 then {ticket_events.te_date}

This looks at the two event codes, 100 and 147...then reads in the singel te_event_code time...and places this onto the transaction line...

I then am looking at.....this sort of code...

if previous ({ticket_events.te_event_code}) = 100 then {@R_BothTimes}

//this coudl look back and get the time 100 started, so i can then look at the eleapsed time between 100 and 147.....

but it is not working!!!!! Your help is trully apprecaiated.

[Former Member · Accepted solution]

{@R_BothTimes is not looking at the prior record. It's returning the date from the current record. The formula should be

if previous ({ticket_events.te_event_code}) = 100 then Previous({ticket_events.te_date})

in order to get the date of the 100 record. This would then be DateDiff()ed with the current record's date to get the time between records. Note also that this would only be good for the record after the code 100 record. If you had three records for the event, say 100, 147, and 900, this formula will not give the date of the 100 record for the 900 record. For that you need to use global variables, as my original post shows.

HTH,

Carl

[Former Member]

Thank you for this offering, it looks very useful.

But...I am not sure if the variables time1 and time2 are going to function as you expect....

In that I have one current variable that holds a date time for the event happening, so how in this am I checking for that event having happened??

[Former Member]

Sort your records by status code. You can then reference the prior record's field value with PreviousValue() function, so you can do a DateDiff("s", PreviousValue(), ) (or should the parameters be reversed??). If you want to time everything from the 100 code, create a formula field that holds the timestamp from that record in a global variable, something like (basic syntax):

global Time100 as datetime

if {statuscode} = 100 then
  Time100 = {timestamp}
end if

formula = ""

You can then calculate the elapsed time with:

global Time100 as datetime
formula = DateDiff("s", Time100, {timestamp})

HTH,

Carl

[Former Member]

Can you tell how you have created the event for 100 and 200?

[Former Member]

Hi, thanks for your reply.

the front end application, posts a status change to a record...these records move through various status's with various codes.

there is for each change of status a singel date time stamp....and they get this each time the record changes status.

so code status code 100 happens to the record before status code 200.......

so i need to see how long it took for the record to progress from status 100 to status 200....simply extract the times, retunrs to me 00:00:00 as a time, as the event 200 cannot seem to see the event 100 datetime value......

hoep this hleps you to help me!!

[Former Member]

Check this

Local datetimeVar Time1 := DTSToDateTime("19/2/2010 02:00pm");
Local datetimeVar Time2 := DTSToDateTime("19/2/2010 04:00pm");
Local numberVar Diff;
Local numberVar hours;
Local numberVar minutes;

Diff := DateDiff("n",Time1,Time2);
hours := Diff\60;
minutes := Diff mod 60;

totext(hours,0,"")&":"& totext(minutes,"00")