Basic Services Queries

This guide provides simple, ready-to-use SQL queries for Planning Center Services data. Each query is designed to answer common ministry questions without requiring deep SQL knowledge.

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 Services module live in the planning_center schema. Always prefix table names with planning_center. in your SQL. ✅ CORRECT: SELECT * FROM planning_center.services_plans ❌ INCORRECT: SELECT * FROM services_plans

Row Level Security (RLS)

Row Level Security automatically manages:

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

Avoid adding these filters manually—RLS already enforces them and redundant predicates can hide data or slow queries:

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

Concentrate on scheduling, volunteer, and song-specific filters while trusting RLS to handle tenancy and status.

This Week’s Schedule

Who’s Serving This Sunday?

-- List everyone scheduled for this Sunday's services
SELECT
    p.full_name,
    pp.team_position_name as role,
    t.name as team,
    pl.title as service,
    pl.short_dates as date,
    pp.status
FROM planning_center.services_plan_people pp
JOIN planning_center.services_people p ON pp.person_id = p.person_id
JOIN planning_center.services_teams t ON pp.team_id = t.team_id
JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
WHERE pl.sort_date >= CURRENT_DATE
    AND pl.sort_date < CURRENT_DATE + INTERVAL '7 days'
ORDER BY pl.sort_date, t.name, pp.team_position_name;

Team Status Summary

-- See confirmed vs unconfirmed for this week
SELECT
    t.name as team,
    COUNT(*) as total_scheduled,
    COUNT(CASE WHEN pp.status = 'C' THEN 1 END) as confirmed,
    COUNT(CASE WHEN pp.status = 'U' THEN 1 END) as unconfirmed,
    COUNT(CASE WHEN pp.status = 'D' THEN 1 END) as declined
FROM planning_center.services_plan_people pp
JOIN planning_center.services_teams t ON pp.team_id = t.team_id
JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
WHERE pl.sort_date >= CURRENT_DATE
    AND pl.sort_date < CURRENT_DATE + INTERVAL '7 days'
GROUP BY t.name
ORDER BY total_scheduled DESC;

Upcoming Service Plans

-- Next 4 weeks of services
SELECT
    st.name as service_type,
    p.title,
    p.series_title,
    p.short_dates as dates,
    p.plan_people_count as volunteers,
    p.needed_positions_count as open_positions,
    p.total_length / 60 as duration_minutes
FROM planning_center.services_plans p
JOIN planning_center.services_service_types st ON p.service_type_id = st.service_type_id
WHERE p.sort_date >= CURRENT_DATE
    AND p.sort_date <= CURRENT_DATE + INTERVAL '28 days'
ORDER BY p.sort_date;

Song Analytics

Most Used Songs (Last 3 Months)

-- Top 20 most frequently used songs
SELECT
    s.title,
    s.author,
    s.ccli_number,
    COUNT(DISTINCT i.plan_id) as times_used,
    STRING_AGG(DISTINCT a.chord_chart_key, ', ') as keys_used,
    MAX(pl.sort_date) as last_used
FROM planning_center.services_songs s
JOIN planning_center.services_items i ON s.song_id = i.song_id
JOIN planning_center.services_plans pl ON i.plan_id = pl.plan_id
LEFT JOIN planning_center.services_arrangements a ON i.arrangement_id = a.arrangement_id
WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
    AND i.item_type = 'song'
GROUP BY s.song_id, s.title, s.author, s.ccli_number
ORDER BY times_used DESC
LIMIT 20;

Songs by Theme

-- Find songs by theme/tag
SELECT
    title,
    author,
    themes,
    last_scheduled_at,
    CASE
        WHEN hidden = true THEN 'Hidden'
        ELSE 'Active'
    END as status
FROM planning_center.services_songs
WHERE LOWER(themes) LIKE '%worship%'
    OR LOWER(themes) LIKE '%communion%'
ORDER BY last_scheduled_at DESC NULLS LAST;

Song Key Preferences

-- Most common keys for frequently used songs
SELECT
    s.title,
    a.chord_chart_key as song_key,
    COUNT(*) as times_in_key
FROM planning_center.services_items i
JOIN planning_center.services_songs s ON i.song_id = s.song_id
JOIN planning_center.services_arrangements a ON i.arrangement_id = a.arrangement_id
JOIN planning_center.services_plans p ON i.plan_id = p.plan_id
WHERE p.sort_date >= CURRENT_DATE - INTERVAL '6 months'
    AND a.chord_chart_key IS NOT NULL
GROUP BY s.title, a.chord_chart_key
ORDER BY s.title, times_in_key DESC;

Volunteer Management

Team Participation This Month

-- How many times each person served this month
SELECT
    p.full_name,
    t.name as team,
    COUNT(DISTINCT pl.plan_id) as times_scheduled,
    COUNT(CASE WHEN pp.status = 'C' THEN 1 END) as times_confirmed,
    COUNT(CASE WHEN pp.status = 'D' THEN 1 END) as times_declined
FROM planning_center.services_people p
JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
JOIN planning_center.services_teams t ON pp.team_id = t.team_id
JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
WHERE pl.sort_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND pl.sort_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY p.full_name, t.name
HAVING COUNT(DISTINCT pl.plan_id) > 0
ORDER BY times_scheduled DESC;

Volunteer Availability

-- People with upcoming blockout dates
SELECT
    p.full_name,
    b.reason,
    bd.date as unavailable_date
FROM planning_center.services_blockouts b
JOIN planning_center.services_blockout_dates bd ON b.blockout_id = bd.blockout_id
JOIN planning_center.services_people p ON b.person_id = p.person_id
WHERE bd.date >= CURRENT_DATE
    AND bd.date <= CURRENT_DATE + INTERVAL '30 days'
ORDER BY bd.date, p.full_name;

Team Capacity

-- Teams and their available positions
SELECT
    t.name as team,
    tp.name as position,
    COUNT(DISTINCT pa.person_id) as available_people
FROM planning_center.services_teams t
JOIN planning_center.services_team_positions tp ON t.team_id = tp.team_id
JOIN planning_center.services_person_team_position_assignments pa
    ON tp.team_position_id = pa.team_position_id
JOIN planning_center.services_people p ON pa.person_id = p.person_id
WHERE p.archived = false
    AND t.archived_at IS NULL
GROUP BY t.name, tp.name
ORDER BY t.name, tp.name;

Service Planning

Service Item Breakdown

-- What's in this Sunday's service?
SELECT
    pl.title as service,
    i.sequence as order_num,
    i.item_type,
    i.title as item,
    s.title as song_title,
    i.length / 60 as minutes,
    i.service_position
FROM planning_center.services_items i
JOIN planning_center.services_plans pl ON i.plan_id = pl.plan_id
LEFT JOIN planning_center.services_songs s ON i.song_id = s.song_id
WHERE pl.sort_date >= CURRENT_DATE
    AND pl.sort_date < CURRENT_DATE + INTERVAL '7 days'
ORDER BY pl.sort_date, i.sequence;

Service Timing Analysis

-- Average service length by type
SELECT
    st.name as service_type,
    COUNT(p.plan_id) as total_services,
    AVG(p.total_length) / 60 as avg_length_minutes,
    MIN(p.total_length) / 60 as shortest_minutes,
    MAX(p.total_length) / 60 as longest_minutes
FROM planning_center.services_plans p
JOIN planning_center.services_service_types st ON p.service_type_id = st.service_type_id
WHERE p.sort_date >= CURRENT_DATE - INTERVAL '3 months'
    AND p.total_length > 0
GROUP BY st.name
ORDER BY avg_length_minutes DESC;

Needed Positions

-- Unfilled positions for upcoming services
SELECT
    pl.title as service,
    pl.short_dates as date,
    t.name as team,
    np.team_position_name as position,
    np.quantity as spots_needed
FROM planning_center.services_needed_positions np
JOIN planning_center.services_plans pl ON np.plan_id = pl.plan_id
JOIN planning_center.services_teams t ON np.team_id = t.team_id
WHERE pl.sort_date >= CURRENT_DATE
    AND pl.sort_date <= CURRENT_DATE + INTERVAL '14 days'
ORDER BY pl.sort_date, t.name;

Quick Counts

Total Active Volunteers

-- Count of people who have served in last 3 months
SELECT
    COUNT(DISTINCT pp.person_id) as active_volunteers
FROM planning_center.services_plan_people pp
JOIN planning_center.services_plans p ON pp.plan_id = p.plan_id
WHERE p.sort_date >= CURRENT_DATE - INTERVAL '3 months'
    AND pp.status IN ('C', 'U');  -- Confirmed or Unconfirmed

Songs in Library

-- Total songs and arrangements
SELECT
    COUNT(DISTINCT s.song_id) as total_songs,
    COUNT(DISTINCT a.arrangement_id) as total_arrangements,
    COUNT(DISTINCT CASE WHEN s.hidden = false THEN s.song_id END) as active_songs
FROM planning_center.services_songs s
LEFT JOIN planning_center.services_arrangements a ON s.song_id = a.song_id;

Service Types

-- Active service types and their frequency
SELECT
    name,
    frequency,
    CASE
        WHEN archived_at IS NULL THEN 'Active'
        ELSE 'Archived'
    END as status
FROM planning_center.services_service_types
ORDER BY sequence;

File Attachments

Recent Chord Charts

-- Recently added chord charts and lead sheets
SELECT
    s.title as song,
    a.filename,
    a.file_size / 1024 as size_kb,
    a.created_at as added_date
FROM planning_center.services_attachments a
JOIN planning_center.services_songs s ON a.attachable_id = s.song_id
WHERE a.attachable_type = 'Song'
    AND (LOWER(a.filename) LIKE '%.pdf'
         OR LOWER(a.content_type) LIKE '%pdf%')
ORDER BY a.created_at DESC
LIMIT 20;

Plan Resources

-- Files attached to upcoming plans
SELECT
    p.title as plan,
    p.short_dates as date,
    a.filename,
    a.display_name,
    a.content_type
FROM planning_center.services_attachments a
JOIN planning_center.services_plans p ON a.attachable_id = p.plan_id
WHERE a.attachable_type = 'Plan'
    AND p.sort_date >= CURRENT_DATE
    AND p.sort_date <= CURRENT_DATE + INTERVAL '7 days'
ORDER BY p.sort_date, a.created_at;

Tips for Using These Queries

Date Ranges: Adjust the INTERVAL values to change the time period. For example, change ‘7 days’ to ‘14 days’ for two weeks.

Status Codes:

C = Confirmed
U = Unconfirmed
D = Declined

Performance: For large databases, consider adding date filters to limit the data being processed.

Next Steps

Ready for more complex queries? Check out our Advanced Services Queries guide for:

Multi-team scheduling analysis
Volunteer burnout detection
Song rotation optimization
Service planning templates
Cross-campus coordination

​Basic Services Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​This Week’s Schedule

​Who’s Serving This Sunday?

​Team Status Summary

​Upcoming Service Plans

​Song Analytics

​Most Used Songs (Last 3 Months)

​Songs by Theme

​Song Key Preferences

​Volunteer Management

​Team Participation This Month

​Volunteer Availability

​Team Capacity

​Service Planning

​Service Item Breakdown

​Service Timing Analysis

​Needed Positions

​Quick Counts

​Total Active Volunteers

​Songs in Library

​Service Types

​File Attachments

​Recent Chord Charts

​Plan Resources

​Tips for Using These Queries

​Next Steps

Basic Services Queries

Query Requirements

Schema Prefix

Row Level Security (RLS)

This Week’s Schedule

Who’s Serving This Sunday?

Team Status Summary

Upcoming Service Plans

Song Analytics

Most Used Songs (Last 3 Months)

Songs by Theme

Song Key Preferences

Volunteer Management

Team Participation This Month

Volunteer Availability

Team Capacity

Service Planning

Service Item Breakdown

Service Timing Analysis

Needed Positions

Quick Counts

Total Active Volunteers

Songs in Library

Service Types

File Attachments

Recent Chord Charts

Plan Resources

Tips for Using These Queries

Next Steps