Budget Control

view name = 'budget_control'

[NEW] Query

 WITH fullmonths AS (
         SELECT to_char('2019-01-01'::date + ((n.n || ' months'::text)::interval), 'YYYY-MM'::text) AS month
           FROM generate_series(0, 12 * (2025 - 2019) + 11) n(n)
        ), transactionsummary AS (
         SELECT to_char(transaction.payment_date::timestamp with time zone, 'YYYY-MM'::text) AS month,
            transaction.branch,
            transaction.budget_category,
            transaction.coa_category,
            transaction.coa,
            transaction.transaction_type,
            transaction.pic,
            sum(transaction.amount_idr) AS budget_usage
           FROM transaction
          WHERE EXTRACT(year FROM transaction.payment_date) <= 2025::numeric
          GROUP BY (to_char(transaction.payment_date::timestamp with time zone, 'YYYY-MM'::text)), transaction.branch, transaction.budget_category, transaction.coa_category, transaction.coa, transaction.transaction_type, transaction.pic
        ), budgetsummary AS (
         SELECT budget.branch,
            budget.budget_category,
            budget.coa_category,
            budget.coa,
            fm.month,
            sum(
                CASE
                    WHEN fm.month = '2025-01'::text THEN budget."2025_Jan"
                    WHEN fm.month = '2025-02'::text THEN budget."2025_Feb"
                    WHEN fm.month = '2025-03'::text THEN budget."2025_Mar"
                    WHEN fm.month = '2025-04'::text THEN budget."2025_Apr"
                    WHEN fm.month = '2025-05'::text THEN budget."2025_May"
                    WHEN fm.month = '2025-06'::text THEN budget."2025_Jun"
                    WHEN fm.month = '2025-07'::text THEN budget."2025_Jul"
                    WHEN fm.month = '2025-08'::text THEN budget."2025_Aug"
                    WHEN fm.month = '2025-09'::text THEN budget."2025_Sep"
                    WHEN fm.month = '2025-10'::text THEN budget."2025_Oct"
                    WHEN fm.month = '2025-11'::text THEN budget."2025_Nov"
                    WHEN fm.month = '2025-12'::text THEN budget."2025_Dec"
                    ELSE 0::numeric
                END) AS budget
           FROM budget
             CROSS JOIN fullmonths fm
          GROUP BY budget.branch, budget.budget_category, budget.coa_category, budget.coa, fm.month
        ), combined AS (
         SELECT fm.month,
            COALESCE(ts.branch, bs.branch) AS branch,
            COALESCE(ts.budget_category, bs.budget_category) AS budget_category,
            COALESCE(ts.coa_category, bs.coa_category) AS coa_category,
            COALESCE(ts.coa, bs.coa) AS coa,
            COALESCE(ts.transaction_type, NULL::text) AS transaction_type,
            COALESCE(ts.pic, NULL::text) AS pic,
            COALESCE(ts.budget_usage, 0::numeric) AS budget_usage,
            COALESCE(bs.budget, 0::numeric) AS budget
           FROM fullmonths fm
             LEFT JOIN transactionsummary ts ON fm.month = ts.month
             FULL JOIN budgetsummary bs ON fm.month = bs.month AND (ts.branch = bs.branch OR ts.branch IS NULL) AND (ts.budget_category = bs.budget_category OR ts.budget_category IS NULL) AND (ts.coa_category = bs.coa_category OR ts.coa_category IS NULL) AND (ts.coa = bs.coa OR ts.coa IS NULL)
        )
 SELECT month,
    branch,
    budget_category,
    coa_category,
    coa,
    transaction_type,
    pic,
    budget_usage,
    budget,
    budget - budget_usage AS budget_remaining
   FROM combined
  ORDER BY month, branch, budget_category, coa_category, coa;