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!
Course Of The Month
8 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
using RECURSIVE WITH statement - using oracle 11g
Posted on 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
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
[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
What's the point of using recursive function for your target output? You can just get the max of all the amount.
Active 1 day ago
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
Active today
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
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') )
Active today
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
)
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
)
Active today
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
Featured Post
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
Background
In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC
Introduction
First, a brief summary of what a Database Gateway is. A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility. The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month8 days, 10 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.