using RECURSIVE WITH statement - using oracle 11g

PHIL Sawyer
PHIL Sawyer used Ask the Experts™
on
Hello
Another question similar to one posted earlier today - angelIII helped me out earlier but this one is slightly different and best explained by seeing the attached - see cell C108 as for the required answer.

I have a table with 2 columns date and amount - again see attached for required outcome.

Regards
recursive2.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
the idea is again the same...
you join the row with the row from a date 12 months ago (months_add.. -11)
select max(delta)
  from ( select ( t.value - p.value ) / p.value delta
            from yourtable t
            join yourtable p
              on p.date = months_add(t.date, -11)
   )

Open in new window

Author

Commented:
Hello
This one works if I change t.date, -11 to
t.date, -12

Many thanks again

Author

Commented:
Thanks
awking00Information Technology Specialist

Commented:
Not sure but I'm thinking that, in order to match your spreadsheet line for line the join should be on add_months(p.date,-1) = add_months(t.date,-12)

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