# Budget Control

{% code title="budget\_control" overflow="wrap" lineNumbers="true" %}

```plsql
 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;
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://work.bahrul.me/work/kyzn/database-management/table-view/budget-control.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.