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 (
    'Michael',
    'Richardson',
    'michael.r@email.com',
    '555-0101'
);

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

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

INSERT INTO venue (
    name,
    location,
    city,
    address,
    capacity,
    price_per_guest,
    type_id
)
VALUES (
    'Grand Vista',
    '123 Oak Street',
    'Springfield',
    '123 Oak Street',
    200,
    45.00,
    1
);

Query Execution Result

 wedding_id | organizer_name      | wedding_date | budgeted_amount
------------+---------------------+--------------+-----------------
 1          | Michael Richardson  | 2024-06-15   | 8500.00

Calculation Verification

• Venue Cost: fixed venue booking price
• Photographer Cost: calculated using hourly duration
• Band Cost: calculated using hourly duration
• Total Actual Cost: sum of all vendor costs
• Remaining Budget: planned budget minus total actual cost
• Variance Percentage: remaining budget percentage relative to total budget

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