Calendar Report Examples

Production-ready SQL reports for facility management, event coordination, and ministry planning.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Calendar module live in the planning_center schema. Always prefix table names with planning_center. in every query. ✅ CORRECT: SELECT * FROM planning_center.calendar_events ❌ INCORRECT: SELECT * FROM calendar_events

Row Level Security (RLS)

Row Level Security automatically handles:

tenant_organization_id – restricts results to your organization
system_status – returns active records by default

Do not add these filters manually—RLS already enforces them and redundant predicates can hide data or slow performance:

❌ WHERE tenant_organization_id = 1
❌ WHERE system_status = 'active'

Keep your focus on scheduling windows, resource capacity, and approval states while trusting RLS for tenancy and status.

Executive Dashboard Reports
Facility Management Reports
Event Coordination Reports
Resource Utilization Reports
Conflict and Compliance Reports
Ministry Planning Reports

Executive Dashboard Reports

Weekly Ministry Overview

A comprehensive snapshot for leadership meetings:

-- Executive Weekly Calendar Summary
WITH this_week AS (
    SELECT 
        DATE_TRUNC('week', CURRENT_DATE) as week_start,
        DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '6 days' as week_end
),
event_summary AS (
    SELECT 
        COUNT(DISTINCT e.event_id) as total_events,
        COUNT(DISTINCT ei.event_instance_id) as total_instances,
        COUNT(DISTINCT CASE WHEN e.approval_status = 'A' THEN e.event_id END) as approved_events,
        COUNT(DISTINCT CASE WHEN e.approval_status = 'P' THEN e.event_id END) as pending_events,
        COUNT(DISTINCT CASE WHEN ei.all_day_event = true THEN ei.event_instance_id END) as all_day_events,
        COUNT(DISTINCT DATE(ei.starts_at)) as days_with_events
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    CROSS JOIN this_week tw
    WHERE ei.starts_at >= tw.week_start
        AND ei.starts_at <= tw.week_end
),
resource_summary AS (
    SELECT 
        COUNT(DISTINCT rb.resource_id) as resources_booked,
        COUNT(DISTINCT rb.resource_booking_id) as total_bookings,
        SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as total_hours_booked
    FROM planning_center.calendar_resource_bookings rb
    CROSS JOIN this_week tw
    WHERE rb.starts_at >= tw.week_start
        AND rb.starts_at <= tw.week_end
),
top_events AS (
    SELECT STRING_AGG(
        e.name || ' (' || TO_CHAR(ei.starts_at, 'Dy HH12:MI AM') || ')',
        ', ' ORDER BY ei.starts_at
        LIMIT 5
    ) as featured_events
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    CROSS JOIN this_week tw
    WHERE ei.starts_at >= tw.week_start
        AND ei.starts_at <= tw.week_end
        AND e.featured = true
)
SELECT 
    TO_CHAR(tw.week_start, 'Mon DD') || ' - ' || TO_CHAR(tw.week_end, 'Mon DD, YYYY') as week_range,
    es.total_events,
    es.total_instances,
    es.approved_events,
    es.pending_events,
    es.days_with_events,
    rs.resources_booked,
    rs.total_bookings as resource_bookings,
    ROUND(rs.total_hours_booked, 1) as facility_hours_used,
    te.featured_events
FROM this_week tw
CROSS JOIN event_summary es
CROSS JOIN resource_summary rs
CROSS JOIN top_events te;

Monthly Activity Metrics

-- Monthly Calendar Activity Dashboard
WITH monthly_metrics AS (
    SELECT 
        DATE_TRUNC('month', ei.starts_at) as month,
        COUNT(DISTINCT e.event_id) as unique_events,
        COUNT(DISTINCT ei.event_instance_id) as total_occurrences,
        COUNT(DISTINCT DATE(ei.starts_at)) as active_days,
        COUNT(DISTINCT ei.location) as unique_locations,
        COUNT(DISTINCT CASE WHEN e.visible_in_church_center THEN e.event_id END) as public_events,
        SUM(CASE WHEN ei.all_day_event THEN 1 ELSE 0 END) as all_day_count
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE ei.starts_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
    GROUP BY DATE_TRUNC('month', ei.starts_at)
),
with_comparisons AS (
    SELECT 
        TO_CHAR(month, 'Month YYYY') as month_year,
        unique_events,
        total_occurrences,
        active_days,
        unique_locations,
        public_events,
        LAG(total_occurrences, 1) OVER (ORDER BY month) as prev_month_occurrences,
        LAG(total_occurrences, 12) OVER (ORDER BY month) as year_ago_occurrences
    FROM monthly_metrics
)
SELECT 
    month_year,
    unique_events,
    total_occurrences,
    active_days,
    ROUND(total_occurrences::numeric / NULLIF(active_days, 0), 1) as avg_events_per_active_day,
    unique_locations,
    public_events,
    ROUND(((total_occurrences - prev_month_occurrences) * 100.0 / NULLIF(prev_month_occurrences, 0)), 1) as month_over_month_pct,
    ROUND(((total_occurrences - year_ago_occurrences) * 100.0 / NULLIF(year_ago_occurrences, 0)), 1) as year_over_year_pct
FROM with_comparisons
WHERE month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
ORDER BY month DESC;

Facility Management Reports

Room Utilization Report

-- Comprehensive Room Utilization Analysis
WITH room_bookings AS (
    SELECT 
        r.resource_id,
        r.name as room_name,
        r.home_location,
        r.quantity as capacity,
        DATE_TRUNC('week', rb.starts_at) as week,
        COUNT(DISTINCT rb.resource_booking_id) as bookings,
        SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as hours_used,
        COUNT(DISTINCT DATE(rb.starts_at)) as days_used,
        COUNT(DISTINCT ei.event_id) as unique_events
    FROM planning_center.calendar_resources r
    LEFT JOIN planning_center.calendar_resource_bookings rb 
        ON r.resource_id = rb.resource_id
        AND rb.starts_at >= CURRENT_DATE - INTERVAL '90 days'
    LEFT JOIN planning_center.calendar_event_instances ei 
        ON rb.event_instance_id = ei.event_instance_id
    WHERE r.kind = 'Room'
    GROUP BY r.resource_id, r.name, r.home_location, r.quantity, 
             DATE_TRUNC('week', rb.starts_at)
),
room_summary AS (
    SELECT 
        room_name,
        home_location,
        capacity,
        COUNT(DISTINCT week) as weeks_with_bookings,
        SUM(bookings) as total_bookings,
        SUM(hours_used) as total_hours,
        AVG(hours_used) as avg_weekly_hours,
        SUM(days_used) as total_days_used,
        SUM(unique_events) as unique_events_hosted
    FROM room_bookings
    GROUP BY resource_id, room_name, home_location, capacity
)
SELECT 
    room_name,
    home_location,
    capacity,
    total_bookings,
    ROUND(total_hours, 1) as total_hours_used,
    ROUND(avg_weekly_hours, 1) as avg_hours_per_week,
    total_days_used,
    unique_events_hosted,
    ROUND(total_hours / (90.0 * 12) * 100, 1) as utilization_rate_pct,  -- Assuming 12 hours/day availability
    CASE 
        WHEN total_hours / (90.0 * 12) > 0.7 THEN 'High'
        WHEN total_hours / (90.0 * 12) > 0.3 THEN 'Medium'
        ELSE 'Low'
    END as utilization_level
FROM room_summary
ORDER BY total_hours DESC;

Maintenance Schedule Windows

-- Find optimal maintenance windows based on usage patterns
WITH hourly_usage AS (
    SELECT 
        r.resource_id,
        r.name as resource_name,
        EXTRACT(DOW FROM rb.starts_at) as day_of_week,
        EXTRACT(HOUR FROM rb.starts_at) as hour_of_day,
        COUNT(*) as booking_count
    FROM planning_center.calendar_resources r
    LEFT JOIN planning_center.calendar_resource_bookings rb 
        ON r.resource_id = rb.resource_id
        AND rb.starts_at >= CURRENT_DATE - INTERVAL '180 days'
    WHERE r.kind = 'Room'
    GROUP BY r.resource_id, r.name, 
             EXTRACT(DOW FROM rb.starts_at), 
             EXTRACT(HOUR FROM rb.starts_at)
),
usage_patterns AS (
    SELECT 
        resource_name,
        day_of_week,
        hour_of_day,
        booking_count,
        SUM(booking_count) OVER (PARTITION BY resource_name) as total_bookings,
        RANK() OVER (PARTITION BY resource_name ORDER BY booking_count) as usage_rank
    FROM hourly_usage
)
SELECT 
    resource_name,
    CASE day_of_week
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END as best_maintenance_day,
    hour_of_day || ':00-' || (hour_of_day + 1) || ':00' as best_maintenance_time,
    booking_count as typical_bookings_at_this_time,
    ROUND(booking_count * 100.0 / NULLIF(total_bookings, 0), 2) as pct_of_total_usage
FROM usage_patterns
WHERE usage_rank <= 3  -- Bottom 3 usage slots
ORDER BY resource_name, booking_count;

Event Coordination Reports

Event Setup Requirements

-- Comprehensive Event Setup Checklist
WITH upcoming_events AS (
    SELECT 
        e.event_id,
        e.name as event_name,
        e.description,
        ei.event_instance_id,
        ei.starts_at,
        ei.ends_at,
        ei.location,
        ei.all_day_event,
        e.approval_status,
        e.owner_id
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE ei.starts_at >= CURRENT_DATE
        AND ei.starts_at < CURRENT_DATE + INTERVAL '14 days'
),
event_resources AS (
    SELECT 
        ue.event_instance_id,
        STRING_AGG(
            r.name || ' (Qty: ' || rb.quantity || ')',
            ', ' ORDER BY r.name
        ) as resources_needed,
        COUNT(DISTINCT rb.resource_id) as resource_count,
        SUM(rb.quantity) as total_items
    FROM upcoming_events ue
    LEFT JOIN planning_center.calendar_resource_bookings rb 
        ON ue.event_instance_id = rb.event_instance_id
    LEFT JOIN planning_center.calendar_resources r 
        ON rb.resource_id = r.resource_id
    GROUP BY ue.event_instance_id
),
event_conflicts AS (
    SELECT 
        rb1.event_instance_id,
        COUNT(DISTINCT rb2.event_instance_id) as conflicting_events
    FROM planning_center.calendar_resource_bookings rb1
    JOIN planning_center.calendar_resource_bookings rb2 
        ON rb1.resource_id = rb2.resource_id
        AND rb1.resource_booking_id != rb2.resource_booking_id
        AND rb1.starts_at < rb2.ends_at
        AND rb1.ends_at > rb2.starts_at
    GROUP BY rb1.event_instance_id
)
SELECT 
    ue.event_name,
    TO_CHAR(ue.starts_at, 'Dy Mon DD, HH12:MI AM') as event_time,
    ue.location,
    CASE ue.approval_status
        WHEN 'A' THEN '✓ Approved'
        WHEN 'P' THEN '⚠ Pending'
        WHEN 'R' THEN '✗ Rejected'
        ELSE '○ No Approval Required'
    END as approval_status,
    COALESCE(er.resource_count, 0) as resources_needed,
    COALESCE(er.resources_needed, 'No resources booked') as resource_list,
    COALESCE(ec.conflicting_events, 0) as potential_conflicts,
    EXTRACT(DAY FROM (ue.starts_at - CURRENT_TIMESTAMP)) as days_until_event,
    CASE 
        WHEN EXTRACT(DAY FROM (ue.starts_at - CURRENT_TIMESTAMP)) <= 2 THEN 'URGENT'
        WHEN EXTRACT(DAY FROM (ue.starts_at - CURRENT_TIMESTAMP)) <= 7 THEN 'This Week'
        ELSE 'Next Week'
    END as priority
FROM upcoming_events ue
LEFT JOIN event_resources er ON ue.event_instance_id = er.event_instance_id
LEFT JOIN event_conflicts ec ON ue.event_instance_id = ec.event_instance_id
ORDER BY ue.starts_at;

Recurring Event Consistency Report

-- Monitor Recurring Events for Irregularities
WITH recurring_events AS (
    SELECT 
        e.event_id,
        e.name,
        ei.recurrence_description,
        ei.starts_at,
        ei.location,
        LAG(ei.starts_at) OVER (PARTITION BY e.event_id ORDER BY ei.starts_at) as prev_start,
        LAG(ei.location) OVER (PARTITION BY e.event_id ORDER BY ei.starts_at) as prev_location,
        EXTRACT(DOW FROM ei.starts_at) as day_of_week,
        EXTRACT(HOUR FROM ei.starts_at) as hour_of_day
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE ei.recurrence IS NOT NULL
        AND ei.starts_at >= CURRENT_DATE - INTERVAL '90 days'
),
event_patterns AS (
    SELECT 
        event_id,
        name,
        recurrence_description,
        COUNT(*) as instance_count,
        COUNT(DISTINCT location) as location_variations,
        COUNT(DISTINCT day_of_week) as day_variations,
        COUNT(DISTINCT hour_of_day) as time_variations,
        MODE() WITHIN GROUP (ORDER BY location) as usual_location,
        MODE() WITHIN GROUP (ORDER BY day_of_week) as usual_day,
        MODE() WITHIN GROUP (ORDER BY hour_of_day) as usual_hour,
        AVG(EXTRACT(EPOCH FROM (starts_at - prev_start))/86400) as avg_days_between
    FROM recurring_events
    WHERE prev_start IS NOT NULL
    GROUP BY event_id, name, recurrence_description
)
SELECT 
    name as event_name,
    recurrence_description,
    instance_count as occurrences_last_90_days,
    CASE 
        WHEN location_variations > 1 THEN location_variations || ' different locations'
        ELSE 'Consistent location'
    END as location_consistency,
    CASE 
        WHEN day_variations > 1 THEN day_variations || ' different days'
        ELSE 'Consistent day'
    END as day_consistency,
    CASE 
        WHEN time_variations > 1 THEN time_variations || ' different times'
        ELSE 'Consistent time'
    END as time_consistency,
    usual_location,
    ROUND(avg_days_between, 1) as avg_interval_days,
    CASE 
        WHEN location_variations > 1 OR day_variations > 1 OR time_variations > 1 
        THEN 'Review needed'
        ELSE 'Consistent'
    END as status
FROM event_patterns
WHERE instance_count > 1
ORDER BY 
    CASE 
        WHEN location_variations > 1 OR day_variations > 1 OR time_variations > 1 
        THEN 0 ELSE 1 
    END,
    name;

Resource Utilization Reports

Equipment Usage Analysis

-- Equipment Utilization and Demand Report
WITH equipment_usage AS (
    SELECT 
        r.resource_id,
        r.name as equipment_name,
        r.quantity as total_quantity,
        r.serial_number,
        DATE_TRUNC('month', rb.starts_at) as month,
        COUNT(DISTINCT rb.resource_booking_id) as times_booked,
        SUM(rb.quantity) as total_quantity_booked,
        SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as hours_used,
        COUNT(DISTINCT ei.event_id) as unique_events
    FROM planning_center.calendar_resources r
    LEFT JOIN planning_center.calendar_resource_bookings rb 
        ON r.resource_id = rb.resource_id
        AND rb.starts_at >= CURRENT_DATE - INTERVAL '6 months'
    LEFT JOIN planning_center.calendar_event_instances ei 
        ON rb.event_instance_id = ei.event_instance_id
    WHERE r.kind IN ('Equipment', 'Resource')
    GROUP BY r.resource_id, r.name, r.quantity, r.serial_number,
             DATE_TRUNC('month', rb.starts_at)
),
equipment_summary AS (
    SELECT 
        equipment_name,
        total_quantity,
        serial_number,
        COUNT(DISTINCT month) as months_used,
        SUM(times_booked) as total_bookings,
        AVG(times_booked) as avg_monthly_bookings,
        SUM(hours_used) as total_hours,
        AVG(total_quantity_booked) as avg_quantity_per_booking,
        SUM(unique_events) as events_supported
    FROM equipment_usage
    GROUP BY resource_id, equipment_name, total_quantity, serial_number
)
SELECT 
    equipment_name,
    total_quantity as available_qty,
    COALESCE(serial_number, 'N/A') as serial,
    total_bookings,
    ROUND(avg_monthly_bookings, 1) as avg_bookings_per_month,
    ROUND(total_hours, 1) as total_hours_used,
    ROUND(avg_quantity_per_booking, 1) as avg_qty_per_use,
    events_supported,
    CASE 
        WHEN avg_monthly_bookings > 20 THEN 'High Demand'
        WHEN avg_monthly_bookings > 10 THEN 'Medium Demand'
        WHEN avg_monthly_bookings > 0 THEN 'Low Demand'
        ELSE 'Unused'
    END as demand_level,
    CASE 
        WHEN avg_quantity_per_booking > total_quantity * 0.8 THEN 'Consider increasing inventory'
        WHEN total_bookings = 0 THEN 'Consider removing from inventory'
        ELSE 'Adequate'
    END as recommendation
FROM equipment_summary
ORDER BY total_bookings DESC;

Peak Usage Heatmap Data

-- Generate heatmap data for resource usage patterns
WITH usage_grid AS (
    SELECT 
        TO_CHAR(rb.starts_at, 'Day') as day_name,
        EXTRACT(DOW FROM rb.starts_at) as day_num,
        EXTRACT(HOUR FROM rb.starts_at) as hour,
        r.kind as resource_type,
        COUNT(DISTINCT rb.resource_booking_id) as bookings,
        COUNT(DISTINCT rb.resource_id) as resources_used
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    WHERE rb.starts_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY TO_CHAR(rb.starts_at, 'Day'),
             EXTRACT(DOW FROM rb.starts_at),
             EXTRACT(HOUR FROM rb.starts_at),
             r.kind
),
normalized_usage AS (
    SELECT 
        day_name,
        day_num,
        hour || ':00' as time_slot,
        resource_type,
        bookings,
        resources_used,
        MAX(bookings) OVER (PARTITION BY resource_type) as max_bookings,
        ROUND(bookings * 100.0 / NULLIF(MAX(bookings) OVER (PARTITION BY resource_type), 0), 0) as intensity_pct
    FROM usage_grid
)
SELECT 
    resource_type,
    day_name,
    time_slot,
    bookings,
    resources_used,
    intensity_pct,
    CASE 
        WHEN intensity_pct >= 80 THEN '🔴 Peak'
        WHEN intensity_pct >= 50 THEN '🟡 High'
        WHEN intensity_pct >= 20 THEN '🔵 Medium'
        WHEN intensity_pct > 0 THEN '⚪ Low'
        ELSE '⚫ None'
    END as usage_level,
    REPEAT('█', (intensity_pct / 10)::int) as intensity_bar
FROM normalized_usage
WHERE hour BETWEEN 6 AND 22  -- Business hours only
ORDER BY resource_type, day_num, hour;

Conflict and Compliance Reports

Resource Conflict Report

-- Comprehensive Conflict Detection and Resolution Report
WITH conflicts AS (
    SELECT 
        r.name as resource_name,
        r.kind as resource_type,
        e1.name as event1,
        rb1.starts_at as event1_start,
        rb1.ends_at as event1_end,
        e2.name as event2,
        rb2.starts_at as event2_start,
        rb2.ends_at as event2_end,
        EXTRACT(EPOCH FROM (
            LEAST(rb1.ends_at, rb2.ends_at) - 
            GREATEST(rb1.starts_at, rb2.starts_at)
        ))/3600 as overlap_hours,
        CASE 
            WHEN e1.approval_status = 'A' AND e2.approval_status != 'A' THEN e1.name
            WHEN e2.approval_status = 'A' AND e1.approval_status != 'A' THEN e2.name
            WHEN e1.created_at < e2.created_at THEN e1.name
            ELSE e2.name
        END as priority_event
    FROM planning_center.calendar_resource_bookings rb1
    JOIN planning_center.calendar_resource_bookings rb2 
        ON rb1.resource_id = rb2.resource_id
        AND rb1.resource_booking_id < rb2.resource_booking_id
        AND rb1.starts_at < rb2.ends_at
        AND rb1.ends_at > rb2.starts_at
    JOIN planning_center.calendar_resources r ON rb1.resource_id = r.resource_id
    JOIN planning_center.calendar_event_instances ei1 ON rb1.event_instance_id = ei1.event_instance_id
    JOIN planning_center.calendar_events e1 ON ei1.event_id = e1.event_id
    JOIN planning_center.calendar_event_instances ei2 ON rb2.event_instance_id = ei2.event_instance_id
    JOIN planning_center.calendar_events e2 ON ei2.event_id = e2.event_id
    WHERE rb1.starts_at >= CURRENT_DATE
)
SELECT 
    resource_name,
    resource_type,
    TO_CHAR(event1_start, 'Mon DD HH12:MI AM') as conflict_time,
    event1 || ' vs ' || event2 as conflicting_events,
    ROUND(overlap_hours, 1) || ' hours' as overlap_duration,
    priority_event as suggested_priority,
    CASE 
        WHEN overlap_hours >= 2 THEN 'Critical'
        WHEN overlap_hours >= 1 THEN 'Major'
        ELSE 'Minor'
    END as severity
FROM conflicts
ORDER BY event1_start, severity DESC;

Approval Workflow Status

-- Event Approval Workflow Dashboard
WITH approval_timeline AS (
    SELECT 
        e.event_id,
        e.name,
        e.created_at,
        e.updated_at,
        e.approval_status,
        e.percent_approved,
        e.percent_rejected,
        ei.starts_at as event_date,
        EXTRACT(EPOCH FROM (ei.starts_at - e.created_at))/86400 as days_advance_notice,
        EXTRACT(EPOCH FROM (COALESCE(e.updated_at, CURRENT_TIMESTAMP) - e.created_at))/3600 as hours_to_decision,
        EXTRACT(EPOCH FROM (ei.starts_at - CURRENT_TIMESTAMP))/86400 as days_until_event
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE ei.starts_at >= CURRENT_DATE - INTERVAL '30 days'
        AND ei.starts_at <= CURRENT_DATE + INTERVAL '60 days'
),
approval_categories AS (
    SELECT 
        CASE approval_status
            WHEN 'A' THEN 'Approved'
            WHEN 'P' THEN 'Pending'
            WHEN 'R' THEN 'Rejected'
            ELSE 'No Approval Required'
        END as status,
        COUNT(*) as event_count,
        AVG(days_advance_notice) as avg_advance_notice,
        AVG(CASE WHEN approval_status IS NOT NULL THEN hours_to_decision END) as avg_decision_hours,
        MIN(days_until_event) FILTER (WHERE approval_status = 'P') as most_urgent_pending_days
    FROM approval_timeline
    GROUP BY approval_status
)
SELECT 
    status,
    event_count,
    ROUND(avg_advance_notice, 1) as avg_days_advance_notice,
    ROUND(avg_decision_hours, 1) as avg_hours_to_approve,
    COALESCE(ROUND(most_urgent_pending_days, 1)::text, 'N/A') as days_to_most_urgent,
    CASE 
        WHEN status = 'Pending' AND most_urgent_pending_days < 7 THEN '⚠️ Urgent Review Needed'
        WHEN status = 'Pending' THEN '📋 Review Required'
        WHEN status = 'Approved' THEN '✅ Complete'
        WHEN status = 'Rejected' THEN '❌ Denied'
        ELSE '➖ N/A'
    END as action_required
FROM approval_categories
ORDER BY 
    CASE status
        WHEN 'Pending' THEN 1
        WHEN 'Approved' THEN 2
        WHEN 'No Approval Required' THEN 3
        WHEN 'Rejected' THEN 4
    END;

Ministry Planning Reports

Annual Ministry Calendar

-- Annual Ministry Planning Calendar
WITH ministry_events AS (
    SELECT 
        DATE_TRUNC('month', ei.starts_at) as month,
        e.name,
        ei.starts_at,
        ei.ends_at,
        ei.all_day_event,
        ei.recurrence_description,
        e.featured,
        t.name as ministry_tag
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    LEFT JOIN planning_center.calendar_events_relationships er 
        ON e.event_id = er.event_id AND er.relationship_type = 'Tag'
    LEFT JOIN planning_center.calendar_tags t ON er.relationship_id = t.tag_id
    WHERE ei.starts_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND ei.starts_at < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year'
),
monthly_summary AS (
    SELECT 
        TO_CHAR(month, 'Month') as month_name,
        EXTRACT(MONTH FROM month) as month_num,
        COUNT(DISTINCT name) as unique_events,
        COUNT(*) as total_occurrences,
        COUNT(DISTINCT DATE(starts_at)) as event_days,
        COUNT(*) FILTER (WHERE featured = true) as featured_events,
        COUNT(*) FILTER (WHERE recurrence_description IS NOT NULL) as recurring_events,
        STRING_AGG(DISTINCT ministry_tag, ', ') as ministries_active
    FROM ministry_events
    GROUP BY month, TO_CHAR(month, 'Month'), EXTRACT(MONTH FROM month)
),
key_events AS (
    SELECT 
        EXTRACT(MONTH FROM starts_at) as month_num,
        STRING_AGG(
            name || ' (' || TO_CHAR(starts_at, 'DD') || ')',
            ', ' ORDER BY starts_at
        ) FILTER (WHERE featured = true) as featured_list
    FROM ministry_events
    WHERE featured = true
    GROUP BY EXTRACT(MONTH FROM starts_at)
)
SELECT 
    ms.month_name,
    ms.unique_events,
    ms.total_occurrences,
    ms.event_days,
    ms.featured_events,
    ms.recurring_events,
    COALESCE(ms.ministries_active, 'None tagged') as active_ministries,
    COALESCE(ke.featured_list, 'No featured events') as key_events
FROM monthly_summary ms
LEFT JOIN key_events ke ON ms.month_num = ke.month_num
ORDER BY ms.month_num;

Ministry Participation Trends

-- Track Ministry Engagement Through Events
WITH ministry_metrics AS (
    SELECT 
        tg.name as ministry_group,
        t.name as ministry_tag,
        DATE_TRUNC('quarter', ei.starts_at) as quarter,
        COUNT(DISTINCT e.event_id) as unique_events,
        COUNT(DISTINCT ei.event_instance_id) as total_instances,
        COUNT(DISTINCT ei.location) as locations_used,
        SUM(CASE WHEN e.visible_in_church_center THEN 1 ELSE 0 END) as public_events
    FROM planning_center.calendar_tags t
    LEFT JOIN planning_center.calendar_tag_groups tg ON t.tag_group_id = tg.tag_group_id
    LEFT JOIN planning_center.calendar_events_relationships er 
        ON t.tag_id = er.relationship_id AND er.relationship_type = 'Tag'
    LEFT JOIN planning_center.calendar_events e ON er.event_id = e.event_id
    LEFT JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE ei.starts_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY tg.name, t.name, DATE_TRUNC('quarter', ei.starts_at)
),
ministry_trends AS (
    SELECT 
        ministry_group,
        ministry_tag,
        TO_CHAR(quarter, 'Q#YYYY') as quarter_label,
        unique_events,
        total_instances,
        locations_used,
        public_events,
        LAG(total_instances, 1) OVER (
            PARTITION BY ministry_tag ORDER BY quarter
        ) as prev_quarter_instances
    FROM ministry_metrics
    WHERE quarter IS NOT NULL
)
SELECT 
    COALESCE(ministry_group, 'Uncategorized') as ministry_area,
    ministry_tag,
    quarter_label,
    unique_events,
    total_instances,
    locations_used,
    public_events,
    CASE 
        WHEN prev_quarter_instances IS NULL THEN 'New'
        WHEN total_instances > prev_quarter_instances THEN '↑ Growing'
        WHEN total_instances < prev_quarter_instances THEN '↓ Declining'
        ELSE '→ Stable'
    END as trend,
    ROUND(((total_instances - prev_quarter_instances) * 100.0 / 
           NULLIF(prev_quarter_instances, 0)), 1) as growth_pct
FROM ministry_trends
WHERE total_instances > 0
ORDER BY ministry_area, ministry_tag, quarter DESC;

Export-Ready Reports

CSV Export for Facility Schedule

-- Export-ready facility schedule for the next month
SELECT 
    TO_CHAR(ei.starts_at, 'YYYY-MM-DD') as date,
    TO_CHAR(ei.starts_at, 'HH24:MI') as start_time,
    TO_CHAR(ei.ends_at, 'HH24:MI') as end_time,
    e.name as event_name,
    ei.location as primary_location,
    STRING_AGG(DISTINCT r.name, '; ') as resources_booked,
    e.description as event_description,
    CASE e.approval_status
        WHEN 'A' THEN 'Approved'
        WHEN 'P' THEN 'Pending'
        WHEN 'R' THEN 'Rejected'
        ELSE 'No Approval Required'
    END as status,
    CASE 
        WHEN ei.all_day_event THEN 'Yes'
        ELSE 'No'
    END as all_day
FROM planning_center.calendar_events e
JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
LEFT JOIN planning_center.calendar_resource_bookings rb ON ei.event_instance_id = rb.event_instance_id
LEFT JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
WHERE ei.starts_at >= CURRENT_DATE
    AND ei.starts_at < CURRENT_DATE + INTERVAL '30 days'
GROUP BY e.event_id, e.name, e.description, e.approval_status,
         ei.event_instance_id, ei.starts_at, ei.ends_at, ei.location, ei.all_day_event
ORDER BY ei.starts_at;

Report Best Practices

1. Performance Optimization

Use date filters early in WHERE clauses
Create indexes on frequently filtered columns
Consider materialized views for complex reports

2. Data Accuracy

Always check approval_status for confirmed events
Account for all-day events in date calculations
Handle NULL values in optional fields

3. User Experience

Include visual indicators (emojis/symbols) for status
Format dates and times for readability
Provide actionable recommendations

4. Report Scheduling

Executive dashboards: Weekly
Utilization reports: Monthly
Conflict reports: Daily or as-needed
Planning reports: Quarterly

Next Steps

Review the Data Model for complete table documentation
Return to Advanced Queries for more query techniques
Check Basic Queries for fundamental concepts

Reporting examples

Calendar Report Examples

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Executive Dashboard Reports

Weekly Ministry Overview

Monthly Activity Metrics

Facility Management Reports

Room Utilization Report

Maintenance Schedule Windows

Event Coordination Reports

Event Setup Requirements

Recurring Event Consistency Report

Resource Utilization Reports

Equipment Usage Analysis

Peak Usage Heatmap Data

Conflict and Compliance Reports

Resource Conflict Report

Approval Workflow Status

Ministry Planning Reports

Annual Ministry Calendar

Ministry Participation Trends

Export-Ready Reports

CSV Export for Facility Schedule

Report Best Practices

1. Performance Optimization

2. Data Accuracy

3. User Experience

4. Report Scheduling

Next Steps

​Calendar Report Examples

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Table of Contents

​Executive Dashboard Reports

​Weekly Ministry Overview

​Monthly Activity Metrics

​Facility Management Reports

​Room Utilization Report

​Maintenance Schedule Windows

​Event Coordination Reports

​Event Setup Requirements

​Recurring Event Consistency Report

​Resource Utilization Reports

​Equipment Usage Analysis

​Peak Usage Heatmap Data

​Conflict and Compliance Reports

​Resource Conflict Report

​Approval Workflow Status

​Ministry Planning Reports

​Annual Ministry Calendar

​Ministry Participation Trends

​Export-Ready Reports

​CSV Export for Facility Schedule

​Report Best Practices

​1. Performance Optimization

​2. Data Accuracy

​3. User Experience

​4. Report Scheduling

​Next Steps

Calendar Report Examples

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Executive Dashboard Reports

Weekly Ministry Overview

Monthly Activity Metrics

Facility Management Reports

Room Utilization Report

Maintenance Schedule Windows

Event Coordination Reports

Event Setup Requirements

Recurring Event Consistency Report

Resource Utilization Reports

Equipment Usage Analysis

Peak Usage Heatmap Data

Conflict and Compliance Reports

Resource Conflict Report

Approval Workflow Status

Ministry Planning Reports

Annual Ministry Calendar

Ministry Participation Trends

Export-Ready Reports

CSV Export for Facility Schedule

Report Best Practices

1. Performance Optimization

2. Data Accuracy

3. User Experience

4. Report Scheduling

Next Steps