Changes between Initial Version and Version 1 of P6BudgetReport

Timestamp:

05/13/26 20:56:33 (13 days ago)

Author:

193284

Comment:

--

P6BudgetReport

@@ +1 @@
+= Budget vs Actual Expenditure Analysis =
+
+== Objective ==
+
+This report compares the planned wedding budget with the actual costs of booked services.
+
+It includes venue costs, photographer costs, and band costs. The report helps identify remaining budget, total spending, and budget variance.
+
+== SQL Query Implementation ==
+
+{{{
+#!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(DISTINCT 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(DISTINCT 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(DISTINCT 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
+
+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;
+}}}
+
+== Query Complexity Analysis ==
+
+* Join count: 7 tables
+* Aggregate functions: SUM, COALESCE, EXTRACT
+* Temporal calculation: booking duration is converted from time interval to hours
+* Grouping: results are grouped per wedding
+
+== Relational Algebra Expression ==
+
+{{{
+π wedding_id, organizer_name, wedding_date, budget, venue_cost, photographer_cost, band_cost, total_actual_cost
+(
+  γ wedding_id;
+    SUM(venue_booking.price),
+    SUM((photographer_booking.end_time - photographer_booking.start_time) * photographer.price_per_hour),
+    SUM((band_booking.end_time - band_booking.start_time) * band.price_per_hour)
+  (
+    Wedding ⟕ User ⟕ Venue_Booking ⟕ Photographer_Booking ⟕ Photographer ⟕ Band_Booking ⟕ Band
+  )
+)
+}}}
+
+== Stored Procedure ==
+
+{{{
+#!sql
+CREATE OR REPLACE PROCEDURE budget_variance_report(
+    IN p_wedding_id INT DEFAULT NULL
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_record RECORD;
+    v_venue_cost NUMERIC;
+    v_photographer_cost NUMERIC;
+    v_band_cost NUMERIC;
+    v_total_cost 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
+    );
+
+    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
+    LOOP
+        SELECT COALESCE(SUM(price), 0)
+        INTO v_venue_cost
+        FROM venue_booking
+        WHERE 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
+        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
+        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;
+
+        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_record.budget - v_total_cost
+        );
+    END LOOP;
+
+    RAISE NOTICE 'Budget variance report generated.';
+END;
+$$;
+}}}
+
+== Purpose ==
+
+This report supports financial planning by showing whether a wedding is within its planned budget or exceeding it.