Basic Check-ins Queries

This guide provides simple, ready-to-use SQL queries for Planning Center Check-ins 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 Check-ins module live in the planning_center schema. Always prefix table names with planning_center. when writing queries. ✅ CORRECT: SELECT * FROM planning_center.checkins_checkins ❌ INCORRECT: SELECT * FROM checkins_checkins

Row Level Security (RLS)

Row Level Security automatically enforces:

tenant_organization_id – results limited to your organization
system_status – only active records returned by default

Skip manual filters for these columns—RLS already applies them and redundant predicates can slow queries or mask data:

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

Focus your WHERE clauses on ministry-specific logic while trusting the database to keep tenant and status filters in place.

Today’s Attendance

Who Checked In Today?

-- List everyone who checked in today
SELECT
    first_name,
    last_name,
    kind as check_in_type,
    security_code,
    created_at as checked_in_at,
    CASE
        WHEN checked_out_at IS NULL THEN 'Still here'
        ELSE 'Checked out'
    END as status
FROM planning_center.checkins_checkins
WHERE DATE(created_at) = CURRENT_DATE
ORDER BY created_at DESC;

Count Total Attendance by Type

-- See how many regulars, guests, and volunteers checked in today
SELECT
    kind as attendee_type,
    COUNT(*) as total
FROM planning_center.checkins_checkins
WHERE DATE(created_at) = CURRENT_DATE
GROUP BY kind
ORDER BY total DESC;

Currently Checked In (Not Yet Checked Out)

-- Find who is still checked in right now
SELECT
    first_name,
    last_name,
    security_code,
    kind as type,
    created_at as checked_in_at,
    EXTRACT(HOUR FROM AGE(NOW(), created_at)) as hours_checked_in
FROM planning_center.checkins_checkins
WHERE DATE(created_at) = CURRENT_DATE
  AND checked_out_at IS NULL
ORDER BY created_at;

Location-Based Queries

Check-ins by Location

-- Count check-ins for each location today
SELECT
    l.name as location_name,
    COUNT(c.check_in_id) as total_checkins
FROM planning_center.checkins_locations l
LEFT JOIN planning_center.checkins_checkin_relationships cr
    ON l.location_id = cr.relationship_id
    AND cr.relationship_type = 'Location'
LEFT JOIN planning_center.checkins_checkins c
    ON cr.checkin_id = c.check_in_id
    AND DATE(c.created_at) = CURRENT_DATE
GROUP BY l.name
HAVING COUNT(c.check_in_id) > 0
ORDER BY total_checkins DESC;

Room Capacity Status

-- Check how full each room is
SELECT
    l.name as room,
    l.max_occupancy as capacity,
    COUNT(c.check_in_id) as current_count,
    CASE
        WHEN l.max_occupancy IS NULL THEN 'No limit set'
        WHEN COUNT(c.check_in_id) >= l.max_occupancy THEN 'FULL'
        WHEN COUNT(c.check_in_id) >= l.max_occupancy * 0.8 THEN 'Nearly full'
        ELSE 'Space available'
    END as status
FROM planning_center.checkins_locations l
LEFT JOIN planning_center.checkins_checkin_relationships cr
    ON l.location_id = cr.relationship_id
    AND cr.relationship_type = 'Location'
LEFT JOIN planning_center.checkins_checkins c
    ON cr.checkin_id = c.check_in_id
    AND DATE(c.created_at) = CURRENT_DATE
    AND c.checked_out_at IS NULL  -- Only currently present
WHERE l.kind = 'Folder'  -- Physical locations only
GROUP BY l.name, l.max_occupancy
ORDER BY l.name;

Guest Tracking

First-Time Guests This Week

-- Find all first-time guests from the past 7 days
SELECT
    first_name,
    last_name,
    DATE(created_at) as visit_date,
    security_code
FROM planning_center.checkins_checkins
WHERE one_time_guest = true
  AND created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC;

Guest vs Regular Comparison

-- Compare guest and regular attendance by day this month
SELECT
    DATE(created_at) as date,
    COUNT(CASE WHEN kind = 'Guest' THEN 1 END) as guests,
    COUNT(CASE WHEN kind = 'Regular' THEN 1 END) as regulars,
    COUNT(*) as total
FROM planning_center.checkins_checkins
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY DATE(created_at)
ORDER BY date DESC;

Volunteer Management

Today’s Volunteers

-- List all volunteers who checked in today
SELECT
    first_name,
    last_name,
    created_at as checked_in_at,
    CASE
        WHEN checked_out_at IS NULL THEN 'Currently serving'
        ELSE 'Finished serving'
    END as status
FROM planning_center.checkins_checkins
WHERE kind = 'Volunteer'
  AND DATE(created_at) = CURRENT_DATE
ORDER BY created_at;

Volunteer Coverage by Hour

-- See volunteer coverage throughout the day
SELECT
    DATE_TRUNC('hour', created_at) as hour,
    COUNT(*) as volunteers_checked_in
FROM planning_center.checkins_checkins
WHERE kind = 'Volunteer'
  AND DATE(created_at) = CURRENT_DATE
GROUP BY DATE_TRUNC('hour', created_at)
ORDER BY hour;

Weekly Patterns

Attendance by Day of Week (Last 30 Days)

-- See which days have the highest attendance
SELECT
    TO_CHAR(created_at, 'Day') as day_of_week,
    COUNT(*) as total_checkins,
    COUNT(DISTINCT DATE(created_at)) as number_of_days,
    ROUND(COUNT(*)::NUMERIC / COUNT(DISTINCT DATE(created_at))) as avg_per_day
FROM planning_center.checkins_checkins
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY TO_CHAR(created_at, 'Day'), EXTRACT(DOW FROM created_at)
ORDER BY EXTRACT(DOW FROM created_at);

Peak Check-in Times

-- Find when most people check in
SELECT
    TO_CHAR(created_at, 'HH:00 AM') as hour,
    COUNT(*) as checkins
FROM planning_center.checkins_checkins
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY TO_CHAR(created_at, 'HH:00 AM'), EXTRACT(HOUR FROM created_at)
ORDER BY EXTRACT(HOUR FROM created_at);

Event Analysis

Recent Events with Attendance

-- Show events and their attendance counts
SELECT
    e.name as event_name,
    COUNT(DISTINCT c.check_in_id) as total_attendance
FROM planning_center.checkins_events e
LEFT JOIN planning_center.checkins_checkin_relationships cr
    ON e.event_id = cr.relationship_id
    AND cr.relationship_type = 'Event'
LEFT JOIN planning_center.checkins_checkins c
    ON cr.checkin_id = c.check_in_id
    AND c.created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY e.name
HAVING COUNT(DISTINCT c.check_in_id) > 0
ORDER BY total_attendance DESC;

Event Frequency Settings

-- See how often events are scheduled
SELECT
    name as event,
    frequency,
    CASE
        WHEN archived_at IS NOT NULL THEN 'Archived'
        ELSE 'Active'
    END as status
FROM planning_center.checkins_events
ORDER BY name;

Security and Safety

Security Codes in Use Today

-- List all security codes currently in use
SELECT DISTINCT
    security_code,
    COUNT(*) as times_used
FROM planning_center.checkins_checkins
WHERE DATE(created_at) = CURRENT_DATE
  AND checked_out_at IS NULL
  AND security_code IS NOT NULL
GROUP BY security_code
ORDER BY security_code;

Emergency Contact Information

-- Find check-ins with emergency contacts
SELECT
    first_name,
    last_name,
    emergency_contact_name,
    emergency_contact_phone_number,
    security_code,
    created_at
FROM planning_center.checkins_checkins
WHERE DATE(created_at) = CURRENT_DATE
  AND emergency_contact_name IS NOT NULL
ORDER BY created_at DESC;

Medical and Special Needs

Check-ins with Medical Notes

-- Find children with medical notes
SELECT
    first_name,
    last_name,
    medical_notes,
    security_code,
    created_at as checked_in_at
FROM planning_center.checkins_checkins
WHERE medical_notes IS NOT NULL
  AND medical_notes != ''
  AND DATE(created_at) = CURRENT_DATE
ORDER BY created_at DESC;

Time-Based Analysis

Average Check-in Duration

-- Calculate how long people typically stay
SELECT
    kind as attendee_type,
    COUNT(*) as total_checkouts,
    ROUND(AVG(EXTRACT(EPOCH FROM (checked_out_at - created_at))/3600), 1) as avg_hours
FROM planning_center.checkins_checkins
WHERE checked_out_at IS NOT NULL
  AND created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY kind
ORDER BY avg_hours DESC;

Busiest Check-in Days (Last 3 Months)

-- Find the days with the most check-ins
SELECT
    DATE(created_at) as date,
    TO_CHAR(created_at, 'Day') as day_name,
    COUNT(*) as total_checkins
FROM planning_center.checkins_checkins
WHERE created_at >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY DATE(created_at), TO_CHAR(created_at, 'Day')
ORDER BY total_checkins DESC
LIMIT 10;

Tips for Using These Queries

Dates: Replace CURRENT_DATE with specific dates like '2024-01-07' to query historical data
Limits: Add LIMIT 10 to any query to see just the first 10 results
Sorting: Change DESC to ASC if you want to reverse the sort order
Filtering: Add more WHERE conditions to narrow your results

Next Steps

Ready for more complex analysis? Check out:

Advanced Queries - Multi-table joins and complex calculations
Reporting Examples - Complete reports ready for leadership
Data Model - Understand all available fields and relationships

​Basic Check-ins Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Today’s Attendance

​Who Checked In Today?

​Count Total Attendance by Type

​Currently Checked In (Not Yet Checked Out)

​Location-Based Queries

​Check-ins by Location

​Room Capacity Status

​Guest Tracking

​First-Time Guests This Week

​Guest vs Regular Comparison

​Volunteer Management

​Today’s Volunteers

​Volunteer Coverage by Hour

​Weekly Patterns

​Attendance by Day of Week (Last 30 Days)

​Peak Check-in Times

​Event Analysis

​Recent Events with Attendance

​Event Frequency Settings

​Security and Safety

​Security Codes in Use Today

​Emergency Contact Information

​Medical and Special Needs

​Check-ins with Medical Notes

​Time-Based Analysis

​Average Check-in Duration

​Busiest Check-in Days (Last 3 Months)

​Tips for Using These Queries

​Next Steps