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