From c5daf76196c0238d3286d8593fe0ba0f91bd2fba Mon Sep 17 00:00:00 2001 From: Jesse Luehrs Date: Mon, 26 Aug 2019 01:04:44 -0400 Subject: add some useful views --- data/schema.sql | 288 +++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 286 insertions(+), 2 deletions(-) diff --git a/data/schema.sql b/data/schema.sql index 59526ef..d8115a4 100644 --- a/data/schema.sql +++ b/data/schema.sql @@ -66,7 +66,7 @@ CREATE TYPE flag_color_t AS ENUM('red', 'orange', 'yellow', 'green', 'blue', 'pu CREATE TABLE transactions ( id text PRIMARY KEY, - transaction_date date NOT NULL, + date date NOT NULL, amount bigint NOT NULL, memo text, cleared cleared_t NOT NULL, @@ -105,7 +105,7 @@ CREATE TYPE frequency_t AS ENUM ( ); CREATE TABLE scheduled_transactions ( id text PRIMARY KEY, - date_next date NOT NULL, + date date NOT NULL, frequency frequency_t NOT NULL, amount bigint NOT NULL, memo text, @@ -129,3 +129,287 @@ CREATE TABLE scheduled_subtransactions ( CREATE TABLE ints ( i integer PRIMARY KEY ); + +DROP VIEW IF EXISTS denorm_transactions; +CREATE VIEW denorm_transactions AS ( + WITH + transactions_with_subtransactions AS ( + SELECT + transactions.id, + subtransactions.id AS subtransaction_id, + transactions.date, + coalesce(subtransactions.amount, transactions.amount) AS amount, + coalesce(subtransactions.memo, transactions.memo) AS memo, + transactions.cleared, + transactions.approved, + transactions.flag_color, + transactions.account_id, + coalesce(subtransactions.payee_id, transactions.payee_id) AS payee_id, + coalesce(subtransactions.category_id, transactions.category_id) AS category_id, + coalesce(subtransactions.transfer_account_id, transactions.transfer_account_id) AS transfer_account_id + FROM + transactions LEFT JOIN subtransactions ON ( + transactions.id = subtransactions.transaction_id + ) + ) + SELECT + id, + subtransaction_id, + date, + amount / 1000.0 as amount, + memo, + cleared, + approved, + flag_color, + accounts.name AS account, + payees.name AS payee, + category_groups.name AS category_group, + categories.name AS category, + transfer_accounts.name AS transfer_account + FROM + transactions_with_subtransactions LEFT JOIN accounts ON ( + transactions_with_subtransactions.account_id = accounts.id + ) LEFT JOIN payees ON ( + transactions_with_subtransactions.payee_id = payees.id + ) LEFT JOIN categories ON ( + transactions_with_subtransactions.category_id = categories.id + ) LEFT JOIN category_groups ON ( + categories.category_group_id = category_groups.id + ) LEFT JOIN accounts transfer_accounts ON ( + transactions_with_subtransactions.transfer_account_id = transfer_accounts.id + ) +); + +DROP VIEW IF EXISTS denorm_scheduled_transactions; +CREATE VIEW denorm_scheduled_transactions AS ( + WITH + scheduled_transactions_with_subtransactions AS ( + SELECT + scheduled_transactions.id, + scheduled_subtransactions.id AS scheduled_subtransaction_id, + scheduled_transactions.date, + scheduled_transactions.frequency, + coalesce(scheduled_subtransactions.amount, scheduled_transactions.amount) AS amount, + coalesce(scheduled_subtransactions.memo, scheduled_transactions.memo) AS memo, + scheduled_transactions.flag_color, + scheduled_transactions.account_id, + coalesce(scheduled_subtransactions.payee_id, scheduled_transactions.payee_id) AS payee_id, + coalesce(scheduled_subtransactions.category_id, scheduled_transactions.category_id) AS category_id, + coalesce(scheduled_subtransactions.transfer_account_id, scheduled_transactions.transfer_account_id) AS transfer_account_id, + FROM + scheduled_transactions LEFT JOIN scheduled_subtransactions ON ( + scheduled_transactions.id = scheduled_subtransactions.scheduled_transaction_id + ) + ) + SELECT + id, + subtransaction_id, + date, + frequency, + amount / 1000.0 as amount, + memo, + flag_color, + accounts.name AS account, + payees.name AS payee, + category_groups.name AS category_group, + categories.name AS category, + transfer_accounts.name AS transfer_account + FROM + scheduled_transactions_with_subtransactions LEFT JOIN accounts ON ( + scheduled_transactions_with_subtransactions.account_id = accounts.id + ) LEFT JOIN payees ON ( + scheduled_transactions_with_subtransactions.payee_id = payees.id + ) LEFT JOIN categories ON ( + scheduled_transactions_with_subtransactions.category_id = categories.id + ) LEFT JOIN category_groups ON ( + categories.category_group_id = category_groups.id + ) LEFT JOIN accounts transfer_accounts ON ( + scheduled_transactions_with_subtransactions.transfer_account_id = transfer_accounts.id + ) +); + +DROP VIEW IF EXISTS future_transactions; +CREATE VIEW future_transactions AS ( + WITH + daily AS ( + SELECT + 'daily'::frequency_t AS frequency, + (ints.i - 1) * interval '1 day' AS span + FROM + ints + WHERE + ints.i <= 750 + ), + weekly AS ( + SELECT + 'weekly'::frequency_t AS frequency, + (ints.i - 1) * interval '1 week' AS span + FROM + ints + WHERE + ints.i <= 120 + ), + every_other_week AS ( + SELECT + 'everyOtherWeek'::frequency_t AS frequency, + (ints.i - 1) * interval '2 weeks' AS span + FROM + ints + WHERE + ints.i <= 60 + ), + twice_a_month AS ( + SELECT + 'twiceAMonth'::frequency_t AS frequency, + make_interval(months => a.i, days => b.i) AS span + FROM + ints a CROSS JOIN ints b + WHERE + a.i <= 30 and (b.i = 0 or b.i = 15) + ), + every_four_weeks AS ( + SELECT + 'every4Weeks'::frequency_t AS frequency, + (ints.i - 1) * interval '4 weeks' AS span + FROM + ints + WHERE + ints.i <= 30 + ), + monthly AS ( + SELECT + 'monthly'::frequency_t AS frequency, + (ints.i - 1) * interval '1 month' AS span + FROM + ints + WHERE + ints.i <= 30 + ), + every_other_month AS ( + SELECT + 'everyOtherMonth'::frequency_t AS frequency, + (ints.i - 1) * interval '2 months' AS span + FROM + ints + WHERE + ints.i <= 15 + ), + every_three_months AS ( + SELECT + 'every3Months'::frequency_t AS frequency, + (ints.i - 1) * interval '3 months' AS span + FROM + ints + WHERE + ints.i <= 10 + ), + every_four_months AS ( + SELECT + 'every4Months'::frequency_t AS frequency, + (ints.i - 1) * interval '4 months' AS span + FROM + ints + WHERE + ints.i <= 10 + ), + twice_a_year AS ( + SELECT + 'twiceAYear'::frequency_t AS frequency, + (ints.i - 1) * interval '6 months' AS span + FROM + ints + WHERE + ints.i <= 5 + ), + yearly AS ( + SELECT + 'yearly'::frequency_t AS frequency, + (ints.i - 1) * interval '1 year' AS span + FROM + ints + WHERE + ints.i <= 5 + ), + every_other_year AS ( + SELECT + 'everyOtherYear'::frequency_t AS frequency, + (ints.i - 1) * interval '2 years' AS span + FROM + ints + WHERE + ints.i <= 5 + ), + repeated_transactions AS ( + SELECT + id, + scheduled_subtransaction_id, + CASE + WHEN frequency = 'never' THEN + date + WHEN frequency = 'daily' THEN + date + daily.span + WHEN frequency = 'weekly' THEN + date + weekly.span + WHEN frequency = 'everyOtherWeek' THEN + date + every_other_week.span + WHEN frequency = 'twiceAMonth' THEN + date + twice_a_month.span + WHEN frequency = 'every4Weeks' THEN + date + every_four_weeks.span + WHEN frequency = 'monthly' THEN + date + monthly.span + WHEN frequency = 'everyOtherMonth' THEN + date + every_other_month.span + WHEN frequency = 'every3Months' THEN + date + every_three_months.span + WHEN frequency = 'every4Months' THEN + date + every_four_months.span + WHEN frequency = 'twiceAYear' THEN + date + twice_a_year.span + WHEN frequency = 'yearly' THEN + date + yearly.span + WHEN frequency = 'everyOtherYear' THEN + date + every_other_year.span + ELSE + NULL + END AS date, + amount, + memo, + flag_color, + account, + payee, + category_group, + category, + transfer_account_id + FROM + denorm_scheduled_transactions + LEFT JOIN daily USING (frequency) + LEFT JOIN weekly USING (frequency) + LEFT JOIN every_other_week USING (frequency) + LEFT JOIN twice_a_month USING (frequency) + LEFT JOIN every_four_weeks USING (frequency) + LEFT JOIN monthly USING (frequency) + LEFT JOIN every_other_month USING (frequency) + LEFT JOIN every_three_months USING (frequency) + LEFT JOIN every_four_months USING (frequency) + LEFT JOIN twice_a_year USING (frequency) + LEFT JOIN yearly USING (frequency) + LEFT JOIN every_other_year USING (frequency) + ) + SELECT + id, + scheduled_subtransaction_id, + date + amount, + memo, + flag_color, + account, + payee, + category_group, + category, + transfer_account_id + FROM + repeated_transactions + WHERE + date <= CURRENT_DATE + interval '2 years' +); -- cgit v1.2.3