Analytical SQL Views

Description

This section contains SQL views used for reporting and analytical summaries.

1. vw_wedding_financial_summary

Description

Displays wedding budget and total booking expenses.

SQL Code

CREATE OR REPLACE VIEW vw_wedding_financial_summary AS
SELECT
    w.wedding_id,
    w.budget,
    calculate_wedding_total_cost(w.wedding_id) AS total_cost,
    w.budget - calculate_wedding_total_cost(w.wedding_id) AS remaining_budget
FROM wedding w;

2. vw_rsvp_overview

Description

Displays RSVP statistics for guests and events.

SQL Code

CREATE OR REPLACE VIEW vw_rsvp_overview AS
SELECT
    e.event_id,
    e.event_type,
    r.status,
    COUNT(*) AS total
FROM event_rsvp r
JOIN event e ON r.event_id = e.event_id
GROUP BY e.event_id, e.event_type, r.status;

3. vw_vendor_booking_overview

Description

Displays venue, photographer, and band bookings.

SQL Code

CREATE OR REPLACE VIEW vw_vendor_booking_overview AS
SELECT
    w.wedding_id,
    v.name AS venue_name,
    p.name AS photographer_name,
    b.band_name
FROM wedding w
LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
LEFT JOIN venue v ON vb.venue_id = v.venue_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;

4. vw_upcoming_weddings

Description

Displays upcoming weddings scheduled in the future.

SQL Code

CREATE OR REPLACE VIEW vw_upcoming_weddings AS
SELECT
    wedding_id,
    "date" AS wedding_date,
    budget,
    notes
FROM wedding
WHERE "date" >= CURRENT_DATE;