Hi,
I have two tables:
1: Account table with Account_Number and Account_Opening_Date columns.
2: Balances tables with Account_Number and Account_Balance columns.
The Balances tables are created for every month by the application, with the table name in the format BAL_PYYYYMM. So for this year there are six tables: BAL_P202101, BAL_P202102, BAL_P202103, BAL_P202104, BAL_P202105 and BAL_P202106.
I would like to get the balances of accounts based on their opening date from the correct Balances table using the statement:
SELECT A.Account_Number, A.Account_Opening_Date, B.Account_Balance FROM Account_Table A LEFT JOIN ( SELECT Account_Balance from BAL_P2021MM ) B ON A.Account_Number = B.Account_Number
such that, say, if the Account_Opening_Date is in the month of May, then the Account_Balance would be picked from the table BAL_P202105.
How would I write a statement that will dynamically set the Balances table name based on the account opening date month?