diff --git a/schema.sql b/schema.sql
index d3ad426..3879034 100644
--- a/schema.sql
+++ b/schema.sql
@@ -6,3 +6,71 @@ CREATE TABLE entries (
memo TEXT NOT NULL
);
+
+ CREATE VIEW v_projected_balance AS
+ SELECT
+ (
+ (SELECT COALESCE(SUM(amount), 0)
+ FROM entries
+ WHERE account = 'CURRENT')
+ +
+ (
+ (SELECT average_amount
+ FROM v_budget_3m_average)
+ -
+ (SELECT total_amount
+ FROM v_current_budget_spend)
+ )
+ ) AS projected_balance
+ /* v_projected_balance(projected_balance) */;
+
+
+ CREATE VIEW v_budget_3m_average AS
+ WITH date_range AS (
+ SELECT
+ date(strftime('%Y-%m-01', 'now'), '-3 months') AS start_date,
+ date(strftime('%Y-%m-01', 'now'), '-1 day') AS end_date
+ )
+ SELECT
+ ROUND(COALESCE(SUM(amount), 0) / 3.0, 2) AS average_amount
+ FROM entries
+ WHERE account = 'CURRENT'
+ AND category IN (
+ 'CREDIT CARD', 'ENERGY', 'FUEL', 'HOME',
+ 'MOTOR', 'PHONE', 'RENT', 'SHOPPING', 'WATER'
+ )
+ AND date >= (SELECT start_date FROM date_range)
+ AND date <= (SELECT end_date FROM date_range)
+ /* v_budget_3m_average(average_amount) */;
+
+
+ CREATE VIEW v_current_budget_spend AS
+ WITH period AS (
+ SELECT
+ CASE
+ WHEN CAST(strftime('%d', 'now') AS INTEGER) < 15
+ THEN date('now', 'start of month', '-1 month', '+14 days')
+ ELSE
+ date('now', 'start of month', '+14 days')
+ END AS period_start,
+ CASE
+ WHEN CAST(strftime('%d', 'now') AS INTEGER) < 15
+ THEN date('now', 'start of month', '+13 days')
+ ELSE
+ date('now', 'start of month', '+1 month', '+13 days')
+ END AS period_end
+ )
+ SELECT
+ p.period_start,
+ p.period_end,
+ COALESCE(SUM(e.amount), 0) AS total_amount
+ FROM period p
+ LEFT JOIN entries e
+ ON e.date BETWEEN p.period_start AND p.period_end
+ AND e.account = 'CURRENT'
+ AND e.category IN (
+ 'CREDIT CARD', 'ENERGY', 'FUEL', 'HOME',
+ 'MOTOR', 'PHONE', 'RENT', 'SHOPPING', 'WATER'
+ )
+ /* v_current_budget_spend(period_start,period_end,total_amount) */;
+