Selection spec - Should I be using them pesky analytical functions?

[Horace]

Hi Guys,

Rushed off my feet as usual, large production environment supporting large complex local applications.

So, I gets an email with the following enquiry, and am hoping to look into it tomorrow evening.

But, I know you guys love these things, so I thought you might be so kind as to give me a head start.

I'm thinking analytical functions here, and I know juri & dapi understand these things. Enough waffle.....

We have an account_history table (simplified).

ACCOUNT_NUMBER NUMBER(10),
ACCOUNT_BALANCE NUMBER(5),
DATE_STAMP

---------------------------------------------------------------------

"What we need is to take those account numbers with at least three months worth of account history (the last three months worth) - So 3 rows with the same account_number.

We want to end up with a table containing an account number and the account_balance average for the last 3 months for that account number.

We don't want accounts with less than three months, and we don't want to sum up more than three months worth even if they have more account history than that."

---------------------------------------------------------------------

I'd greatly appreciate any input

Thanks.

[tamilselvan]

You can do it in ANALYTIC FUNCTION.
How ever, your requirement seems to be confusing.
Could you be more specific about your requirement? A sample report may help us.

Tamil

[DaPi]

A bit busy myself . . however . .

Don't think you need an AF here. Something like:

Select Account_Number, Avg(Account_Balance)
From my_table
Where Date_Stamp > sysdate-90 --< fix this accordingly ! ! !
Group By Account_Number
Having Count(Account_Number) = 3;