How to Group Dates

[ejeffcott]

I need to be able to group a set of dates by 30 days away, 60 days away and 90 days away.

I'm new to Oracle Reports and I've been researching within the help tools, but have been unsuccesful in finding a solution.

Can anyone please shed some light on how to accomplish this?

I have included an abridged version of my code.

Thank you.

select c.casename, c.casenumber, c.trialdt

from t_case c

 

[cruishall]

I will suggest that you create a function that will calculate for the AGE of your data, and this function should be in your break group. This way, you will be able to group your data by the age:

FUNCTION get_age RETURN NUMBER IS
v_age NUMBER;
v_return NUMBER;
BEGIN
v_age := TRUNC(SYSDATE) - :date_var;
IF v_age < 31 THEN
v_return := 30;
ELSIF v_age BETWEEN 31 AND 60 THEN
...
ELSIF v_age BETWEEN 61 AND 90 THEN
...
END IF;
RETURN v(_return);
END;

 

[nagornyi]

Or you can add a calculated field to the query that would show how old the case is (in 30 day increments), and then group by the field. Like

select 
  c.casename
, c.casenumber
, c.trialdt
, [b](trunc((sysdate-c.trialdt)/30)+1)*30[/b] age
from t_case c