Crystal Enterprise 9: How to Calculate average for datediff

Asked Aug 01 '14 at 16:04

Active Aug 04 '14 at 17:17

Viewed 136 times

So I have this statement that calculates the time difference. It uses the previous mod_date minus the current mod_date. Now I would like to get the average of each users time interval, basically average of DateDiff.

Below is the SQL to get the time interval:

(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60

I've tried something like:

 Select AVG(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60

But I keep getting prompted for some numerical value on AVG().

edited Aug 04 '14 at 17:17

asked Aug 01 '14 at 16:04

user2668214

`DateDiff` is not a function that exists by default in Oracle. Did you create a user-defined function by that name to replicate the function that exists in other databases like SQL Server? – Justin Cave Aug 01 '14 at 16:12
I'm using it in Crystal 9 as a Report – user2668214 Aug 01 '14 at 16:21
Where are you using that query in crystal report? – Siva Aug 01 '14 at 17:11
In the Formula Fields – user2668214 Aug 01 '14 at 18:01
I get, "The remaining text does not appear to be part of the formula" error. – user2668214 Aug 04 '14 at 12:39
If I type out Average instead of AVG for this statement, Select Average(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60 I get, "A field is required here" message – user2668214 Aug 04 '14 at 13:06

Crystal Enterprise 9: How to Calculate average for datediff

0 Answers0