Task
UC Davis Financial Services wants a simple reconciliation view from Aggie Enterprise export data. Write a SQL query to summarize posted financial activity by account for March 2024.
Requirements
- Return one row per
account_code.
- Include only rows where
status = 'Posted' and transaction_date is in March 2024.
- Calculate:
- total debit amount
- total credit amount
- net activity as
SUM(debit_amount) - SUM(credit_amount)
- Sort the result by
net_activity descending, then account_code ascending.
Table Definition
gl_transactions
| column | type | description |
|---|
| transaction_id | INT | Unique transaction ID |
| account_code | VARCHAR(20) | General ledger account code |
| account_name | VARCHAR(100) | Account description |
| transaction_date | DATE | Posting date |
| debit_amount | DECIMAL(12,2) | Debit amount for the transaction |
| credit_amount | DECIMAL(12,2) | Credit amount for the transaction |
| status | VARCHAR(20) | Transaction status |
| source_system | VARCHAR(50) | Source of the entry |
Sample Data
| transaction_id | account_code | account_name | transaction_date | debit_amount | credit_amount | status | source_system |
|---|
| 1008 | 500100 | Office Supplies Expense | 2024-03-20 | 75.00 | 0.00 | Posted | Aggie Enterprise |
| 1002 | 610200 | Travel Expense | 2024-03-05 | 300.00 | 0.00 | Posted | Aggie Enterprise |
| 1005 | 400100 | Tuition Revenue | 2024-03-12 | 0.00 | 1500.00 | Posted | Aggie Enterprise |
| 1001 | 500100 | Office Supplies Expense | 2024-03-02 | 120.50 | 0.00 | Posted | Aggie Enterprise |
| 1004 | 610200 | Travel Expense | 2024-03-10 | 0.00 | 50.00 | Posted | Aggie Enterprise |
Expected Output
| account_code | total_debits | total_credits | net_activity |
|---|
| 610200 | 300.00 | 50.00 | 250.00 |
| 500100 | 195.50 | 0.00 | 195.50 |
| 720300 | 0.00 | 0.00 | 0.00 |
| 400100 | 0.00 | 1500.00 | -1500.00 |
