OraFAQ Forum: SQL & PL/SQL » months bettween

Home » SQL & PL/SQL » SQL & PL/SQL » months bettween

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

months bettween [message #22114]

Tue, 24 September 2002 06:14

karen
Messages: 19
Registered: January 2002

Junior Member

I'm calculating standard deviation for funds based upon a performance table which has monthly cumulative returns and keyed by fund_id, and as_of_d. However, before I run the standard deviation calculation I need to first check that based on whatever month I'm in I have 36 months of previous returns (36 entries). I tried this
select fund_id, as_of_d, min(as_of_d)
from
t_iwf3_fund_3yr
where
fund_id = 6022
group by fund_id
having months_between(as_of_d, min(as_of_d)) > 2

but I'm getting a "group function not allowed here" returned. When I add as_of_d to the group by statement it gets the minimum date for that row. Any help would be greatly appreciated. I'm tearing my hair out here!

Report message to a moderator

Re: months between [message #22121 is a reply to message #22114]

Tue, 24 September 2002 09:29

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Karen, maybe this will give you some ideas. This query will list every row that has at least 36 months of previous history (assuming an entry is made every month). You can add to the WHERE clause to filter to the current month, etc.:

select *
  from t t1
 where exists (select null
                 from t t2
                where t2.fund_id = t1.fund_id
                  and t2.as_of_date <= months_between(t1.as_of_date, -36));

Report message to a moderator