> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getparable.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Basic queries

# 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.

<Card title="Query Customization Required" icon="circle-exclamation" color="#FFA500" horizontal>
  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.
</Card>

## 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?

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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)

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

<Tip>
  **Date Ranges**: Adjust the INTERVAL values to change the time period. For example, change '7 days' to '14 days' for two weeks.
</Tip>

<Note>
  **Status Codes**:

  * C = Confirmed
  * U = Unconfirmed
  * D = Declined
</Note>

<Warning>
  **Performance**: For large databases, consider adding date filters to limit the data being processed.
</Warning>

## Next Steps

Ready for more complex queries? Check out our [Advanced Services Queries](/planning-center/services/advanced-queries) guide for:

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