Solved

using RECURSIVE WITH statement - using oracle 11g

Posted on 2012-12-28
14
436 Views
Last Modified: 2012-12-28
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
0
Comment
Question by:PHIL Sawyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 38726514
What's the point of using recursive function for your target output? You can just get the max of all the amount.
0
 
LVL 20

Expert Comment

by:flow01
ID: 38726534
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 ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726537
>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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PHIL Sawyer
ID: 38726538
Sorry - I meant the average for each time window - so, assume the average is 22 then 45
I then want
max(22,45,......................................)
Regards
0
 

Author Comment

by:PHIL Sawyer
ID: 38726560
flow001
No time windows without date
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726570
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') )
0
 

Author Comment

by:PHIL Sawyer
ID: 38726574
See attached as this should make it clear - the answer is in cell C111
recursive.xls
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 38726575
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

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38726579
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

0
 

Author Comment

by:PHIL Sawyer
ID: 38726615
angelIII
when I change add_months(t.date,-12) to
add_months(t.date,-11)
it works - does that make sense?
Regards
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726621
indeed, -11 will take the 12 months correctly. sorry for the typo
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726624
current month (1) is 0, -11 is the 12th month... just to explain the -11 instead of -12
0
 

Author Comment

by:PHIL Sawyer
ID: 38726643
Thanks for this - brilliant!
0
 

Author Closing Comment

by:PHIL Sawyer
ID: 38726645
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question