Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 3 (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

Venue Capacity Utilization Analysis

2. Scenario Overview

This scenario analyzes venue occupancy and attendance utilization across weddings.

The analysis combines:

venue capacity
confirmed attendance
actual attendance
booking information
occupancy percentages

The report enables:

occupancy monitoring
capacity validation
venue efficiency analysis
utilization categorization
operational planning

2.1 Objective

Analyze the relationship between confirmed guest attendance and venue capacity constraints.

This report determines the occupancy rate, identifies capacity violations, and provides venue utilization metrics across weddings.

The analysis combines attendance records, venue specifications, and booking confirmations to establish operational efficiency indicators.

2.2 SQL Query Implementation

SQL Code

SELECT
    v.venue_id,
    v.name AS venue_name,
    v.capacity AS venue_capacity,

    w.wedding_id,

    u.first_name || ' ' || u.last_name
        AS organizer_name,

    w.date AS wedding_date,

    COUNT(DISTINCT a.guest_id)
        AS confirmed_attendees,

    COUNT(DISTINCT CASE
        WHEN a.status = 'ATTENDED'
        THEN a.guest_id
    END) AS actual_attendance,

    v.capacity - COUNT(DISTINCT a.guest_id)
        AS available_seats,

    ROUND(
        (
            CAST(COUNT(DISTINCT a.guest_id) AS NUMERIC)
            / v.capacity
        ) * 100,
        2
    ) AS occupancy_rate_percent,

    CASE
        WHEN COUNT(DISTINCT a.guest_id) > v.capacity
            THEN 'EXCEEDED'

        WHEN COUNT(DISTINCT a.guest_id)
            >= (v.capacity * 0.9)
            THEN 'HIGH'

        WHEN COUNT(DISTINCT a.guest_id)
            >= (v.capacity * 0.6)
            THEN 'MODERATE'

        ELSE 'LOW'
    END AS utilization_category,

    vb.status AS booking_status,
    vb.date AS booking_date

FROM venue v

INNER JOIN venue_booking vb
    ON v.venue_id = vb.venue_id

INNER JOIN wedding w
    ON vb.wedding_id = w.wedding_id

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

LEFT JOIN event e
    ON w.wedding_id = e.wedding_id

LEFT JOIN attendance a
    ON e.event_id = a.event_id
    AND a.status IN ('ATTENDED', 'CONFIRMED')

GROUP BY
    v.venue_id,
    v.name,
    v.capacity,
    w.wedding_id,
    u.first_name,
    u.last_name,
    w.date,
    vb.status,
    vb.date

HAVING COUNT(DISTINCT a.guest_id) > 0

ORDER BY
    v.venue_id,
    w.wedding_id;

2.3 Query Complexity Analysis

Join Count: 6 tables
Join Types:
- INNER JOIN
- LEFT JOIN

Aggregate Functions:
- COUNT(DISTINCT ...)
- ROUND()
- CASE

Filtering:
- HAVING clause after aggregation

Operational Metrics:
- occupancy rate
- available seats
- utilization category

2.4 Relational Algebra Expression

π(
    v.venue_id,
    v.name,
    v.capacity,
    w.wedding_id,
    u.fname,
    u.lname,
    w.date,
    COUNT(a.guest_id)
)

(
    σ(COUNT(guest_id) > 0)

    (
        γ(
            venue_id,
            wedding_id,
            COUNT(DISTINCT a.guest_id)
        )

        (
            (((Venue ⟕ Venue_Booking)
            ⟕ Wedding)
            ⟕ User)
            ⟕ Event)
            ⟕ Attendance
        )
)

Notation

π = Projection
σ = Selection
γ = Grouping and aggregation
⟕ = Join operation

Interpretation

The expression combines venue, wedding, event, and attendance relations to calculate occupancy metrics and venue utilization statistics.

2.5 PostgreSQL Stored Procedure

SQL Code

CREATE OR REPLACE PROCEDURE venue_capacity_utilization_report(
    IN p_venue_id INT DEFAULT NULL,
    IN p_min_occupancy_percent NUMERIC DEFAULT 0,
    IN p_max_occupancy_percent NUMERIC DEFAULT 100
)
LANGUAGE plpgsql
AS $$

DECLARE

    v_record RECORD;

    v_confirmed_count INTEGER;
    v_actual_count INTEGER;

    v_occupancy_rate NUMERIC;

    v_utilization_category VARCHAR;

    v_capacity INTEGER;

BEGIN

    CREATE TEMP TABLE capacity_utilization_results (
        venue_id INTEGER,
        venue_name VARCHAR,
        venue_capacity INTEGER,
        wedding_id INTEGER,
        organizer_name VARCHAR,
        wedding_date DATE,
        confirmed_attendees INTEGER,
        actual_attendance INTEGER,
        available_seats INTEGER,
        occupancy_rate_percent NUMERIC,
        utilization_category VARCHAR,
        booking_status VARCHAR,
        booking_date DATE
    );

    FOR v_record IN

        SELECT DISTINCT
            v.venue_id,
            v.name,
            v.capacity,
            w.wedding_id,
            u.first_name,
            u.last_name,
            w.date,
            vb.status,
            vb.date

        FROM venue v

        INNER JOIN venue_booking vb
            ON v.venue_id = vb.venue_id

        INNER JOIN wedding w
            ON vb.wedding_id = w.wedding_id

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

        WHERE
            (p_venue_id IS NULL
            OR v.venue_id = p_venue_id)

    LOOP

        SELECT COUNT(DISTINCT a.guest_id)
        INTO v_confirmed_count

        FROM event e

        LEFT JOIN attendance a
            ON e.event_id = a.event_id
            AND a.status = 'CONFIRMED'

        WHERE e.wedding_id = v_record.wedding_id;

        SELECT COUNT(DISTINCT a.guest_id)
        INTO v_actual_count

        FROM event e

        LEFT JOIN attendance a
            ON e.event_id = a.event_id
            AND a.status = 'ATTENDED'

        WHERE e.wedding_id = v_record.wedding_id;

        v_confirmed_count :=
            COALESCE(v_confirmed_count, 0);

        v_actual_count :=
            COALESCE(v_actual_count, 0);

        v_capacity := v_record.capacity;

        IF v_capacity > 0 THEN

            v_occupancy_rate := ROUND(
                (
                    CAST(v_confirmed_count AS NUMERIC)
                    / v_capacity
                ) * 100,
                2
            );

        ELSE

            v_occupancy_rate := 0;

        END IF;

        IF v_confirmed_count > v_capacity THEN

            v_utilization_category := 'EXCEEDED';

        ELSIF v_occupancy_rate >= 90 THEN

            v_utilization_category := 'HIGH';

        ELSIF v_occupancy_rate >= 60 THEN

            v_utilization_category := 'MODERATE';

        ELSE

            v_utilization_category := 'LOW';

        END IF;

        IF v_occupancy_rate BETWEEN
            p_min_occupancy_percent
            AND p_max_occupancy_percent
        THEN

            INSERT INTO capacity_utilization_results
            VALUES (
                v_record.venue_id,
                v_record.name,
                v_record.capacity,
                v_record.wedding_id,
                v_record.first_name || ' ' || v_record.last_name,
                v_record.date,
                v_confirmed_count,
                v_actual_count,
                v_capacity - v_confirmed_count,
                v_occupancy_rate,
                v_utilization_category,
                v_record.status,
                v_record.date
            );

        END IF;

    END LOOP;

    RAISE NOTICE
        'Venue Capacity Utilization Report Generated - % rows processed',
        (
            SELECT COUNT(*)
            FROM capacity_utilization_results
        );

END;
$$;

2.6 Procedure Characteristics

Input Parameters:
- venue ID
- minimum occupancy percentage
- maximum occupancy percentage

Attendance Analysis:
- confirmed attendance
- actual attendance

Categorization:
- LOW
- MODERATE
- HIGH
- EXCEEDED

NULL Safety:
- COALESCE() handling

Filtering:
- occupancy percentage range filtering

2.7 Proof of Execution with Sample Data

Sample Data Insertion

INSERT INTO "user" (
    first_name,
    last_name,
    email,
    phone_number
)
VALUES (
    'Александар',
    'Стојановски',
    'aleksandar@email.com',
    '070-111-222'
);

INSERT INTO wedding (
    date,
    budget,
    user_id
)
VALUES (
    '2024-07-20',
    12000.00,
    2
);

INSERT INTO venue (
    name,
    location,
    city,
    address,
    capacity,
    price_per_guest,
    type_id
)
VALUES (
    'Golden Palace',
    'Центар',
    'Скопје',
    'Булевар Македонија 15',
    150,
    55.00,
    1
);

Query Execution Result

 venue_id | venue_name    | capacity | wedding_id
----------+---------------+----------+------------
 2        | Golden Palace | 150      | 2

Calculation Verification

Confirmed Attendees:
- guests with CONFIRMED status

Actual Attendance:
- guests with ATTENDED status

Available Seats:
- venue capacity minus confirmed attendees

Occupancy Rate:
- confirmed attendees percentage relative to venue capacity

Utilization Category:
- LOW, MODERATE, HIGH, or EXCEEDED

Summary

This scenario demonstrates:

venue occupancy analysis
attendance tracking
utilization categorization
operational reporting
aggregate SQL calculations
stored procedure reporting

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text