Solved

using RECURSIVE WITH statement - using oracle 11g

Posted on 2012-12-28
14
434 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
  • 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 142

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
 

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 142

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 142

Expert Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now