AnsweredAssumed Answered

I need a better solution than using Nth record

Question asked by morrismedia on Oct 6, 2016
Latest reply on Oct 7, 2016 by philmodjunk

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



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...