formula help please, time difference 1

[ziggs]

I'm looking for a formula to give a time difference with two criteria. For every row that is GOOD, a time difference rounded to a second with post. However, when a "TIMEOUT" occurs, the time difference, rounded to the second, will be the difference between the first "GOOD" row and the next "GOOD" row. Thus, I'll be excluding the "TIMEOUT" posts. In the example below, 48 seconds would post.

Here's the result table for an example.

ID Status NextRec

187 GOOD 09/02/2008 10:45:43
188 GOOD 09/02/2008 10:46:54
189 TIMEOUT 09/02/2008 10:47:03
190 TIMEOUT 09/02/2008 10:47:12
191 TIMEOUT 09/02/2008 10:47:21
192 TIMEOUT 09/02/2008 10:47:30
193 TIMEOUT 09/02/2008 10:47:39
194 TIMEOUT 09/02/2008 10:47:41
195 GOOD 09/02/2008 10:47:42
196 GOOD 09/02/2008 10:48:39
197 GOOD 09/02/2008 10:49:51

Results needed:

ID Status NextRec Diff in sec

187 GOOD 09/02/2008 10:45:43 1st record, blank
188 GOOD 09/02/2008 10:46:54 1
189 TIMEOUT 09/02/2008 10:47:03
190 TIMEOUT 09/02/2008 10:47:12
191 TIMEOUT 09/02/2008 10:47:21
192 TIMEOUT 09/02/2008 10:47:30
193 TIMEOUT 09/02/2008 10:47:39
194 TIMEOUT 09/02/2008 10:47:41
195 GOOD 09/02/2008 10:47:42 48
196 GOOD 09/02/2008 10:48:39 1
197 GOOD 09/02/2008 10:49:51 1

Any ideas?

TIA

 

[lbass]

Try the following formula:

whileprintingrecords;
datetimevar prev;
datetimevar curr;
prev := curr;
if {table.status} = "Good" then
curr := {table.nextrec};
datediff("s",prev,curr);

-LB