Grouping on a DateFormula 1

[BRL123]

I am running Crystal 8 and using an ODBC to an Oracle DB.
I need to group by the a datetime field where the most recent date minus the next most recent date falls within categories of Less than 2 years, 2-4 years, 4-6 years etc.
The same id can have multiple entries so how do I,after sorting by ID and then sorting by my date field, subtract the second most recent date?
Sorry if my explanation wasn't the best

 

[DataDivaJill]

How about creating a formula that would be used for grouping only
example:
SortYears = if year(datetime field) =>year(today) and year(datetime field) <=< year(today)+2 then 1 else
if year(datetime field) =>year(today)+2 and year(datetime field) <=< year(today)+4 then 2 else .....

if it can't be the year number and must be totally 365 days from today, then try:

SortYears = if datetime field => today and datetime field <=< today+730 then 1 else
if datetime field => today+731 and datetime field <=< today+1460 then 2 else.....

 

[BRL123]

I am not sure if I missed something or most likely didn't explain it too well...but I don't see how this compares the datetime field from 2 different rows (the most recent and the second most recent) ?

 

[lbass]

You can certainly subtract dates, by using:

//{@reset} to be placed in the group header;
whileprintingrecords;
numbervar cnt := 0;

//{@diff} to be placed in the detail section:
numbervar cnt := cnt + 1;
if cnt = 2 then
datediff("d",previous({table.date}),{table.date})/365.25

(I wouldn't use years in this function, since it will literally subtract the years, regardless of the month or day). However, you will not be able to sort by the result or group on it. The only way you could do that is if you were able to return the maximum date per group and the minimum date per top2 dates per group to the report by adjusting the SQL query.

-LB