Changes between Initial Version and Version 1 of BudgetAnalysis

Timestamp:

05/13/26 21:18:08 (13 days ago)

Author:

193284

Comment:

--

BudgetAnalysis

@@ +1 @@
+= 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 ===
+
+{{{
+#!sql
+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 ===
+
+{{{
+#!sql
+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 ===
+
+{{{
+#!sql
+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