Task
You are asked to build a monthly budget versus actuals report for departments using data similar to what might be exported from UNT financial reporting workflows. Write a PostgreSQL query that returns one row per department per month for Q1 2024, including the monthly budget, monthly actuals, variance amount, variance percentage, and a year-to-date cumulative actuals figure. Departments with a budget but no posted transactions should still appear, and only posted transactions should count toward actuals.
Schema
departments
| column | type | description |
|---|
| department_id | INT | Department identifier |
| department_name | VARCHAR(100) | Department name |
| division_name | VARCHAR(100) | Division name |
| | |
monthly_budgets
| column | type | description |
|---|
| budget_id | INT | Budget row identifier |
| department_id | INT | Department tied to the budget |
| budget_month | DATE | First day of the budget month |
| budget_amount | DECIMAL(12,2) | Approved monthly budget |
| | |
gl_transactions
| column | type | description |
|---|
| transaction_id | INT | Transaction identifier |
| department_id | INT | Department charged |
| transaction_date | DATE | Posting date |
| amount | DECIMAL(12,2) | Expense amount |
| status | VARCHAR(20) | Transaction status |
| account_code | VARCHAR(20) | GL account |
Sample data
| department_id | department_name | division_name |
|---|
| 30 | Facilities | Operations |
| 10 | Biology | Academic Affairs |
| 20 | Chemistry | Academic Affairs |
| budget_id | department_id | budget_month | budget_amount |
|---|
| 1 | 10 | 2024-01-01 | 10000.00 |
| 4 | 20 | 2024-01-01 | 8000.00 |
| 7 | 30 | 2024-01-01 | 5000.00 |
| transaction_id | department_id | transaction_date | amount | status | account_code |
|---|
| 101 | 10 | 2024-01-15 | 2500.00 | posted | 6100 |
| 104 | 10 | 2024-02-15 | 500.00 | pending | 6200 |
| 107 | 20 | 2024-02-10 | -200.00 | posted | 6100 |
Expected output
| department_name | budget_month | budget_amount | actual_amount | variance_amount | variance_pct | ytd_actual_amount |
|---|
| Biology | 2024-01 | 10000.00 | 4000.00 | 6000.00 | 60.00 | 4000.00 |
| Biology | 2024-02 | 12000.00 | 7000.00 | 5000.00 | 41.67 | 11000.00 |
| Biology | 2024-03 | 11000.00 | 0.00 | 11000.00 | 100.00 | 11000.00 |
