Compare to previous row

[HelpJohn]

I am a novice and new to sql and crystal reports. I am currently using Crystal reports XI.

I have a table with transactions, where the time is recorded in 24hour format under three fields, hour, min and sec.

These transactions happen across 500 different locations.

I would like to return only transactionsthat are within 15 min of each other in the same location.

If anybody could offer any advice, I would be most appreciative.

Johnny

 

[chelseatech]

You can use the Previous() or Next() functions to interrogate data fields in adjacent records.

So a formula of
Abs (DateDiff("n",Table.DateField, next(table.DateField)))
< 15

would work okay. Use this in the Section expert to suppress the section if the criteria is met.

Can't do this in record selection as you have to get the records first, sort them before you can use this logic. Might be able to do it in Group Selection.

If you want to see both records might need to use...
Abs (DateDiff("n",Table.DateField, next(table.DateField)))
< 15
OR
Abs (DateDiff("n",Table.DateField,
previous(table.DateField))) < 15

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

I think the detail section suppression formula would have to be:

abs(datediff("n",{@datetime},previous({@datetime}))) > 15 and
abs(datediff("n",{@datetime},next({@datetime}))) > 15

Also, you need to convert your three fields into a datetime. What datatype are they? Numbers? If so, use:

//{@datetime}:

datetime({table.date},time({table.hours},{table.mins},{table.secs}))

-LB