6 Replies Latest reply on Oct 7, 2016 6:35 AM by philmodjunk

    I need a better solution than using Nth record

    morrismedia

      I have a table of financial records that contains thousands of records of account balances and the date of that balance for hundreds of clients. Each record represents an account balance for only the last day of each month.

       

      Example: MONTHLYBAL (table)

      PK: 101, Balance: 1,590, Date: 6/31/2016

      PK: 101, Balance: 1,620, Date: 7/31/2016

      PK: 101, Balance: 1,680, Date: 8/30/2016

      PK: 101, Balance: 1,896, Date: 9/31/2016

      PK: 102, Balance: 2,250, Date: 6/31/2016

      PK: 102, Balance: 2,490, Date: 7/31/2016

      PK: 102, Balance: 2,870, Date: 8/30/2016

      PK: 102, Balance: 2,944, Date: 9/31/2016

      (etc)

       

      Every quarter I need to run a report that will calculate the amount of change between the current end quarter month (i.e. 9/31/2016) and the previous quarter ending month (i.e. 6/31/2016). I need a calculation that will grab the account balance from the last quarter end month (skipping the other non quarter ending months) and subtract the amount of the previous quarter from the amount of the current quarter.

       

      I hope that makes sense. If not please ask...