​
Advanced Calendar Queries

Master complex scheduling scenarios, conflict detection, and resource optimization with these advanced SQL patterns for your church calendar.

Query Customization Required

These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.

​
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_event_instances ❌ INCORRECT: SELECT * FROM calendar_event_instances

​
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 hurt performance:
  • ❌ WHERE tenant_organization_id = 1
  • ❌ WHERE system_status = 'active'
Focus on scheduling, resource, and approval logic while trusting RLS to manage tenancy and status.

​
Table of Contents

​
Conflict Detection

​
Find Double-Booked Resources

-- Detect resources with overlapping bookings
WITH booking_conflicts AS (
    SELECT
        rb1.resource_booking_id as booking1_id,
        rb2.resource_booking_id as booking2_id,
        r.name as resource_name,
        r.kind as resource_type,
        rb1.starts_at as booking1_start,
        rb1.ends_at as booking1_end,
        rb2.starts_at as booking2_start,
        rb2.ends_at as booking2_end,
        e1.name as event1_name,
        e2.name as event2_name
    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  -- Avoid duplicates
        AND rb1.starts_at < rb2.ends_at  -- Overlap condition
        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  -- Only future conflicts
)
SELECT
    resource_name,
    resource_type,
    event1_name,
    booking1_start,
    booking1_end,
    event2_name,
    booking2_start,
    booking2_end,
    ROUND(
        EXTRACT(EPOCH FROM (
            LEAST(booking1_end, booking2_end) -
            GREATEST(booking1_start, booking2_start)
        ))/3600, 1
    ) as overlap_hours
FROM booking_conflicts
ORDER BY booking1_start, resource_name;

​
Capacity Violations

-- Find bookings exceeding resource capacity
WITH resource_capacity_check AS (
    SELECT
        rb.starts_at,
        rb.ends_at,
        r.resource_id,
        r.name as resource_name,
        r.quantity as max_capacity,
        SUM(rb.quantity) OVER (
            PARTITION BY r.resource_id, rb.starts_at
            ORDER BY rb.starts_at
        ) as total_booked,
        e.name as event_name
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    JOIN planning_center.calendar_event_instances ei ON rb.event_instance_id = ei.event_instance_id
    JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
    WHERE rb.starts_at >= CURRENT_DATE
)
SELECT
    resource_name,
    max_capacity,
    total_booked,
    total_booked - max_capacity as overbooked_by,
    starts_at,
    event_name
FROM resource_capacity_check
WHERE total_booked > max_capacity
ORDER BY starts_at, resource_name;

​
Time Buffer Violations

-- Find back-to-back bookings without buffer time
WITH sequential_bookings AS (
    SELECT
        r.name as resource_name,
        e1.name as first_event,
        rb1.ends_at as first_ends,
        e2.name as second_event,
        rb2.starts_at as second_starts,
        ROUND(
            EXTRACT(EPOCH FROM (rb2.starts_at - rb1.ends_at))/60, 1
        ) as gap_minutes
    FROM planning_center.calendar_resource_bookings rb1
    JOIN planning_center.calendar_resource_bookings rb2
        ON rb1.resource_id = rb2.resource_id
        AND rb2.starts_at >= rb1.ends_at
        AND rb2.starts_at < rb1.ends_at + INTERVAL '30 minutes'  -- Within 30 min
    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 *
FROM sequential_bookings
WHERE gap_minutes < 15  -- Less than 15 minute buffer
ORDER BY first_ends;

​
Resource Optimization

​
Underutilized Resources

-- Find resources rarely used
  WITH resource_usage AS (
      SELECT
          r.resource_id,
          r.name,
          r.kind,
          COUNT(rb.resource_booking_id) as booking_count,
          COALESCE(
              SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600),
              0
          ) as total_hours_booked,
          -- Only consider past bookings for "last used" date
          MAX(CASE
              WHEN rb.starts_at <= CURRENT_DATE THEN rb.starts_at
              ELSE NULL
          END) as last_booking_date,
          -- Track future bookings separately
          MIN(CASE
              WHEN rb.starts_at > CURRENT_DATE THEN rb.starts_at
              ELSE NULL
          END) as next_booking_date
      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'
      GROUP BY r.resource_id, r.name, r.kind
  ),
  usage_stats AS (
      SELECT
          *,
          total_hours_booked / (90 * 24.0) * 100 as utilization_percentage,
          CASE
              WHEN last_booking_date IS NOT NULL
              THEN CURRENT_DATE - last_booking_date::date
              ELSE NULL
          END as days_since_last_booking
      FROM resource_usage
  )
  SELECT
      name,
      kind,
      booking_count,
      ROUND(total_hours_booked, 2) as hours_used_90_days,
      ROUND(utilization_percentage, 2) as utilization_pct,
      CASE
          WHEN days_since_last_booking IS NOT NULL
          THEN days_since_last_booking::text || ' days ago'
          WHEN next_booking_date IS NOT NULL
          THEN 'Scheduled for ' || next_booking_date::date::text
          ELSE 'Never booked'
      END as last_activity
  FROM usage_stats
  WHERE utilization_percentage < 10  -- Less than 10% utilized
     OR booking_count < 5  -- Or rarely booked
  ORDER BY utilization_percentage;

​
Peak Usage Times

-- Identify when resources are most in demand
WITH hourly_usage AS (
    SELECT
        EXTRACT(DOW FROM rb.starts_at) as day_of_week,
        EXTRACT(HOUR FROM rb.starts_at) as hour_of_day,
        TO_CHAR(rb.starts_at, 'Day') as day_name,
        COUNT(DISTINCT rb.resource_booking_id) as bookings,
        COUNT(DISTINCT rb.resource_id) as unique_resources
    FROM planning_center.calendar_resource_bookings rb
    WHERE rb.starts_at >= CURRENT_DATE - INTERVAL '180 days'
    GROUP BY
        EXTRACT(DOW FROM rb.starts_at),
        EXTRACT(HOUR FROM rb.starts_at),
        TO_CHAR(rb.starts_at, 'Day')
)
SELECT
    day_name,
    hour_of_day || ':00' as time_slot,
    bookings,
    unique_resources,
    REPEAT('█', (bookings::float / MAX(bookings) OVER () * 20)::int) as usage_bar
FROM hourly_usage
WHERE bookings > 0
ORDER BY day_of_week, hour_of_day;

​
Optimal Resource Allocation

-- Suggest resource reassignments based on usage patterns
WITH resource_demand AS (
    SELECT
        DATE_TRUNC('week', rb.starts_at) as week,
        r.kind,
        COUNT(DISTINCT rb.resource_booking_id) as bookings,
        COUNT(DISTINCT r.resource_id) as resources_used,
        SUM(rb.quantity) as total_quantity_requested
    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 '12 weeks'
    GROUP BY DATE_TRUNC('week', rb.starts_at), r.kind
),
resource_availability AS (
    SELECT
        kind,
        COUNT(*) as total_resources,
        SUM(quantity) as total_capacity
    FROM planning_center.calendar_resources
    GROUP BY kind
)
SELECT
    rd.kind as resource_type,
    ROUND(AVG(rd.bookings)::numeric, 2) as avg_weekly_bookings,
    ROUND(AVG(rd.resources_used)::numeric, 2) as avg_resources_used,
    ra.total_resources as available_resources,
    ROUND(AVG(rd.resources_used) * 100.0 / ra.total_resources, 2) as utilization_rate,
    CASE
        WHEN AVG(rd.resources_used) * 100.0 / ra.total_resources > 80 THEN 'High Demand - Consider adding resources'
        WHEN AVG(rd.resources_used) * 100.0 / ra.total_resources < 30 THEN 'Low Demand - Consider consolidating'
        ELSE 'Balanced'
    END as recommendation
FROM resource_demand rd
JOIN resource_availability ra ON rd.kind = ra.kind
GROUP BY rd.kind, ra.total_resources, ra.total_capacity
ORDER BY utilization_rate DESC;

​
Complex Scheduling Patterns

​
Multi-Resource Event Requirements

-- Events requiring multiple resources simultaneously
WITH event_resource_summary AS (
    SELECT
        e.event_id,
        e.name as event_name,
        ei.starts_at,
        ei.ends_at,
        COUNT(DISTINCT rb.resource_id) as resource_count,
        STRING_AGG(DISTINCT r.name || ' (' || r.kind || ')', ', ' ORDER BY r.name) as resources_needed,
        SUM(rb.quantity) as total_quantity
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    JOIN planning_center.calendar_resource_bookings rb ON ei.event_instance_id = rb.event_instance_id
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    WHERE ei.starts_at >= CURRENT_DATE
    GROUP BY e.event_id, e.name, ei.event_instance_id, ei.starts_at, ei.ends_at
    HAVING COUNT(DISTINCT rb.resource_id) > 1  -- Multiple resources
)
SELECT
    event_name,
    starts_at,
    resource_count,
    resources_needed,
    total_quantity
FROM event_resource_summary
ORDER BY starts_at, resource_count DESC;

​
Recurring Event Pattern Analysis

-- Analyze recurring event patterns and exceptions
WITH recurring_analysis AS (
    SELECT
        e.event_id,
        e.name,
        ei.recurrence,
        ei.recurrence_description,
        COUNT(*) as total_instances,
        MIN(ei.starts_at) as first_occurrence,
        MAX(ei.starts_at) as last_occurrence,
        -- Calculate average interval between occurrences
        EXTRACT(EPOCH FROM (MAX(ei.starts_at) - MIN(ei.starts_at))) /
            NULLIF(COUNT(*) - 1, 0) / 86400 as avg_days_between,
        -- Detect irregular patterns
        STDDEV(EXTRACT(DOW FROM ei.starts_at)) as day_variance,
        STDDEV(EXTRACT(HOUR FROM ei.starts_at)) as hour_variance
    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
    GROUP BY e.event_id, e.name, ei.recurrence, ei.recurrence_description
)
SELECT
    name,
    recurrence_description,
    total_instances,
    TO_CHAR(first_occurrence, 'Mon DD, YYYY') as first_date,
    TO_CHAR(last_occurrence, 'Mon DD, YYYY') as last_date,
    ROUND(avg_days_between, 1) as avg_days_interval,
    CASE
        WHEN day_variance < 0.5 THEN 'Consistent day'
        WHEN day_variance < 2 THEN 'Variable day'
        ELSE 'Irregular schedule'
    END as schedule_consistency,
    CASE
        WHEN hour_variance < 0.5 THEN 'Consistent time'
        ELSE 'Variable time'
    END as time_consistency
FROM recurring_analysis
ORDER BY total_instances DESC;

​
Availability Windows

-- Find available time slots for a resource
WITH time_slots AS (
    -- Generate hourly time slots for next 7 days
    SELECT
        generate_series(
            DATE_TRUNC('hour', CURRENT_TIMESTAMP),
            DATE_TRUNC('hour', CURRENT_TIMESTAMP) + INTERVAL '7 days',
            INTERVAL '1 hour'
        ) as slot_start
),
booked_slots AS (
    -- Find already booked time slots
    SELECT DISTINCT
        DATE_TRUNC('hour', rb.starts_at) as booked_start,
        DATE_TRUNC('hour', rb.ends_at) + INTERVAL '1 hour' as booked_end,
        r.resource_id,
        r.name
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    WHERE r.name = 'Main Sanctuary'  -- Change to desired resource
        AND rb.starts_at >= CURRENT_TIMESTAMP
        AND rb.starts_at < CURRENT_TIMESTAMP + INTERVAL '7 days'
),
availability AS (
    SELECT
        ts.slot_start,
        ts.slot_start + INTERVAL '1 hour' as slot_end,
        CASE
            WHEN bs.booked_start IS NULL THEN 'Available'
            ELSE 'Booked'
        END as status
    FROM time_slots ts
    LEFT JOIN booked_slots bs
        ON ts.slot_start >= bs.booked_start
        AND ts.slot_start < bs.booked_end
    WHERE EXTRACT(HOUR FROM ts.slot_start) BETWEEN 8 AND 20  -- Business hours only
)
SELECT
    TO_CHAR(slot_start, 'Day, Mon DD') as date,
    TO_CHAR(slot_start, 'HH12:MI AM') || ' - ' || TO_CHAR(slot_end, 'HH12:MI AM') as time_slot,
    status
FROM availability
WHERE status = 'Available'
ORDER BY slot_start
LIMIT 20;

​
Utilization Analytics

-- Track facility utilization trends over time
WITH monthly_metrics AS (
    SELECT
        DATE_TRUNC('month', rb.starts_at) as month,
        r.kind as resource_type,
        COUNT(DISTINCT rb.resource_booking_id) as total_bookings,
        COUNT(DISTINCT DATE(rb.starts_at)) as days_with_bookings,
        COUNT(DISTINCT rb.resource_id) as unique_resources_used,
        SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as total_hours,
        AVG(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as avg_booking_hours
    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 '12 months'
    GROUP BY DATE_TRUNC('month', rb.starts_at), r.kind
),
with_trends AS (
    SELECT
        *,
        LAG(total_bookings, 1) OVER (PARTITION BY resource_type ORDER BY month) as prev_month_bookings,
        AVG(total_hours) OVER (
            PARTITION BY resource_type
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) as three_month_avg_hours
    FROM monthly_metrics
)
SELECT
    TO_CHAR(month, 'Mon YYYY') as month_year,
    resource_type,
    total_bookings,
    days_with_bookings,
    ROUND(total_hours, 1) as total_hours,
    ROUND(avg_booking_hours, 1) as avg_duration,
    ROUND(((total_bookings - prev_month_bookings) * 100.0 / NULLIF(prev_month_bookings, 0)), 1) as month_over_month_pct,
    ROUND(three_month_avg_hours, 1) as rolling_3mo_avg_hours
FROM with_trends
ORDER BY month DESC, resource_type;

​
Cost Per Use Analysis

-- Calculate implied cost per resource use (if costs were tracked)
WITH resource_usage_costs AS (
    SELECT
        r.resource_id,
        r.name,
        r.kind,
        COUNT(rb.resource_booking_id) as times_used,
        SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as total_hours_used,
        -- Assuming some baseline costs (customize as needed)
        CASE r.kind
            WHEN 'Room' THEN 50  -- $50/hour for rooms
            WHEN 'Equipment' THEN 25  -- $25/hour for equipment
            ELSE 10  -- $10/hour for other
        END as hourly_rate
    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 >= DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY r.resource_id, r.name, r.kind
)
SELECT
    name,
    kind,
    times_used,
    ROUND(total_hours_used, 2) as hours_used,
    hourly_rate as rate_per_hour,
    ROUND(total_hours_used * hourly_rate, 2) as implied_value,
    CASE
        WHEN times_used > 0 THEN ROUND((total_hours_used * hourly_rate) / times_used, 2)
        ELSE 0
    END as value_per_use
FROM resource_usage_costs
WHERE times_used > 0
ORDER BY implied_value DESC;

​
Approval Workflows

​
Pending Approvals

-- Events awaiting approval with their resource requirements
SELECT
    e.event_id,
    e.name as event_name,
    e.approval_status,
    e.percent_approved,
    e.percent_rejected,
    ei.starts_at,
    COUNT(DISTINCT err.event_resource_request_id) as pending_resource_requests,
    STRING_AGG(DISTINCT r.name, ', ') as requested_resources
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_event_resource_requests err
    ON e.event_id = err.event_id
LEFT JOIN planning_center.calendar_resources r
    ON err.resource_id = r.resource_id
WHERE e.approval_status IN ('P', NULL)  -- Pending or not yet reviewed
    AND ei.starts_at >= CURRENT_DATE
GROUP BY e.event_id, e.name, e.approval_status, e.percent_approved,
         e.percent_rejected, ei.event_instance_id, ei.starts_at
ORDER BY ei.starts_at;

​
Approval Response Times

-- Analyze how quickly approvals are processed
WITH approval_metrics AS (
    SELECT
        e.event_id,
        e.name,
        e.created_at as request_time,
        e.updated_at as decision_time,
        e.approval_status,
        EXTRACT(EPOCH FROM (e.updated_at - e.created_at))/3600 as hours_to_decision,
        ei.starts_at as event_start,
        EXTRACT(EPOCH FROM (ei.starts_at - e.created_at))/86400 as days_advance_notice
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE e.approval_status IS NOT NULL
        AND e.updated_at > e.created_at
)
SELECT
    approval_status,
    COUNT(*) as total_events,
    ROUND(AVG(hours_to_decision), 1) as avg_hours_to_decision,
    ROUND(MIN(hours_to_decision), 1) as fastest_decision,
    ROUND(MAX(hours_to_decision), 1) as slowest_decision,
    ROUND(AVG(days_advance_notice), 1) as avg_days_advance_notice
FROM approval_metrics
GROUP BY approval_status
ORDER BY approval_status;

​
Recurring Event Management

​
Detect Broken Recurring Patterns

-- Find recurring events with missed occurrences
WITH expected_intervals AS (
    SELECT
        e.event_id,
        e.name,
        ei.recurrence,
        -- Calculate expected interval based on recurrence type
        CASE
            WHEN ei.recurrence LIKE '%WEEKLY%' THEN 7
            WHEN ei.recurrence LIKE '%DAILY%' THEN 1
            WHEN ei.recurrence LIKE '%MONTHLY%' THEN 30
            ELSE NULL
        END as expected_days,
        ei.starts_at,
        LAG(ei.starts_at) OVER (PARTITION BY e.event_id ORDER BY ei.starts_at) as prev_occurrence,
        EXTRACT(EPOCH FROM (
            ei.starts_at - LAG(ei.starts_at) OVER (PARTITION BY e.event_id ORDER BY ei.starts_at)
        ))/86400 as actual_days_gap
    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
)
SELECT
    name,
    TO_CHAR(prev_occurrence, 'Mon DD, YYYY') as previous_date,
    TO_CHAR(starts_at, 'Mon DD, YYYY') as current_date,
    expected_days,
    ROUND(actual_days_gap, 1) as actual_days,
    ROUND(actual_days_gap - expected_days, 1) as variance_days
FROM expected_intervals
WHERE expected_days IS NOT NULL
    AND actual_days_gap IS NOT NULL
    AND ABS(actual_days_gap - expected_days) > expected_days * 0.2  -- 20% variance
ORDER BY starts_at DESC;

​
Performance Optimization

​
Optimized Conflict Detection Query

-- Efficient conflict detection using window functions
WITH resource_timeline AS (
    SELECT
        rb.resource_id,
        r.name as resource_name,
        rb.starts_at,
        rb.ends_at,
        e.name as event_name,
        -- Use window functions to find overlaps
        LAG(rb.ends_at) OVER (PARTITION BY rb.resource_id ORDER BY rb.starts_at) as prev_end,
        LEAD(rb.starts_at) OVER (PARTITION BY rb.resource_id ORDER BY rb.starts_at) as next_start
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    JOIN planning_center.calendar_event_instances ei ON rb.event_instance_id = ei.event_instance_id
    JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
    WHERE rb.starts_at >= CURRENT_DATE
        AND rb.starts_at < CURRENT_DATE + INTERVAL '30 days'
)
SELECT
    resource_name,
    event_name,
    starts_at,
    ends_at,
    CASE
        WHEN prev_end > starts_at THEN 'Conflict with previous'
        WHEN next_start < ends_at THEN 'Conflict with next'
        ELSE 'No conflict'
    END as conflict_status
FROM resource_timeline
WHERE prev_end > starts_at OR next_start < ends_at
ORDER BY resource_id, starts_at;

​
Best Practices for Advanced Calendar Queries

​
1. Use Window Functions for Sequential Analysis

Window functions are perfect for finding gaps, overlaps, and patterns in scheduling data.

​
2. Optimize Date Range Filters

Always filter by date ranges early in your query to reduce the dataset size.

​
3. Handle NULL Values in Scheduling

Remember that some fields like approval_status or recurrence might be NULL.

​
4. Consider Time Zones

Ensure your timestamp comparisons account for time zone differences if applicable.

​
5. Use CTEs for Complex Logic

Break down complex scheduling logic into manageable CTEs for better readability and performance.

​
Next Steps