wiki:BudgetAnalysis

Context Navigation

Version 2 (modified by 193284, 13 days ago) ( diff )
--

Budget vs Actual Expenditure Analysis

1. Scenario Overview

This scenario performs financial analysis by comparing planned wedding budgets against actual vendor expenditures.

The report aggregates costs from:

venue bookings
photographer services
band entertainment

The analysis enables:

budget tracking
financial reconciliation
identification of budget overruns
expenditure monitoring
variance calculation

1.1 Objective

Perform comprehensive financial analysis comparing budgeted wedding costs against actual vendor expenditures.

This report aggregates costs from venue bookings, photographer services, and band entertainment across all bookings associated with each wedding.

The analysis enables identification of budget overruns, cost variances, and financial reconciliation at the wedding level.

1.2 SQL Query Implementation

SQL Code

SELECT
    w.wedding_id,
    u.first_name || ' ' || u.last_name AS organizer_name,
    w.date AS wedding_date,
    w.budget AS budgeted_amount,

    COALESCE(SUM(vb.price), 0) AS venue_cost,

    COALESCE(SUM(
        EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
        * p.price_per_hour
    ), 0) AS photographer_cost,

    COALESCE(SUM(
        EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
        * b.price_per_hour
    ), 0) AS band_cost,

    COALESCE(SUM(vb.price), 0)
    + COALESCE(SUM(
        EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
        * p.price_per_hour
    ), 0)
    + COALESCE(SUM(
        EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
        * b.price_per_hour
    ), 0) AS total_actual_cost,

    w.budget - (
        COALESCE(SUM(vb.price), 0)
        + COALESCE(SUM(
            EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
            * p.price_per_hour
        ), 0)
        + COALESCE(SUM(
            EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
            * b.price_per_hour
        ), 0)
    ) AS remaining_budget,

    ROUND((
        (
            w.budget - (
                COALESCE(SUM(vb.price), 0)
                + COALESCE(SUM(
                    EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
                    * p.price_per_hour
                ), 0)
                + COALESCE(SUM(
                    EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
                    * b.price_per_hour
                ), 0)
            )
        ) / w.budget
    ) * 100, 2) AS budget_variance_percent

FROM wedding w

LEFT JOIN "user" u
    ON w.user_id = u.user_id

LEFT JOIN venue_booking vb
    ON w.wedding_id = vb.wedding_id

LEFT JOIN photographer_booking pb
    ON w.wedding_id = pb.wedding_id

LEFT JOIN photographer p
    ON pb.photographer_id = p.photographer_id

LEFT JOIN band_booking bb
    ON w.wedding_id = bb.wedding_id

LEFT JOIN band b
    ON bb.band_id = b.band_id

GROUP BY
    w.wedding_id,
    u.first_name,
    u.last_name,
    w.date,
    w.budget

ORDER BY w.wedding_id;

1.3 Query Complexity Analysis

Join Count: 7 tables
Aggregate Functions: SUM(), COALESCE(), EXTRACT(), ROUND()
Grouping Columns: wedding_id, organizer name, wedding date, and budget
Temporal Calculation: EXTRACT(EPOCH FROM ...) converts time intervals into billable hours
Financial Metrics: total cost, remaining budget, and variance percentage

1.4 Relational Algebra Expression

π(w.wedding_id, u.fname, u.lname, w.date, w.budget,
  SUM(vb.price),
  SUM((pb.end - pb.start) * p.rate),
  SUM((bb.end - bb.start) * b.rate))

(
    γ(
        wedding_id,
        SUM(venue_cost),
        SUM(photo_cost),
        SUM(band_cost)
    )

    (
        ρ(
            vb.price → venue_cost,
            (pb.end - pb.start) * p.price_per_hour → photo_cost,
            (bb.end - bb.start) * b.price_per_hour → band_cost
        )

        (
            (((Wedding ⟕ User)
            ⟕ Venue_Booking)
            ⟕ Photographer_Booking)
            ⟕ Photographer)
            ⟕ Band_Booking)
            ⟕ Band
    )
)

Notation

π = Projection (SELECT clause)
γ = Grouping and aggregation (GROUP BY)
⟕ = Left outer join (LEFT JOIN)
ρ = Rename operation (AS)
× = Cartesian product

Interpretation

The expression chains seven relations through left outer joins to preserve all weddings regardless of booking status.

The grouping operation aggregates cost components by wedding_id, enabling dimensional financial analysis of expenditures.

1.5 PostgreSQL Stored Procedure

SQL Code

CREATE OR REPLACE PROCEDURE budget_variance_report(
    IN p_wedding_id INT DEFAULT NULL,
    IN p_start_date DATE DEFAULT '2020-01-01',
    IN p_end_date DATE DEFAULT '2099-12-31'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_record RECORD;
    v_venue_cost NUMERIC;
    v_photographer_cost NUMERIC;
    v_band_cost NUMERIC;
    v_total_cost NUMERIC;
    v_remaining NUMERIC;
    v_variance NUMERIC;

BEGIN

    CREATE TEMP TABLE budget_variance_results (
        wedding_id INTEGER,
        organizer_name VARCHAR,
        wedding_date DATE,
        budgeted_amount NUMERIC,
        venue_cost NUMERIC,
        photographer_cost NUMERIC,
        band_cost NUMERIC,
        total_actual_cost NUMERIC,
        remaining_budget NUMERIC,
        variance_percent NUMERIC
    );

    FOR v_record IN

        SELECT
            w.wedding_id,
            u.first_name,
            u.last_name,
            w.date,
            w.budget

        FROM wedding w

        LEFT JOIN "user" u
            ON w.user_id = u.user_id

        WHERE
            (p_wedding_id IS NULL OR w.wedding_id = p_wedding_id)
            AND w.date BETWEEN p_start_date AND p_end_date

    LOOP

        SELECT COALESCE(SUM(vb.price), 0)
        INTO v_venue_cost
        FROM venue_booking vb
        WHERE vb.wedding_id = v_record.wedding_id;

        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
            * p.price_per_hour
        ), 0)
        INTO v_photographer_cost

        FROM photographer_booking pb

        LEFT JOIN photographer p
            ON pb.photographer_id = p.photographer_id

        WHERE pb.wedding_id = v_record.wedding_id;

        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
            * b.price_per_hour
        ), 0)
        INTO v_band_cost

        FROM band_booking bb

        LEFT JOIN band b
            ON bb.band_id = b.band_id

        WHERE bb.wedding_id = v_record.wedding_id;

        v_total_cost := v_venue_cost + v_photographer_cost + v_band_cost;

        v_remaining := v_record.budget - v_total_cost;

        v_variance := ROUND(
            (v_remaining / v_record.budget) * 100,
            2
        );

        INSERT INTO budget_variance_results
        VALUES (
            v_record.wedding_id,
            v_record.first_name || ' ' || v_record.last_name,
            v_record.date,
            v_record.budget,
            v_venue_cost,
            v_photographer_cost,
            v_band_cost,
            v_total_cost,
            v_remaining,
            v_variance
        );

    END LOOP;

    RAISE NOTICE
        'Budget Variance Report Generated - % rows',
        (SELECT COUNT(*) FROM budget_variance_results);

END;
$$;

1.6 Procedure Characteristics

Input Parameters:
- wedding ID (optional)
- start date
- end date

Iteration Logic:
- cursor-based iteration through weddings

Financial Calculations:
- isolated cost calculations for each vendor category

Error Handling:
- RAISE NOTICE execution logging

Temporary Storage:
- results stored in temporary session-scoped table

1.7 Proof of Execution with Sample Data

Sample Data Insertion

INSERT INTO "user" (
    first_name,
    last_name,
    email,
    phone_number
)
VALUES (
    'Марко',
    'Стојановски',
    'marko.stojanovski@email.com',
    '+38970111222'
);

INSERT INTO wedding (
    date,
    budget,
    user_id
)
VALUES (
    '2024-06-15',
    8500.00,
    1
);

INSERT INTO venue_type(type_name)
VALUES ('Wedding Hall');

INSERT INTO venue (
    name,
    location,
    city,
    address,
    capacity,
    price_per_guest,
    type_id
)
VALUES (
    'Golden Palace',
    'Centar',
    'Skopje',
    'Ilindenska 15',
    200,
    45.00,
    1
);

INSERT INTO venue_booking (
    date,
    start_time,
    end_time,
    status,
    price,
    venue_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '17:00:00',
    '23:00:00',
    'CONFIRMED',
    3600.00,
    1,
    1
);

INSERT INTO photographer (
    name,
    email,
    phone_number,
    price_per_hour
)
VALUES (
    'Foto Studio Aurora',
    'aurora@studio.mk',
    '+38970222333',
    150.00
);

INSERT INTO photographer_booking (
    date,
    start_time,
    end_time,
    status,
    photographer_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '16:00:00',
    '22:00:00',
    'CONFIRMED',
    1,
    1
);

INSERT INTO band (
    band_name,
    genre,
    equipment,
    phone_number,
    price_per_hour
)
VALUES (
    'Balkan Harmony',
    'Pop/Folk',
    'Full sound system',
    '+38970333444',
    200.00
);

INSERT INTO band_booking (
    date,
    start_time,
    end_time,
    status,
    band_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '17:30:00',
    '23:00:00',
    'CONFIRMED',
    1,
    1
);

Query Execution Result

 wedding_id | organizer_name   | wedding_date | budgeted_amount | venue_cost | photographer_cost | band_cost | total_actual_cost | remaining_budget | budget_variance_percent
------------+------------------+--------------+-----------------+------------+-------------------+-----------+-------------------+------------------+-------------------------
 1          | Марко Стојановски | 2024-06-15   | 8500.00         | 3600.00    | 900.00            | 1100.00   | 5600.00           | 2900.00          | 34.12

Calculation Verification

Venue Cost: 3600.00
Photographer Cost: 6 hours × 150.00 = 900.00
Band Cost: 5.5 hours × 200.00 = 1100.00
Total Actual Cost: 3600.00 + 900.00 + 1100.00 = 5600.00
Remaining Budget: 8500.00 - 5600.00 = 2900.00
Variance Percentage: (2900.00 / 8500.00) × 100 = 34.12%

Summary

This scenario demonstrates advanced financial analysis using:

multi-table joins
aggregate calculations
temporal SQL calculations
stored procedures
relational algebra representation
reusable analytical reporting logic

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text