Lesson 4 of 13 · 24 min
Budget versus actual
By the end of this lesson you can compare budget with posted actuals by period, account and cost center, choosing the join that keeps budgets without actuals and actuals without budgets.
Tahlia's budgets table has one row per fiscal year, period number, account and cost center, with a positive amount in the account's natural direction: revenue it expects to earn, cost it expects to spend. Actuals live in journal lines, at a much finer grain (one row per line of every entry).
To compare them you must first bring actuals to the budget's grain, and sign them the same way the budget is signed:
- revenue:
credit − debit(positive when you earn) - expenses:
debit − credit(positive when you spend)
Only then do you join. Joining raw journal lines to budget rows would repeat each budget amount once per matching line, the same fan-out you met with returns, but now it inflates the budget.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons