Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: How do I select the most recent account balance per account

    I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:

    Table: CBALANCE
    =========================
    ACCOUNTNO
    BDATE
    AMOUNT

    What works: This gives me the most recent account, but I cannot get the amount
    =========================
    SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
    FROM CBALANCEMIB
    GROUP BY CBALANCEMIB.ACCOUNTNO;

    What I want to work: This returns results which should not be in the result set (older balances)
    ==========================
    SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
    FROM CBALANCEMIB
    GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;

    Am I trying to do something too complicated for the query language?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As answered elsewhere you posted this:

    You'd have to join your first query back against the original table on those 2 fields in a new query. I have to mention that saving the balance isn't normally a good idea.
    Paul

  3. #3
    Join Date
    Nov 2007
    Posts
    3

    I may have tried that

    I may have tried that. If I recall, I ended up with the same result as the second query in my post: older records that should not be there.

    I don't have access to the DB where I am now, so I'll let you know the result when I try it.

    As for the DB design, I'm innocent In the words of Shaggy: "It wasn't me"
    Last edited by nder; 11-06-07 at 23:13.

  4. #4
    Join Date
    Nov 2007
    Posts
    3
    Thanks for the help. Got it working with some additional fleshing out of the matter.

    For reference, here is my final solution.

    Pulled the relevant records and account information (PULLACCOUNTS)
    Code:
    SELECT Max(CBALANCEMIB.BDATE) AS MaxOfBDATE, CBALANCEMIB.ACCOUNTNO, ACCOUNTSMIB.ACNAME
    FROM CBALANCEMIB LEFT JOIN ACCOUNTSMIB ON ACCOUNTSMIB.ACCOUNTNO = CBALANCEMIB.ACCOUNTNO
    GROUP BY CBALANCEMIB.ACCOUNTNO, ACCOUNTSMIB.ACNAME;
    Then combined with the balances (COMBINEPULLACCOUNTS)
    Code:
    SELECT PULLACCOUNTS.MAXOFBDATE, PULLACCOUNTS.ACCOUNTNO, CBALANCEMIB.ACCOUNTNO, PULLACCOUNTS.ACNAME, CBALANCEMIB.BDATE, CBALANCEMIB.AMOUNT
    FROM CBALANCEMIB, PULLACCOUNTS;
    Finally weeded out the unnecessary records by combining with the original accounts pulled
    Code:
    SELECT COMBINEPULLACCOUNT.CBALANCEMIB.ACCOUNTNO, COMBINEPULLACCOUNT.ACNAME, COMBINEPULLACCOUNT.BDATE, COMBINEPULLACCOUNT.AMOUNT
    FROM COMBINEPULLACCOUNT
    WHERE COMBINEPULLACCOUNT.CBALANCEMIB.ACCOUNTNO = PULLACCOUNTS.ACCOUNTNO AND COMBINEPULLACCOUNT.BDATE=MAXOFBDATE
    Sorry about the readability problems of all caps. I didn't do the original DB and the owners seem to favour all caps so I just stuck to their coding standard

Posting Permissions

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