PHIL Sawyer

asked on

# using RECURSIVE WITH statement - using oracle 11g

Hello

Is it possible to use sql statement (not PLSQL) to solve the following - eg

Table1 has 2 columns - dates and an amount against each date.

Assume 4 years of data ....

Date Amount

01/01/2000 25

01/02/2000 46

etc etc

Here is the logic - lets say for the first year from 01/01/2000 through to 31/12/2000 has a max value in that date range of 46.

I then want to jump forward by one month and work out the next max value - date range

01/02/2000 through to 01/01/2001 and the max value in that range is say 56.

I want to carry out this process for all the dates in the table and in theory will have a number of max values for reach time window where I jump foward by one month.

I then want the max of all the max values.

eg max(46,56,......................................)

Hope this makes sense

Regards

Is it possible to use sql statement (not PLSQL) to solve the following - eg

Table1 has 2 columns - dates and an amount against each date.

Assume 4 years of data ....

Date Amount

01/01/2000 25

01/02/2000 46

etc etc

Here is the logic - lets say for the first year from 01/01/2000 through to 31/12/2000 has a max value in that date range of 46.

I then want to jump forward by one month and work out the next max value - date range

01/02/2000 through to 01/01/2001 and the max value in that range is say 56.

I want to carry out this process for all the dates in the table and in theory will have a number of max values for reach time window where I jump foward by one month.

I then want the max of all the max values.

eg max(46,56,................

Hope this makes sense

Regards

What's the point of using recursive function for your target output? You can just get the max of all the amount.

select trunc(date,'MM') date, max(amount) amount

from table1

group by cube(trunc(date,'MM') ) -- group by month, the cube gives extra totals for each level

-- in the group

order by trunc(date,'MM')

Are there time-windows without data ? If yes, what should happen with them ?

from table1

group by cube(trunc(date,'MM') ) -- group by month, the cube gives extra totals for each level

-- in the group

order by trunc(date,'MM')

Are there time-windows without data ? If yes, what should happen with them ?

>I then want the max of all the max values.

isn't that the overall max() value?

i mean, what is the point of getting the "intermediate" maxes, if the end result is 1 single max value?

please clarify

isn't that the overall max() value?

i mean, what is the point of getting the "intermediate" maxes, if the end result is 1 single max value?

please clarify

ASKER

Sorry - I meant the average for each time window - so, assume the average is 22 then 45

I then want

max(22,45,......................................)

Regards

I then want

max(22,45,................

Regards

ASKER

flow001

No time windows without date

No time windows without date

so you want the MAX() over the monthly averages?

select max( avg_value )

from ( select average(value), trunc(date, 'MM') from yourtable group by trunc(date, 'MM') )

select max( avg_value )

from ( select average(value), trunc(date, 'MM') from yourtable group by trunc(date, 'MM') )

ASKER

See attached as this should make it clear - the answer is in cell C111

recursive.xls

recursive.xls

Please see if the following works (not tested):

```
select max(avg_amt) from
(
select average(amount) avg_amt over (order by date range between current row and 11 following)
from table1
)
```

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

angelIII

when I change add_months(t.date,-12) to

add_months(t.date,-11)

it works - does that make sense?

Regards

when I change add_months(t.date,-12) to

add_months(t.date,-11)

it works - does that make sense?

Regards

indeed, -11 will take the 12 months correctly. sorry for the typo

current month (1) is 0, -11 is the 12th month... just to explain the -11 instead of -12

ASKER

Thanks for this - brilliant!

ASKER

Thanks