using RECURSIVE WITH statement - using oracle 11g

PHIL Sawyer
PHIL Sawyer used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Peter KwanAnalyst Programmer

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

Commented:
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 ?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Sorry - I meant the average for each time window - so, assume the average is 22 then 45
I then want
max(22,45,......................................)
Regards

Author

Commented:
flow001
No time windows without date
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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') )

Author

Commented:
See attached as this should make it clear - the answer is in cell C111
recursive.xls
Peter KwanAnalyst Programmer

Commented:
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
)

Open in new window

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
clear now....
select max( av )
  from ( 
    select t.date
            , (select average(x.value) from yourtable x where x.date >= add_months(t.date,-12) and x.date <= t.date ) av 
      from yourtable t
 ) 

Open in new window

Author

Commented:
angelIII
when I change add_months(t.date,-12) to
add_months(t.date,-11)
it works - does that make sense?
Regards
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
indeed, -11 will take the 12 months correctly. sorry for the typo
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
Thanks for this - brilliant!

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial