Selection spec - Should I be using them pesky analytical functions?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

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

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width