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.
account_code.status = 'Posted' and transaction_date is in March 2024.SUM(debit_amount) - SUM(credit_amount)net_activity descending, then account_code ascending.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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| account_code | VARCHAR(20) | General ledger account code |
| account_name | VARCHAR(100) | General ledger account description |
| transaction_date | DATE | Date the transaction was posted or recorded |
| debit_amount | DECIMAL(12,2) | Debit amount on the transaction |
| credit_amount | DECIMAL(12,2) | Credit amount on the transaction |
| status | VARCHAR(20) | Transaction status such as Posted or Pending |
| source_system | VARCHAR(50) | System where the transaction originated |
| 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 |
| 1010 | 500100 | Office Supplies Expense | 2024-03-25 | 40.00 | 0.00 | Pending | Aggie Enterprise |
| 1007 | 400100 | Tuition Revenue | 2024-02-28 | 0.00 | 200.00 | Posted | Aggie Enterprise |
| 1003 | 610200 | Travel Expense | 2024-03-07 | 25.00 | Pending | Aggie Enterprise | |
| 1009 | 720300 | Equipment Expense | 2024-03-22 | Posted | Aggie Enterprise | ||
| 1006 | 400100 | Tuition Revenue | 2024-04-01 | 0.00 | 300.00 | Posted | Aggie Enterprise |
| 1 | 630100 | Facilities Expense | 2024-03-30 | 343 | 660 | In Review | Bookstore Feed |
| 2 | 810200 | Scholarship Expense | 2024-03-16 | 173 | 883 | Cancelled | Manual Journal |
| 3 | 710200 | Accounts Receivable | 2024-03-09 | 133 | 1501 | Released | Department Spreadsheet |
| 4 | 110000 | Scholarship Expense | 2024-02-26 | 102 | 975 | Voided | Procure-to-Pay |
| 5 | 210300 | Accounts Payable | 2024-03-25 | -21 | 1405 | Approved | Recharge System |
| 6 | 400100 | Auxiliary Revenue | 2024-03-19 | 229 | 837 | Preliminary | Foundation Feed |
| 7 | 100100 | Utilities Expense | 2024-03-07 | 87 | null | Validated | Payroll Interface |
| 8 | 710200 | Accrued Liabilities | 2024-03-31 | 261 | 769 | Posted | null |
| 9 | 710200 | Accounts Receivable | 2024-04-02 | 260 | -88 | Reversed | UCPath |
| 10 | 600100 | Travel Expense | 2024-03-04 | -37 | 1167 | Cancelled | null |
| 11 | 120500 | Service Fee Revenue | 2024-04-01 | 23 | 1338 | Adjusted | CashNet |
| 12 | 730400 | Professional Services Expense | 2024-02-27 | 100 | 780 | On Hold | Health Feed |
| 13 | 400100 | Office Supplies Expense | 2024-03-02 | 16 | 1141 | Draft | Foundation Feed |
| 14 | 110000 | Training Expense | 2024-03-26 | 355 | 974 | Voided | Kuali Research |
| 15 | 620500 | Accrued Liabilities | 2024-03-10 | 277 | 1365 | Voided | Recharge System |
| 16 | 400200 | Prepaid Expenses | 2024-03-06 | 19 | 1785 | Processed | Sponsored Projects |
| 17 | 610200 | Professional Services Expense | 2024-02-27 | -16 | 790 | Approved | Central Finance Upload |
| 18 | 620500 | Lab Supplies Expense | 2024-03-22 | 157 | 1231 | Voided | Manual Journal |
| 19 | 700100 | Transfer Out | 2024-04-01 | null | 966 | On Hold | CashNet |
| account_code | total_debits | total_credits | net_activity |
|---|---|---|---|
| 610200 | 300.00 | 50.00 | 250.00 |
| 500100 | 195.50 | 0.00 | 195.50 |
| 720300 | 0 | 0 | 0 |
| 710200 | 261.00 | 769.00 | -508.00 |
| 400100 | 0.00 | 1500.00 | -1500.00 |
