Swaby's TouchPoint SQL Database Reference

📊 Database Overview & Architecture

TouchPoint is an enterprise-level church management system with one of the most comprehensive databases in the church software industry. Understanding its scale and architecture is crucial for effective use.

🏗️ Database Scale (Verified from Local Database)

📈 Production Data Volumes (from Local Database)

🏛️ Core System Architecture

-- Core Entity Relationships
People (PeopleId) 
    ├── FamilyId → Groups family members
    ├── SpouseId → Direct spouse link
    └── OrganizationMembers → Group participation
        └── Organizations (OrganizationId)
            ├── Meetings → Scheduled events
            └── Attend → Attendance records

People (PeopleId)
    ├── Contribution → Financial giving
    │   └── ContributionFund → Designated funds
    ├── TaskNote → Tasks and notes
    └── Registration → Event signups
        └── RegPeople → Participants
            └── RegAnswer → Form responses

🚨 Critical Business Rules - READ THIS FIRST

-- ✅ CORRECT: Always start with TOP
SELECT TOP 100 * FROM People WHERE IsDeceased = 0 AND ArchivedFlag = 0

-- ❌ WRONG: Never run unlimited on large tables
SELECT * FROM TagPerson  -- This table has 138M+ records!

-- 🚨 FILTER #1: Active People Only
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0

-- 🚨 FILTER #2: Actual Attendance (not absences)
WHERE a.AttendanceFlag = 1  -- 1 = present, 0 = absent

-- 🚨 FILTER #3: Real Donations (exclude non-giving types)
WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)  -- 6=Returned, 7=Reversed, 8=Pledge, 99=Event Fee
  AND c.ContributionStatusId = 0  -- Posted contributions only

-- 🚨 FILTER #4: Active Organizations
WHERE o.OrganizationStatusId = 30  -- 30 = Active

-- 🚨 FILTER #5: Current Members Only
WHERE om.InactiveDate IS NULL

-- 🚨 FILTER #6: Completed Meetings
WHERE m.DidNotMeet = 0

-- Always check permissions before communication
WHERE p.DoNotMailFlag = 0      -- Can send mail
WHERE p.DoNotCallFlag = 0      -- Can make calls
WHERE p.DoNotVisitFlag = 0     -- Can visit
WHERE p.ReceiveSMS = 1         -- Can send texts
WHERE p.SendEmailAddress1 = 1  -- Can email primary
WHERE p.SendEmailAddress2 = 1  -- Can email secondary

-- Ensure good data quality
WHERE p.PrimaryBadAddrFlag = 0  -- Valid addresses only
WHERE p.CellPhoneIsValid = 1    -- Valid phone numbers
WHERE p.EmailAddress IS NOT NULL AND p.EmailAddress != ''

🔥 Hard-Won Lessons & Gotchas

These are critical discoveries from months of real-world development. Each one caused real debugging pain. Learn from our mistakes!

-- ✅ CORRECT
SELECT * FROM [Transaction] WHERE Approved = 1

-- ❌ WRONG - Will cause a syntax error
SELECT * FROM Transaction WHERE Approved = 1

-- ✅ CORRECT - BIT comparison
WHERE t.Approved = 1

-- ❌ WRONG - Approved is not a string
WHERE t.Approved = 'Approved'

-- ✅ CORRECT - Catches all abandoned registrations
WHERE rd.Completed IS NULL

-- ❌ WRONG - Will miss records (NULL != 0)
WHERE rd.Completed = 0

-- ❌ WRONG - This is not valid SQL for NULL comparison
WHERE rd.Completed = NULL

-- ✅ CORRECT - Uses the actual (misspelled) column name
SELECT Vistors FROM OrganizationStructure

-- ❌ WRONG - This column does not exist
SELECT Visitors FROM OrganizationStructure

-- ✅ CORRECT
SELECT * FROM TagPerson tp
JOIN Tag t ON tp.Id = t.Id

-- ❌ WRONG - TagId is NOT the join column
SELECT * FROM TagPerson tp
JOIN Tag t ON tp.TagId = t.Id

-- ✅ CORRECT - Direct match, not FK
SELECT * FROM EmailQueue eq
JOIN EmailQueueTo eqt ON eq.Id = eqt.Id

-- ✅ CORRECT - Excludes all non-giving types
WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)
  AND c.ContributionStatusId = 0  -- Posted only

-- ❌ INCOMPLETE - Only excludes event fees, includes returned checks and reversals
WHERE c.ContributionTypeId != 99

-- Types to exclude:
-- 6 = Returned Check (bounced)
-- 7 = Reversed (cancelled)
-- 8 = Pledge (commitment, not actual giving)
-- 99 = Event Registration Fee (payment, not donation)

-- ✅ CORRECT - Uses the VIEW with correct column name
SELECT * FROM IncompleteTasks WHERE CreatedOn >= DATEADD(day, -30, GETDATE())

-- ❌ WRONG - CreatedDate is the Task TABLE column, not the VIEW column
SELECT * FROM IncompleteTasks WHERE CreatedDate >= DATEADD(day, -30, GETDATE())

-- ✅ BEST PRACTICE - Match exact name from database
WHERE mt.Name = 'Blue Team'
  AND mt.OrgId = 123  -- SubGroups are org-specific

-- ✅ SIMPLER - Use pre-calculated field
SELECT Name, Age FROM People WHERE Age >= 18

-- Also works but unnecessary for filtering
SELECT Name, DATEDIFF(year, BDate, GETDATE()) as CalculatedAge FROM People

-- ✅ CORRECT
WHERE om.InactiveDate IS NULL  -- Active members only

-- ❌ WRONG
WHERE om.InactiveDate = 0

WHERE m.DidNotMeet = 0  -- Meeting actually occurred

⚡ Performance Guidelines & Large Table Warnings

🎯 TOP Clause Usage Guidelines

🚨 High-Volume Table Requirements

-- TagPerson (138M+ records) - EXTREME CAUTION
SELECT TOP 100 * FROM TagPerson tp
WHERE tp.DateCreated >= DATEADD(day, -30, GETDATE())

-- EmailResponses (3.9M+ records)
SELECT TOP 100 * FROM EmailResponses er
WHERE er.Dt >= DATEADD(week, -1, GETDATE())

-- ActivityLog (3.4M+ records)
SELECT TOP 100 * FROM ActivityLog al
WHERE al.ActivityDate >= DATEADD(day, -7, GETDATE())

-- EngagementScoreTag (1.4M+ records)
SELECT TOP 100 * FROM EngagementScoreTag est
JOIN EngagementScore es ON est.EngagementScoreId = es.Id
WHERE es.WeekDate >= DATEADD(month, -3, GETDATE())

-- EmailQueueTo (3.8M+ records)
SELECT TOP 100 * FROM EmailQueueTo eqt
JOIN EmailQueue eq ON eqt.Id = eq.Id
WHERE eq.Sent >= DATEADD(month, -1, GETDATE())

✅ Safe Query Development Process

-- STEP 1: Start with minimal data
SELECT TOP 10 * 
FROM YourTable
WHERE DateField >= DATEADD(day, -1, GETDATE())

-- STEP 2: Verify query logic with small sample
SELECT TOP 100 
    SpecificColumns,
    YouNeed
FROM YourTable
WHERE DateField >= DATEADD(week, -1, GETDATE())
  AND OtherFilters = 'Applied'

-- STEP 3: Expand sample if needed
SELECT TOP 1000 
    -- your final column list
FROM YourTable
WHERE -- all your filters
ORDER BY -- your sort

-- STEP 4: Remove TOP only for production reports
-- after confirming performance is acceptable

🚨 Tables Over 1M Records - EXTREME CAUTION

Safe Query Patterns for Large Tables

-- GOOD: ActivityLog with date filter
SELECT TOP 1000 * 
FROM ActivityLog 
WHERE ActivityDate >= DATEADD(day, -7, GETDATE())

-- BAD: Never do this!
-- SELECT * FROM ActivityLog

-- GOOD: EngagementScore with filters
SELECT es.* 
FROM EngagementScore es
WHERE es.WeekDate >= DATEADD(month, -3, GETDATE())
  AND es.PeopleId IN (SELECT PeopleId FROM ...)

-- GOOD: Contribution with date range
SELECT c.*
FROM Contribution c
WHERE c.ContributionDate BETWEEN '2024-01-01' AND '2024-12-31'
  AND c.FundId = 1

📚 Table Quick Reference Guide

🗂️ Core Tables by Category

-- PEOPLE & IDENTITY (Primary Keys and Relationships)
People              -- 50K+ records, PeopleId primary key
PeopleExtra         -- 213K+ records, custom fields for people
Families            -- Family units, FamilyId primary key
FamilyExtra         -- Custom fields for families
Users               -- 7K+ records, links PeopleId to system users

-- ORGANIZATIONAL HIERARCHY (3-Level Structure)
Program             -- 42 records, top level (e.g., "Adult Ministry")
Division            -- 217 records, second level, links to Program via ProgId
Organizations       -- 1.5K+ records, actual groups/classes, links to Division
DivOrg              -- Links Divisions to Organizations (if org has multiple divisions)
ProgDiv             -- Links Programs to Divisions
OrganizationMembers -- People's involvement in organizations
OrganizationExtra   -- Custom fields for organizations

-- ATTENDANCE & MEETINGS
Attend              -- 2.6M+ records, attendance records (CHECK AttendanceFlag!)
Meetings            -- 61K+ records, scheduled gatherings
MeetingExtra        -- Custom meeting fields

-- FINANCIAL
Contribution        -- 687K+ records, donations (EXCLUDE TypeId=99)
ContributionFund    -- Fund designations
Transaction         -- Financial transactions
TransactionPeople   -- Links transactions to people

-- COMMUNICATION
EmailQueue          -- 3.9M+ records, email campaigns
EmailQueueTo        -- Recipients for emails
EmailResponses      -- Email opens/clicks
SMSList             -- Text messages sent
SmsReceived         -- Incoming texts

-- TASKS & NOTES
TaskNote            -- 174K+ records, ministry tasks and notes
TaskList            -- Task categories
TaskStatus          -- Task workflow states

-- TAGS & SEGMENTATION
Tag                 -- Tag definitions
TagPerson           -- 138M+ records! ALWAYS use date filters
TagShare            -- Shared tag permissions

-- REGISTRATION & EVENTS
Registration        -- Event registrations
RegPeople           -- People registered for events
RegAnswer           -- Form responses
RegSetting          -- Registration configuration

-- DATABASE VIEWS (107 total across 4 schemas)
-- Custom Schema (1 view)
custom.MissionTripTotals_Optimized    -- Mission trip financials

-- DBO Schema Views (82 views) - Key Examples:
ContributionsView          -- Enriched contribution data with donor info
OrganizationStructure      -- Complete org hierarchy with programs/divisions
AttendCredits             -- Weekly attendance credit calculations
CheckInActivities         -- Check-in activity with person names (denormalized)
EmailDelegation           -- Email delegation permissions
UserList                  -- User details with last activity
FailedEmails              -- Failed email tracking
MobileAppActions          -- Mobile app user actions
RecurringGivingDueForToday -- Today's expected recurring gifts
TasksAssigned             -- Task assignments and status
IncompleteTasks           -- Open tasks needing completion

-- Export Schema Views (23 views) - For Integration:
export.XpPeople           -- People data for export (text values not IDs)
export.XpFamily           -- Family data for export
export.XpOrganization     -- Organization data for export
export.XpAttend           -- Attendance data for export
export.XpContribution     -- Contribution data for export
export.XpTag              -- Tag assignments for export

-- Lookup Schema Views (1 view):
lookup.PushPayBundleHeaderTypes -- Pushpay integration bundle types

-- T-SQL BUILT-IN FUNCTIONS (No Custom Functions Allowed)
-- String Functions (20+)
CONCAT(), SUBSTRING()      -- String concatenation and extraction
LEFT(), RIGHT(), LEN()     -- String positioning functions
REPLACE(), STUFF()         -- String modification
UPPER(), LOWER(), TRIM()   -- Case and whitespace handling
STRING_AGG(), STRING_SPLIT() -- Row aggregation and splitting (SQL 2016+)
FORMAT(), QUOTENAME()      -- Formatting and SQL identifiers

-- Date & Time Functions (14+)
GETDATE(), DATEADD()      -- Current date and date arithmetic
DATEDIFF(), DATEPART()    -- Date differences and components
YEAR(), MONTH(), DAY()    -- Date part extraction
EOMONTH(), DATEFROMPARTS() -- End of month and date construction (SQL 2012+)

-- Mathematical Functions (15+)
SUM(), AVG(), COUNT()     -- Basic aggregates
MIN(), MAX()              -- Range functions
ROUND(), CEILING(), FLOOR() -- Rounding functions
STDEV(), VAR()            -- Statistical functions
ABS(), SIGN(), POWER()    -- Mathematical operations

-- Window Functions (12+)
ROW_NUMBER(), RANK()      -- Ranking functions
DENSE_RANK(), NTILE()     -- Distribution functions
LAG(), LEAD()             -- Row offset functions
FIRST_VALUE(), LAST_VALUE() -- Window boundary values
PERCENT_RANK(), CUME_DIST() -- Percentile functions

-- Conversion Functions (6+)
CAST(), CONVERT()         -- Type conversions
TRY_CAST(), TRY_CONVERT() -- Safe conversions (returns NULL on error)
PARSE(), TRY_PARSE()      -- Culture-aware parsing (SQL 2012+)

-- Logical Functions (6+)
CASE WHEN...THEN...END    -- Conditional logic
IIF(), CHOOSE()           -- Inline conditionals (SQL 2012+)
COALESCE(), ISNULL()      -- NULL handling
NULLIF()                  -- Return NULL if equal

-- TOUCHPOINT USER-DEFINED FUNCTIONS (337+ UDFs)
-- Most Critical UDFs by Category:

-- CONTRIBUTIONS (51 Functions)
Contributions2()          -- ⭐ MOST IMPORTANT: Handles spouse, excludes non-tax (7 params)
GetTotalContributions()   -- Person's total giving (2 params: PeopleId, optional date)
ContributionAmount()      -- Single contribution amount (3 params)
PledgeAmount()           -- Pledge total for person/fund (3 params)

-- PEOPLE & PROFILES (42 Functions)
Age()                    -- Calculate age from BDate (1 param: PeopleId)
GetSpouseId()           -- Get spouse PeopleId (1 param: PeopleId)
FamilyGreetingName()    -- Format family greeting (1 param: FamilyId)
DaysSinceContact()      -- Days since last contact (1 param: PeopleId)

-- ATTENDANCE (31 Functions)
LastAttend()            -- Last attendance date (2 params: orgid, peopleid - ORDER MATTERS!)
AttendancePercentage()  -- Attendance rate calculation (3 params)
RecentAttendance()      -- Recent attendance details (2 params)
AttendanceCredits()     -- Weekly attendance credits (2 params)

-- ORGANIZATIONS (29 Functions)
OrgPeopleCurrent()      -- Current org members (1 param: OrgId)
                        -- Returns: PeopleId, AttPct, LastAttended, MemberType, Groups, etc.
OrganizationLeaderName() -- Get leader name (1 param: OrgId)
InSmallGroup()          -- Check small group membership (2 params)

-- FORMATTING (8 Functions)
FmtPhone()              -- Format phone numbers (1 param: phone)
FmtZip()               -- Format zip codes (1 param: zip)

-- Function Types:
-- 179 Scalar Functions (return single value - slower in WHERE clauses)
-- 118 Inline Table-Valued (best performance, optimizer-friendly)
-- 35 Multi-Statement Table-Valued (moderate performance)
-- 5 CLR Functions (compiled .NET functions)

-- ⚠️ IMPORTANT UDF USAGE NOTES:
-- 1. Table-valued functions require FROM: SELECT * FROM dbo.GetTotalContributions(117, NULL)
-- 2. Birth date column is 'BDate' not 'BirthDate'
-- 3. LastAttend takes (orgid, peopleid) NOT (peopleid, orgid)
-- 4. Some functions require SET QUOTED_IDENTIFIER ON
-- 5. Use CROSS APPLY with table-valued functions for better performance

-- JSON Functions (SQL 2016+)
JSON_VALUE(), JSON_QUERY() -- Extract from JSON
ISJSON(), OPENJSON()      -- Validate and parse JSON
FOR JSON PATH/AUTO        -- Format results as JSON

-- System Functions (10+)
@@ROWCOUNT, @@IDENTITY    -- Session values
DB_NAME(), USER_NAME()    -- Database context
NEWID(), CHECKSUM()       -- GUIDs and checksums
ERROR_MESSAGE()           -- Error handling

🔗 Critical Table Relationships

-- User Authentication Chain
Users.PeopleId → People.PeopleId
Users.UserId → UserRole.UserId → Roles.RoleId

-- Organization Hierarchy
Program.Id → Division.ProgId → Organizations.DivisionId

-- Family Structure
People.FamilyId → Families.FamilyId
Families.HeadOfHouseholdId → People.PeopleId
Families.HeadOfHouseholdSpouseId → People.PeopleId

-- Attendance Chain
People.PeopleId → Attend.PeopleId
Attend.MeetingId → Meetings.MeetingId
Meetings.OrganizationId → Organizations.OrganizationId

-- Email System
EmailQueue.Id = EmailQueueTo.Id (direct match, not FK)
EmailQueueTo.PeopleId → People.PeopleId
EmailResponses.EmailQueueId → EmailQueue.Id

-- Tag System (Special!)
TagPerson.Id = Tag.Id (NOT TagPerson.TagId!)
TagPerson.PeopleId → People.PeopleId

🔧 Common JOIN Patterns

Organization Hierarchy Join

-- Complete hierarchy from Program to Organization
SELECT 
    p.Name as Program,
    d.Name as Division,
    o.OrganizationName,
    o.OrganizationId
FROM Organizations o
JOIN Division d ON o.DivisionId = d.Id
JOIN Program p ON d.ProgId = p.Id
WHERE o.OrganizationStatusId = 30  -- Active only

User to Person Join

-- Link system users to people records
SELECT 
    u.Username,
    p.Name,
    p.EmailAddress,
    r.RoleName
FROM Users u
JOIN People p ON u.PeopleId = p.PeopleId
LEFT JOIN UserRole ur ON u.UserId = ur.UserId
LEFT JOIN Roles r ON ur.RoleId = r.RoleId
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0

Family Members Join

-- Get all family members with relationships
-- Note: Families table does not have FamilyName column - use household names instead
SELECT 
    COALESCE(hoh.Name, '') + 
        CASE WHEN spouse.Name IS NOT NULL THEN ' & ' + spouse.Name ELSE '' END as HouseholdName,
    p.Name,
    fp.Description as FamilyPosition,
    CASE 
        WHEN p.PeopleId = f.HeadOfHouseholdId THEN 'Head'
        WHEN p.PeopleId = f.HeadOfHouseholdSpouseId THEN 'Spouse'
        ELSE 'Member'
    END as Role
FROM People p
JOIN Families f ON p.FamilyId = f.FamilyId
LEFT JOIN People hoh ON f.HeadOfHouseholdId = hoh.PeopleId
LEFT JOIN People spouse ON f.HeadOfHouseholdSpouseId = spouse.PeopleId
JOIN lookup.FamilyPosition fp ON p.PositionInFamilyId = fp.Id
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY HouseholdName, p.PositionInFamilyId

Attendance with Full Context

-- Attendance with complete organizational context
SELECT 
    p.Name,
    prog.Name as Program,
    div.Name as Division,
    o.OrganizationName,
    m.MeetingDate,
    at.Description as AttendType
FROM Attend a
JOIN People p ON a.PeopleId = p.PeopleId
JOIN Meetings m ON a.MeetingId = m.MeetingId
JOIN Organizations o ON m.OrganizationId = o.OrganizationId
JOIN Division div ON o.DivisionId = div.Id
JOIN Program prog ON div.ProgId = prog.Id
JOIN lookup.AttendType at ON a.AttendanceTypeId = at.Id
WHERE a.AttendanceFlag = 1  -- Present only
  AND m.DidNotMeet = 0      -- Meeting occurred
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0

Custom Fields (Extra Values) Join

-- Get people with their custom fields
SELECT 
    p.Name,
    pe.Field as CustomField,
    pe.Data as Value,
    pe.Type as DataType
FROM People p
LEFT JOIN PeopleExtra pe ON p.PeopleId = pe.PeopleId
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
  AND pe.Field IN ('Status', 'LifeStage', 'MembershipDate')
ORDER BY p.Name, pe.Field

🎯 Most Common Queries (Quick Reference)

👥 Active Church Members

SELECT TOP 1000 
    p.PeopleId,
    p.Name,
    p.EmailAddress,
    p.CellPhone,
    ms.Description as MemberStatus,
    DATEDIFF(year, p.BDate, GETDATE()) as Age,
    c.Description as Campus
FROM People p
JOIN lookup.MemberStatus ms ON p.MemberStatusId = ms.Id
LEFT JOIN lookup.Campus c ON p.CampusId = c.Id
WHERE p.IsDeceased = 0 
  AND p.ArchivedFlag = 0
  AND p.MemberStatusId = 10  -- Members only
ORDER BY p.Name

📅 Recent Attendance

SELECT TOP 500
    p.Name,
    o.OrganizationName,
    m.MeetingDate,
    at.Description as AttendanceType
FROM Attend a
JOIN People p ON a.PeopleId = p.PeopleId
JOIN Organizations o ON a.OrganizationId = o.OrganizationId
JOIN Meetings m ON a.MeetingId = m.MeetingId
JOIN lookup.AttendType at ON a.AttendanceTypeId = at.Id
WHERE a.AttendanceFlag = 1  -- Critical: actual attendance
  AND m.MeetingDate >= DATEADD(week, -1, GETDATE())
  AND m.DidNotMeet = 0
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY m.MeetingDate DESC, o.OrganizationName, p.Name

💰 Recent Giving

SELECT TOP 500
    p.Name,
    c.ContributionDate,
    c.ContributionAmount,
    cf.FundName,
    c.CheckNo
FROM Contribution c
JOIN People p ON c.PeopleId = p.PeopleId
JOIN ContributionFund cf ON c.FundId = cf.FundId
WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)  -- Exclude returned, reversed, pledges, event fees
  AND c.ContributionStatusId = 0  -- Posted only
  AND c.ContributionDate >= DATEADD(month, -1, GETDATE())
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY c.ContributionDate DESC, c.ContributionAmount DESC

💡 Using Temporary Tables for Complex Analysis

-- Example: Using temp tables to stage data for complex reporting
-- Temp tables are allowed in TouchPoint SQL and are very useful!

-- Create a temp table with attendance summary
SELECT 
    p.PeopleId,
    p.Name,
    COUNT(DISTINCT a.MeetingDate) as AttendanceCount,
    MAX(a.MeetingDate) as LastAttended
INTO #AttendanceSummary
FROM People p
JOIN Attend a ON p.PeopleId = a.PeopleId
WHERE a.MeetingDate >= DATEADD(month, -6, GETDATE())
GROUP BY p.PeopleId, p.Name

-- Create another temp table with giving summary
SELECT 
    p.PeopleId,
    SUM(c.ContributionAmount) as TotalGiving,
    COUNT(c.ContributionId) as GiftCount
INTO #GivingSummary
FROM People p
JOIN Contribution c ON p.PeopleId = c.PeopleId
WHERE c.ContributionDate >= DATEADD(month, -6, GETDATE())
  AND c.ContributionTypeId NOT IN (8, 99)
GROUP BY p.PeopleId

-- Now join the temp tables for final analysis
SELECT 
    a.Name,
    a.AttendanceCount,
    a.LastAttended,
    ISNULL(g.TotalGiving, 0) as TotalGiving,
    ISNULL(g.GiftCount, 0) as GiftCount,
    CASE 
        WHEN a.AttendanceCount > 10 AND g.TotalGiving > 500 THEN 'Highly Engaged'
        WHEN a.AttendanceCount > 5 OR g.TotalGiving > 100 THEN 'Engaged'
        ELSE 'New/Occasional'
    END as EngagementLevel
FROM #AttendanceSummary a
LEFT JOIN #GivingSummary g ON a.PeopleId = g.PeopleId
ORDER BY a.AttendanceCount DESC, g.TotalGiving DESC

-- Temp tables are automatically cleaned up when your session ends
-- Or you can manually drop them:
-- DROP TABLE #AttendanceSummary
-- DROP TABLE #GivingSummary

🆕 First-Time Visitors

-- Recent first-time visitors for follow-up
-- Note: AttendType values vary by church. Common values: 60=New Guest, 50=Recent Guest
-- Always check your lookup.AttendType table for actual values
WITH FirstVisit AS (
    SELECT 
        p.PeopleId,
        MIN(a.MeetingDate) as FirstVisitDate
    FROM Attend a
    JOIN People p ON a.PeopleId = p.PeopleId
    WHERE a.AttendanceFlag = 1
      AND a.AttendanceTypeId IN (50, 60)  -- Recent Guest, New Guest (verify your IDs)
    GROUP BY p.PeopleId
)
SELECT TOP 100
    p.Name,
    p.EmailAddress,
    p.CellPhone,
    fv.FirstVisitDate,
    DATEDIFF(day, fv.FirstVisitDate, GETDATE()) as DaysSinceVisit
FROM FirstVisit fv
JOIN People p ON fv.PeopleId = p.PeopleId
WHERE fv.FirstVisitDate >= DATEADD(week, -2, GETDATE())
  AND p.DoNotCallFlag = 0
  AND p.DoNotVisitFlag = 0
  AND p.IsDeceased = 0 
  AND p.ArchivedFlag = 0
ORDER BY fv.FirstVisitDate DESC

📊 Organization Summary

SELECT TOP 100
    o.OrganizationName,
    o.Location,
    leader.Name as Leader,
    o.MemberCount,
    o.Limit as Capacity,
    CASE 
        WHEN o.Limit > 0 THEN 
            CAST(o.MemberCount as float) / o.Limit * 100 
        ELSE 0 
    END as PercentFull,
    o.FirstMeetingDate,
    o.LastMeetingDate
FROM Organizations o
LEFT JOIN People leader ON o.LeaderId = leader.PeopleId
WHERE o.OrganizationStatusId = 30  -- Active
ORDER BY o.MemberCount DESC

👥 People Table - Complete Reference

Purpose: Central repository for all person and family data
Primary Key: PeopleId
Scale: 136 columns with 23 foreign key relationships
Critical Rule: Always filter WHERE IsDeceased = 0 AND ArchivedFlag = 0

Name Fields

Demographics

Family Relationships

Contact Information

Address Information

Church Membership

Communication Preferences

System & Status Fields

Mobile & Check-in

📚 Lookup Tables Reference

TouchPoint includes 67+ lookup tables that provide business logic, validation rules, and categorization throughout the system.

Complete List of All 67 Lookup Tables

Query All Lookup Tables

-- Get all lookup tables with record counts
SELECT
    t.TABLE_NAME,
    (SELECT COUNT(*)
     FROM lookup.[' + t.TABLE_NAME + ']) as RecordCount
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_SCHEMA = 'lookup'
ORDER BY t.TABLE_NAME;

-- Query any specific lookup table
SELECT * FROM lookup.MemberStatus ORDER BY Id;
SELECT * FROM lookup.AttendType ORDER BY Id;
SELECT * FROM lookup.ContributionType ORDER BY Id;

Critical Lookup Tables - Detailed Reference

lookup.MemberStatus Most Important

lookup.AttendType Important - Values Vary by Church

lookup.FamilyPosition

lookup.ContributionType Important for Reporting

Note: Your church may have different contribution types. Query SELECT * FROM lookup.ContributionType to see your specific values. Types like Cash, Check, Credit Card are typically NOT in this table but may be tracked elsewhere.

Other Important Lookup Tables

-- Common lookup tables and their purposes
SELECT 'Gender' as LookupTable, Id, Description FROM lookup.Gender
UNION ALL
SELECT 'MaritalStatus', Id, Description FROM lookup.MaritalStatus
UNION ALL
SELECT 'Campus', Id, Description FROM lookup.Campus
UNION ALL
SELECT 'OrganizationStatus', Id, Description FROM lookup.OrganizationStatus
UNION ALL
SELECT 'ContributionType', Id, Description FROM lookup.ContributionType
UNION ALL
SELECT 'BaptismStatus', Id, Description FROM lookup.BaptismStatus
UNION ALL
SELECT 'DecisionType', Id, Description FROM lookup.DecisionType
UNION ALL
SELECT 'DropType', Id, Description FROM lookup.DropType
ORDER BY LookupTable, Id

📈 Common Report Patterns

Church Dashboard Metrics

-- Executive Dashboard Query
WITH Metrics AS (
    SELECT 
        -- People Metrics
        (SELECT COUNT(*) FROM People WHERE IsDeceased = 0 AND ArchivedFlag = 0 AND MemberStatusId = 10) as ActiveMembers,
        (SELECT COUNT(*) FROM People WHERE IsDeceased = 0 AND ArchivedFlag = 0 AND MemberStatusId = 20) as Prospects,
        
        -- Attendance Metrics (Last Week)
        (SELECT COUNT(DISTINCT a.PeopleId) 
         FROM Attend a 
         WHERE a.AttendanceFlag = 1 
           AND a.MeetingDate >= DATEADD(week, -1, GETDATE())) as WeeklyAttendance,
        
        -- Attendance Metrics (Last Month)
        (SELECT COUNT(DISTINCT a.PeopleId) 
         FROM Attend a 
         WHERE a.AttendanceFlag = 1 
           AND a.MeetingDate >= DATEADD(month, -1, GETDATE())) as MonthlyAttendance,
        
        -- Financial Metrics
        (SELECT SUM(ContributionAmount)
         FROM Contribution
         WHERE ContributionTypeId NOT IN (6, 7, 8, 99)
           AND ContributionStatusId = 0
           AND ContributionDate >= DATEADD(month, -1, GETDATE())) as MonthlyGiving,

        (SELECT COUNT(DISTINCT PeopleId)
         FROM Contribution
         WHERE ContributionTypeId NOT IN (6, 7, 8, 99)
           AND ContributionStatusId = 0
           AND ContributionDate >= DATEADD(month, -1, GETDATE())) as MonthlyGivers,
        
        -- Organization Metrics
        (SELECT COUNT(*) FROM Organizations WHERE OrganizationStatusId = 30) as ActiveOrganizations,
        
        -- New People
        (SELECT COUNT(*) 
         FROM People 
         WHERE CreatedDate >= DATEADD(month, -1, GETDATE()) 
           AND IsDeceased = 0 AND ArchivedFlag = 0) as NewPeopleThisMonth
)
SELECT * FROM Metrics

Attendance Trends Report

-- Weekly attendance trends over 12 weeks
WITH WeeklyAttendance AS (
    SELECT 
        DATEPART(year, a.MeetingDate) as Year,
        DATEPART(week, a.MeetingDate) as Week,
        MIN(CAST(a.MeetingDate as DATE)) as WeekStart,
        COUNT(DISTINCT a.PeopleId) as UniqueAttenders,
        COUNT(*) as TotalAttendances,
        COUNT(DISTINCT a.OrganizationId) as OrgsWithMeetings,
        COUNT(DISTINCT CASE WHEN at.Guest = 1 THEN a.PeopleId END) as Guests,
        COUNT(DISTINCT CASE WHEN at.Worker = 1 THEN a.PeopleId END) as Workers
    FROM Attend a
    JOIN lookup.AttendType at ON a.AttendanceTypeId = at.Id
    WHERE a.AttendanceFlag = 1
      AND a.MeetingDate >= DATEADD(week, -12, GETDATE())
    GROUP BY DATEPART(year, a.MeetingDate), DATEPART(week, a.MeetingDate)
)
SELECT 
    WeekStart,
    UniqueAttenders,
    Guests,
    Workers,
    UniqueAttenders - Guests - Workers as Members,
    TotalAttendances,
    OrgsWithMeetings,
    CAST(TotalAttendances as float) / NULLIF(UniqueAttenders, 0) as AvgAttendancesPerPerson
FROM WeeklyAttendance
ORDER BY WeekStart DESC

Giving Analysis Report

-- Comprehensive giving analysis
WITH GivingStats AS (
    SELECT 
        p.PeopleId,
        p.Name,
        COUNT(*) as GiftCount,
        SUM(c.ContributionAmount) as TotalGiven,
        MIN(c.ContributionDate) as FirstGift,
        MAX(c.ContributionDate) as LastGift,
        DATEDIFF(day, MAX(c.ContributionDate), GETDATE()) as DaysSinceLastGift
    FROM Contribution c
    JOIN People p ON c.PeopleId = p.PeopleId
    WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)
      AND c.ContributionStatusId = 0
      AND c.ContributionDate >= DATEADD(year, -1, GETDATE())
      AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
    GROUP BY p.PeopleId, p.Name
)
SELECT
    CASE
        WHEN TotalGiven >= 10000 THEN 'Major Donor ($10K+)'
        WHEN TotalGiven >= 5000 THEN 'Significant Donor ($5K-$10K)'
        WHEN TotalGiven >= 1000 THEN 'Regular Donor ($1K-$5K)'
        WHEN TotalGiven >= 100 THEN 'Small Donor ($100-$1K)'
        ELSE 'Micro Donor (<$100)'
    END as DonorLevel,
    COUNT(*) as DonorCount,
    SUM(TotalGiven) as TotalFromLevel,
    AVG(TotalGiven) as AvgGiftPerDonor,
    AVG(GiftCount) as AvgGiftsPerDonor
FROM GivingStats
GROUP BY 
    CASE 
        WHEN TotalGiven >= 10000 THEN 'Major Donor ($10K+)'
        WHEN TotalGiven >= 5000 THEN 'Significant Donor ($5K-$10K)'
        WHEN TotalGiven >= 1000 THEN 'Regular Donor ($1K-$5K)'
        WHEN TotalGiven >= 100 THEN 'Small Donor ($100-$1K)'
        ELSE 'Micro Donor (<$100)'
    END
ORDER BY MIN(TotalGiven) DESC

Group Health Report

-- Organization/group health metrics
SELECT TOP 500
    o.OrganizationName,
    o.Location,
    leader.Name as Leader,
    o.MemberCount,
    o.Limit as Capacity,
    
    -- Capacity Metrics
    CASE 
        WHEN o.Limit > 0 THEN CAST(o.MemberCount as float) / o.Limit * 100 
        ELSE NULL 
    END as PercentFull,
    
    -- Meeting Metrics
    (SELECT COUNT(*) 
     FROM Meetings m 
     WHERE m.OrganizationId = o.OrganizationId 
       AND m.MeetingDate >= DATEADD(month, -3, GETDATE())
       AND m.DidNotMeet = 0) as MeetingsLast3Months,
    
    -- Attendance Metrics
    (SELECT AVG(CAST(NumPresent as float))
     FROM Meetings m 
     WHERE m.OrganizationId = o.OrganizationId 
       AND m.MeetingDate >= DATEADD(month, -3, GETDATE())
       AND m.DidNotMeet = 0) as AvgAttendance,
    
    -- Engagement
    (SELECT AVG(om.AttendPct)
     FROM OrganizationMembers om
     WHERE om.OrganizationId = o.OrganizationId
       AND om.InactiveDate IS NULL) as AvgMemberEngagement,
    
    -- Growth
    (SELECT COUNT(*)
     FROM OrganizationMembers om
     WHERE om.OrganizationId = o.OrganizationId
       AND om.EnrollmentDate >= DATEADD(month, -3, GETDATE())) as NewMembersLast3Months
    
FROM Organizations o
LEFT JOIN People leader ON o.LeaderId = leader.PeopleId
WHERE o.OrganizationStatusId = 30
ORDER BY o.MemberCount DESC

🔧 Common Issues & Solutions

Issue: Query Times Out

Issue: Attendance Numbers Don't Match UI

WHERE a.AttendanceFlag = 1  -- Only count actual attendance
  AND m.DidNotMeet = 0      -- Exclude cancelled meetings
  AND p.IsDeceased = 0      -- Exclude deceased
  AND p.ArchivedFlag = 0    -- Exclude archived

Issue: Giving Reports Include Non-Donation Transactions

WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)  -- Exclude returned checks, reversed, pledges, event fees
  AND c.ContributionStatusId = 0                  -- Posted contributions only

Issue: Deceased People Appearing

WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0

Issue: Organizations Show as Inactive

WHERE o.OrganizationStatusId = 30  -- NOT 1!

Issue: Can't Find Custom Fields

-- Discover available custom fields
SELECT DISTINCT Field, COUNT(*) as Uses
FROM PeopleExtra
GROUP BY Field
ORDER BY COUNT(*) DESC

💰 Active Records Billing

What Makes a Record "Active"?

A person is "active" if they meet EITHER of these criteria within the past 365 days:

Quick Active Records Count Query

-- Count current active records
WITH HouseholdsWithGiving AS (
    SELECT DISTINCT p.FamilyId
    FROM Contribution c WITH (NOLOCK)
    JOIN People p WITH (NOLOCK) ON c.PeopleId = p.PeopleId
    WHERE c.ContributionDate >= DATEADD(year, -1, GETDATE())
        AND c.ContributionStatusId = 0
        AND c.ContributionTypeId NOT IN (6, 7, 8, 99)
),
PeopleWithActivity AS (
    SELECT a.PeopleId
    FROM Attend a WITH (NOLOCK)
    JOIN Meetings m WITH (NOLOCK) ON a.MeetingId = m.MeetingId
    WHERE m.MeetingDate >= DATEADD(year, -1, GETDATE())
        AND a.AttendanceFlag = 1
    GROUP BY a.PeopleId
    HAVING COUNT(*) >= 2

    UNION

    SELECT PeopleId
    FROM EnrollmentTransaction WITH (NOLOCK)
    WHERE TransactionDate >= DATEADD(year, -1, GETDATE())
    GROUP BY PeopleId
    HAVING COUNT(*) >= 2
)
SELECT
    COUNT(DISTINCT p.PeopleId) as ActiveRecords
FROM People p WITH (NOLOCK)
LEFT JOIN HouseholdsWithGiving hwg ON p.FamilyId = hwg.FamilyId
LEFT JOIN PeopleWithActivity pwa ON p.PeopleId = pwa.PeopleId
WHERE p.IsDeceased = 0
    AND p.ArchivedFlag = 0
    AND (hwg.FamilyId IS NOT NULL OR pwa.PeopleId IS NOT NULL)

Key Insights

👥 SubGroups System (MemberTags)

SubGroups allow organizations to create smaller groups within an organization (e.g., "Blue Team", "Red Team" within a serving team).

Tables

MemberTags (SubGroup Definitions)

OrgMemMemTags (SubGroup Membership)

SubGroup Queries

-- List all subgroups and their members for an organization
SELECT
    mt.Name as SubGroup,
    p.Name as PersonName,
    ommt.IsLeader,
    om.MemberTypeId
FROM MemberTags mt
JOIN OrgMemMemTags ommt ON mt.Id = ommt.MemberTagId
JOIN People p ON ommt.PeopleId = p.PeopleId
LEFT JOIN OrganizationMembers om
    ON ommt.PeopleId = om.PeopleId
    AND ommt.OrgId = om.OrganizationId
WHERE mt.OrgId = 123  -- Your organization ID
    AND om.InactiveDate IS NULL  -- Active members only
ORDER BY mt.Name, p.Name

-- Find people in a specific subgroup
SELECT p.Name, p.EmailAddress
FROM OrgMemMemTags ommt
JOIN MemberTags mt ON ommt.MemberTagId = mt.Id
JOIN People p ON ommt.PeopleId = p.PeopleId
WHERE mt.OrgId = 123
  AND mt.Name = 'Blue Team'  -- Match name exactly as stored

-- Count members per subgroup across all active orgs
SELECT
    o.OrganizationName,
    mt.Name as SubGroup,
    COUNT(DISTINCT ommt.PeopleId) as MemberCount,
    SUM(CASE WHEN ommt.IsLeader = 1 THEN 1 ELSE 0 END) as LeaderCount
FROM MemberTags mt
JOIN OrgMemMemTags ommt ON mt.Id = ommt.MemberTagId
JOIN Organizations o ON mt.OrgId = o.OrganizationId
WHERE o.OrganizationStatusId = 30
GROUP BY o.OrganizationName, mt.Name
ORDER BY o.OrganizationName, mt.Name

🛡️ Background Checks & Volunteer Compliance

BackgroundChecks Table

Volunteer Table

Compliance Queries

-- Find volunteers with expiring background checks (3-year validity)
SELECT
    p.Name,
    p.EmailAddress,
    bc.Updated as LastCheckDate,
    DATEADD(YEAR, 3, bc.Updated) as ExpirationDate,
    DATEDIFF(day, GETDATE(), DATEADD(YEAR, 3, bc.Updated)) as DaysUntilExpiry,
    CASE
        WHEN DATEADD(YEAR, 3, bc.Updated) <= GETDATE() THEN 'EXPIRED'
        WHEN DATEADD(YEAR, 3, bc.Updated) <= DATEADD(day, 90, GETDATE()) THEN 'EXPIRING SOON'
        ELSE 'Valid'
    END as BGStatus
FROM BackgroundChecks bc
JOIN People p ON bc.PeopleId = p.PeopleId
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY ExpirationDate ASC

-- Volunteers serving with children's ministry
SELECT
    p.Name,
    v.ProcessedDate,
    vs.Description as ApplicationStatus,
    v.Children as ChildrenCleared,
    v.Leader as LeaderCleared
FROM Volunteer v
JOIN People p ON v.PeopleId = p.PeopleId
LEFT JOIN lookup.VolApplicationStatus vs ON v.StatusId = vs.Id
WHERE v.Children = 1  -- Cleared for children's ministry
ORDER BY v.ProcessedDate DESC

✅ Best Practices & Tips

🎯 Query Development Process

1. Start Small: Use TOP 10 to test logic
2. Add Filters Early: Apply mandatory filters immediately
3. Test Incrementally: Build complex queries step by step
4. Check Performance: Monitor execution time
5. Document Assumptions: Comment your business logic

📋 Mandatory Filter Checklist

-- Copy this template for every query
WHERE 1=1
  -- People filters
  AND p.IsDeceased = 0 
  AND p.ArchivedFlag = 0
  
  -- Attendance filters
  AND a.AttendanceFlag = 1
  AND m.DidNotMeet = 0
  
  -- Organization filters
  AND o.OrganizationStatusId = 30
  AND om.InactiveDate IS NULL
  
  -- Contribution filters
  AND c.ContributionTypeId NOT IN (6, 7, 8, 99)
  AND c.ContributionStatusId = 0

  -- Performance filters
  AND DateField >= DATEADD(month, -3, GETDATE())

🚀 Performance Optimization

📝 Documentation Standards

-- Query: Monthly Giving Summary
-- Purpose: Summarize giving by fund for board report
-- Author: [Your Name]
-- Date: [Current Date]
-- Performance: ~2 seconds on production
-- Dependencies: Contribution, ContributionFund, People tables
-- Business Rules:
--   - Excludes returned checks (6), reversed (7), pledges (8), event fees (99)
--   - Only includes posted contributions (StatusId = 0)
--   - Groups by calendar month

SELECT TOP 500
    /* Month grouping */
    YEAR(c.ContributionDate) as GiftYear,
    MONTH(c.ContributionDate) as GiftMonth,

    /* Fund analysis */
    cf.FundName,
    COUNT(DISTINCT c.PeopleId) as UniqueGivers,
    SUM(c.ContributionAmount) as TotalAmount

FROM Contribution c
JOIN ContributionFund cf ON c.FundId = cf.FundId
WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)  -- Exclude non-donations
  AND c.ContributionStatusId = 0                  -- Posted only
  AND c.ContributionDate >= '2024-01-01'
GROUP BY 
    YEAR(c.ContributionDate),
    MONTH(c.ContributionDate),
    cf.FundId,
    cf.FundName
ORDER BY 
    GiftYear DESC,
    GiftMonth DESC,
    TotalAmount DESC

🔗 Essential Table Relationships

Core Relationships

People (PeopleId) ←─────────────────────────┐
    │                                        │
    ├──→ Families (FamilyId)                 │
    │    Groups family members               │
    │                                        │
    ├──→ People (SpouseId)                   │
    │    Direct spouse link                  │
    │                                        │
    ├──→ OrganizationMembers ←───────────────┤
    │    │                                   │
    │    └──→ Organizations (OrganizationId) │
    │         │                              │
    │         ├──→ Meetings (MeetingId) ←────┤
    │         │                              │
    │         └──→ Attend ←──────────────────┘
    │              (PeopleId + MeetingId + OrganizationId)
    │
    ├──→ Contribution (ContributionId)
    │    │
    │    └──→ ContributionFund (FundId)
    │
    ├──→ Registration (RegistrationId)
    │    │
    │    └──→ RegPeople (RegPeopleId)
    │         │
    │         └──→ RegAnswer (RegAnswerId)
    │              │
    │              └──→ RegQuestion (RegQuestionId)
    │
    ├──→ TaskNote (TaskNoteId)
    │    About person tasks/notes
    │
    ├──→ EmailQueueTo (EmailQueueToId)
    │    │
    │    └──→ EmailResponses (Event tracking)
    │
    └──→ TagPerson (138M+ records!)
         │
         └──→ Tag (TagId)

Lookup Relationships

People Table Lookups:
    ├──→ lookup.MemberStatus (MemberStatusId)
    ├──→ lookup.Gender (GenderId)
    ├──→ lookup.MaritalStatus (MaritalStatusId)
    ├──→ lookup.FamilyPosition (PositionInFamilyId)
    ├──→ lookup.Campus (CampusId)
    ├──→ lookup.EntryPoint (EntryPointId)
    ├──→ lookup.BaptismStatus (BaptismStatusId)
    └──→ lookup.DropType (DropCodeId)

Organization Table Lookups:
    ├──→ lookup.OrganizationStatus (OrganizationStatusId = 30 for Active)
    ├──→ lookup.OrganizationType (OrganizationTypeId)
    ├──→ lookup.Campus (CampusId)
    └──→ Division → Program (Hierarchy)

Attendance Lookups:
    ├──→ lookup.AttendType (AttendanceTypeId)
    └──→ lookup.MemberType (MemberTypeId)
         └──→ lookup.AttendType (AttendanceTypeId)

Common Query Patterns with Lookup Tables

-- Join lookup TABLES (not views) for human-readable values
-- NOTE: These are TABLES in the lookup schema, not views
SELECT
    p.PeopleId,
    p.Name2,
    p.EmailAddress,
    ms.Description as MemberStatus,  -- From lookup TABLE
    g.Description as Gender,          -- From lookup TABLE
    c.Description as Campus           -- From lookup TABLE
FROM People p
LEFT JOIN lookup.MemberStatus ms ON p.MemberStatusId = ms.Id
LEFT JOIN lookup.Gender g ON p.GenderId = g.Id
LEFT JOIN lookup.Campus c ON p.CampusId = c.Id
WHERE p.IsDeceased = 0
    AND p.ArchivedFlag = 0;

-- Use lookup tables for complete organization details
-- NOTE: Organization -> Division -> Program hierarchy
SELECT
    o.OrganizationId,
    o.OrganizationName,
    p.Name as ProgramName,
    d.Name as DivisionName,
    os.Description as Status,        -- From lookup TABLE
    ot.Description as OrgType,        -- From lookup TABLE
    c.Description as Campus,          -- From lookup TABLE
    mt.Description as LeaderType      -- From lookup TABLE
FROM Organizations o
LEFT JOIN Division d ON o.DivisionId = d.Id  -- Division first
LEFT JOIN Program p ON d.ProgId = p.Id       -- Then Program through Division
LEFT JOIN lookup.OrganizationStatus os ON o.OrganizationStatusId = os.Id
LEFT JOIN lookup.OrganizationType ot ON o.OrganizationTypeId = ot.Id
LEFT JOIN lookup.Campus c ON o.CampusId = c.Id
LEFT JOIN lookup.MemberType mt ON o.LeaderMemberTypeId = mt.Id
WHERE o.OrganizationStatusId = 30;  -- Active organizations

-- Volunteer status tracking using lookup tables
-- Note: Volunteer table has StatusId, MVRStatusId, and TrainingStatusId
SELECT
    p.Name2 as VolunteerName,
    v.ProcessedDate,
    vs.Description as ApplicationStatus,     -- From lookup TABLE
    mvrs.Description as MVRStatus,           -- From lookup TABLE
    ts.Description as TrainingStatus,        -- From lookup TABLE
    ms.Description as MemberStatus,          -- From lookup TABLE
    v.Standard,
    v.Children,
    v.Leader
FROM Volunteer v
JOIN People p ON v.PeopleId = p.PeopleId
LEFT JOIN lookup.VolApplicationStatus vs ON v.StatusId = vs.Id
LEFT JOIN lookup.VolApplicationStatus mvrs ON v.MVRStatusId = mvrs.Id
LEFT JOIN lookup.VolApplicationStatus ts ON v.TrainingStatusId = ts.Id
LEFT JOIN lookup.MemberStatus ms ON p.MemberStatusId = ms.Id
WHERE v.StatusId > 0
ORDER BY v.ProcessedDate DESC;

🔍 Database Views Documentation

TouchPoint uses SQL Server views to provide simplified access to complex data relationships. Views are virtual tables that combine data from multiple tables through joins and can include calculated fields.

View Discovery Queries

-- Discover all available views in your TouchPoint database
SELECT
    v.TABLE_SCHEMA,
    v.TABLE_NAME,
    'VIEW' as OBJECT_TYPE,
    COUNT(c.COLUMN_NAME) as COLUMN_COUNT
FROM INFORMATION_SCHEMA.VIEWS v
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
    ON v.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND v.TABLE_NAME = c.TABLE_NAME
WHERE v.TABLE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA')
GROUP BY v.TABLE_SCHEMA, v.TABLE_NAME
ORDER BY v.TABLE_NAME;

-- Get detailed column information for a specific view
SELECT
    c.COLUMN_NAME,
    c.DATA_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH,
    c.IS_NULLABLE,
    c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'lookup'  -- Change schema name
    AND c.TABLE_NAME = 'MemberStatus'  -- Change view name
ORDER BY c.ORDINAL_POSITION;

📊 Complete List of All TouchPoint Database Views (107 Views)

SET QUOTED_IDENTIFIER ON;

📂 TouchPoint Views by Category

👥 People & Contact Views

💰 Financial & Giving Views

📅 Attendance & Activities Views

🏢 Organization Views

📝 Registration Views

✅ Task & Status Views

🔧 System & Admin Views

📧 Communication Views

📊 Analytics & Reporting Views

📝 Using Views in Your Queries

-- Use OrganizationStructure for hierarchy and member counts
-- NOTE: Column is misspelled as 'Vistors' in the view (missing 'i')
SELECT
    Program,
    Division,
    Organization,
    OrgStatus,
    Members,
    Previous,
    Vistors,  -- Note: typo in actual view column name
    Meetings,
    CAST(ISNULL(Vistors * 100.0 / NULLIF(Members, 0), 0) as DECIMAL(10,2)) as VisitorPercent
FROM OrganizationStructure
WHERE ProgId = 1  -- Specific program
    AND Members > 0
ORDER BY Program, Division, Organization;

-- Group summary by division
SELECT
    Program,
    Division,
    COUNT(DISTINCT OrgId) as OrgCount,
    SUM(Members) as TotalMembers,
    SUM(Vistors) as TotalVisitors,  -- Note: typo in column name
    SUM(Meetings) as TotalMeetings
FROM OrganizationStructure
WHERE Members > 0
GROUP BY ProgId, Program, DivId, Division
ORDER BY Program, Division;

-- Get outstanding balances by person
SELECT
    ts.PeopleId,
    p.Name2,
    ts.OrganizationId,
    o.OrganizationName,
    ts.TotDue,
    ts.TotPaid,
    (ts.TotDue - ts.TotPaid) as Balance,
    ts.TranDate as LastTransaction
FROM TransactionSummary ts
JOIN People p ON ts.PeopleId = p.PeopleId
JOIN Organizations o ON ts.OrganizationId = o.OrganizationId
WHERE ts.IsLatestTransaction = 1
    AND (ts.TotDue - ts.TotPaid) > 0  -- Outstanding balance
ORDER BY Balance DESC;

-- Mission trip financial analysis
-- NOTE: GoerSenderAmounts view doesn't exist - use MissionTripTotals instead
SET QUOTED_IDENTIFIER ON;  -- Required for this view
SELECT
    m.InvolvementId,
    m.Trip,
    m.PeopleId,
    m.Name,
    m.TripCost,
    m.Raised,
    m.Due,
    CAST(ISNULL(m.Raised * 100.0 / NULLIF(m.TripCost, 0), 0) as DECIMAL(10,2)) as PercentRaised
FROM MissionTripTotals m
WHERE m.PeopleId IS NOT NULL  -- Exclude undesignated funds
    AND m.TripCost > 0
ORDER BY m.Due DESC;

-- First and Last Attendance Analysis
-- Note: Views already include person names and org details
SELECT
    p.PeopleId,
    p.Name2,
    fa.OrganizationName,
    fa.FirstAttend,
    la.LastAttend,
    DATEDIFF(day, fa.FirstAttend, la.LastAttend) as DaysAttending,
    DATEDIFF(day, la.LastAttend, GETDATE()) as DaysSinceLastAttend,
    la.EmailAddress,
    la.CellPhone
FROM People p
LEFT JOIN (
    SELECT PeopleId, MIN(FirstAttend) as FirstAttend,
           MAX(OrganizationName) as OrganizationName
    FROM FirstAttends
    GROUP BY PeopleId
) fa ON p.PeopleId = fa.PeopleId
LEFT JOIN (
    SELECT PeopleId, MAX(LastAttend) as LastAttend,
           MAX(EmailAddress) as EmailAddress,
           MAX(CellPhone) as CellPhone
    FROM LastAttends
    GROUP BY PeopleId
) la ON p.PeopleId = la.PeopleId
WHERE p.IsDeceased = 0
    AND p.ArchivedFlag = 0
    AND la.LastAttend >= DATEADD(year, -1, GETDATE())
ORDER BY DaysSinceLastAttend;

-- Organization Member Count Comparison
-- Note: PreviousMemberCounts only has OrganizationId and prevcount
SELECT TOP 100
    os.Organization,
    pmc.prevcount as PreviousCount,
    os.Members as CurrentCount,
    (os.Members - pmc.prevcount) as Change,
    CAST(ISNULL((os.Members - pmc.prevcount) * 100.0 / NULLIF(pmc.prevcount, 0), 0) as DECIMAL(10,2)) as PercentChange
FROM PreviousMemberCounts pmc
JOIN OrganizationStructure os ON pmc.OrganizationId = os.OrgId
WHERE pmc.prevcount > 0
    AND os.Members IS NOT NULL
ORDER BY ABS(os.Members - pmc.prevcount) DESC;

Performance Considerations for Views

-- GOOD: Filtered view query with TOP
SELECT TOP 1000 *
FROM MyComplexView WITH (NOLOCK)
WHERE DateColumn >= DATEADD(month, -1, GETDATE())
ORDER BY DateColumn DESC;

-- BAD: Unfiltered view query (may timeout)
SELECT * FROM MyComplexView;

-- GOOD: Cache lookup values for repeated use
DECLARE @MemberStatusLookup TABLE (
    Id INT,
    Description NVARCHAR(100)
);
INSERT INTO @MemberStatusLookup
SELECT Id, Description FROM lookup.MemberStatus;

Simulating Views with Temporary Tables

-- Create a temporary table to simulate a view for active adult members
-- Temp tables exist only for your current session
SELECT
    p.PeopleId,
    p.Name2 as FullName,
    p.EmailAddress,
    p.CellPhone,
    p.Age,
    ms.Description as MemberStatus,
    fp.Description as FamilyPosition,
    c.Description as Campus,
    la.LastAttendDate,
    DATEDIFF(day, la.LastAttendDate, GETDATE()) as DaysSinceAttended
INTO #ActiveAdultMembers  -- Creates temporary table
FROM People p
LEFT JOIN lookup.MemberStatus ms ON p.MemberStatusId = ms.Id
LEFT JOIN lookup.FamilyPosition fp ON p.PositionInFamilyId = fp.Id
LEFT JOIN lookup.Campus c ON p.CampusId = c.Id
LEFT JOIN (
    -- Get last attendance date from Attend table
    SELECT PeopleId, MAX(a.MeetingDate) as LastAttendDate
    FROM Attend a
    JOIN Meetings m ON a.MeetingId = m.MeetingId
    WHERE a.AttendanceFlag = 1
    GROUP BY PeopleId
) la ON p.PeopleId = la.PeopleId
WHERE p.IsDeceased = 0
    AND p.ArchivedFlag = 0
    AND p.MemberStatusId = 10  -- Members only
    AND p.Age >= 18             -- Adults only
    AND p.PositionInFamilyId IN (10, 20);  -- Primary/Secondary adults

-- Now use the temp table like a view
SELECT * FROM #ActiveAdultMembers
WHERE DaysSinceAttended <= 30
ORDER BY DaysSinceAttended;

-- Temp table automatically drops when session ends

🧮 T-SQL Functions in TouchPoint (Read-Only Environment)

📚 Complete T-SQL Function Reference

Built-in Scalar Functions

1. String Functions

-- Common string manipulation functions
SELECT
    PeopleId,
    Name,
    -- Remove extra spaces
    LTRIM(RTRIM(Name)) as CleanName,

    -- Extract parts (using actual column names)
    LEFT(CellPhone, 3) as AreaCode,
    RIGHT(ZipCode, 4) as Plus4,
    SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress) + 1, 100) as EmailDomain,

    -- Transform case (using actual column names)
    UPPER(CityName) as CityUpper,
    LOWER(EmailAddress) as EmailLower,

    -- Replace and format
    REPLACE(HomePhone, '-', '') as PhoneDigitsOnly,
    CONCAT(FirstName, ' ', LastName) as FullName,

    -- Pattern matching
    CASE
        WHEN EmailAddress LIKE '%gmail.com' THEN 'Gmail'
        WHEN EmailAddress LIKE '%yahoo.com' THEN 'Yahoo'
        WHEN EmailAddress LIKE '%outlook.com' THEN 'Outlook'
        ELSE 'Other'
    END as EmailProvider
FROM People
WHERE IsDeceased = 0;

-- String aggregation (SQL 2017+) - Cannot use OVER clause with STRING_AGG
-- Must use GROUP BY instead
SELECT
    FamilyId,
    STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) as FamilyMembers,
    COUNT(*) as FamilyMemberCount
FROM People
WHERE FamilyId IS NOT NULL
    AND IsDeceased = 0
GROUP BY FamilyId
HAVING COUNT(*) > 1;

2. Date Functions

-- Date manipulation and calculations
SELECT
    PeopleId,
    Name,
    BDate,  -- TouchPoint uses BDate, not BirthDate

    -- Age calculations
    DATEDIFF(year, BDate, GETDATE()) as AgeInYears,
    DATEDIFF(month, BDate, GETDATE()) / 12 as ExactAge,

    -- Date parts
    YEAR(BDate) as BirthYear,
    MONTH(BDate) as BirthMonth,
    DATENAME(month, BDate) as BirthMonthName,
    DATEPART(week, BDate) as BirthWeek,
    DATENAME(weekday, BDate) as BirthDayOfWeek,

    -- Date formatting
    FORMAT(BDate, 'MMM dd, yyyy') as FormattedBirthDate,
    CONVERT(VARCHAR(10), BDate, 101) as USDateFormat,

    -- Date calculations
    DATEADD(year, 1, BDate) as FirstBirthday,
    EOMONTH(BDate) as EndOfBirthMonth,

    -- Upcoming birthday
    CASE
        WHEN DATEPART(dayofyear, BDate) >= DATEPART(dayofyear, GETDATE())
        THEN DATEDIFF(day, GETDATE(),
            DATEADD(year, YEAR(GETDATE()) - YEAR(BDate), BDate))
        ELSE DATEDIFF(day, GETDATE(),
            DATEADD(year, YEAR(GETDATE()) - YEAR(BDate) + 1, BDate))
    END as DaysUntilBirthday
FROM People
WHERE BDate IS NOT NULL;

3. Mathematical Functions

-- Mathematical calculations for reporting
SELECT
    -- Giving statistics
    PeopleId,
    COUNT(*) as GiftCount,
    SUM(ContributionAmount) as TotalGiving,
    AVG(ContributionAmount) as AverageGift,
    MIN(ContributionAmount) as SmallestGift,
    MAX(ContributionAmount) as LargestGift,
    STDEV(ContributionAmount) as GivingStdDev,

    -- Rounding and formatting
    ROUND(AVG(ContributionAmount), 2) as RoundedAverage,
    CEILING(AVG(ContributionAmount)) as RoundUp,
    FLOOR(AVG(ContributionAmount)) as RoundDown,

    -- Percentage calculations
    CAST(SUM(ContributionAmount) * 100.0 /
        SUM(SUM(ContributionAmount)) OVER() as DECIMAL(5,2)) as PercentOfTotal,

    -- Quartile calculations using NTILE
    NTILE(4) OVER (ORDER BY SUM(ContributionAmount)) as GivingQuartile
FROM Contribution
WHERE ContributionDate >= '2024-01-01'
    AND ContributionTypeId NOT IN (6, 7, 8, 99)
GROUP BY PeopleId;

4. Conversion Functions

-- Type conversion examples
SELECT
    -- String to number conversions
    CAST('123' as INT) as StringToInt,
    CONVERT(DECIMAL(10,2), '123.45') as StringToDecimal,
    TRY_CAST('bad data' as INT) as SafeCast, -- Returns NULL instead of error
    TRY_CONVERT(DATE, '2024-13-45') as SafeDateConvert,

    -- Number to string formatting
    CAST(12345.67 as VARCHAR(20)) as NumberToString,
    FORMAT(12345.67, 'N2') as FormattedNumber,
    FORMAT(12345.67, 'C', 'en-US') as CurrencyFormat,

    -- Date conversions
    CAST(GETDATE() as DATE) as DateOnly,
    CAST(GETDATE() as TIME) as TimeOnly,
    CONVERT(VARCHAR(10), GETDATE(), 101) as USDateString,

    -- Boolean conversions
    CAST(CASE WHEN IsDeceased = 1 THEN 'Yes' ELSE 'No' END as VARCHAR(3)) as DeceasedText
FROM People
WHERE PeopleId = 1;

Window Functions (Analytical Functions)

5. Ranking Functions

-- Ranking donors by giving amount
WITH DonorRanking AS (
    SELECT
        p.Name,
        SUM(c.ContributionAmount) as TotalGiving,

        -- Different ranking methods
        ROW_NUMBER() OVER (ORDER BY SUM(c.ContributionAmount) DESC) as RowNum,
        RANK() OVER (ORDER BY SUM(c.ContributionAmount) DESC) as RankNum,
        DENSE_RANK() OVER (ORDER BY SUM(c.ContributionAmount) DESC) as DenseRankNum,
        PERCENT_RANK() OVER (ORDER BY SUM(c.ContributionAmount) DESC) as PercentileRank,

        -- Grouping rankings
        ROW_NUMBER() OVER (
            PARTITION BY YEAR(c.ContributionDate)
            ORDER BY SUM(c.ContributionAmount) DESC
        ) as YearlyRank
    FROM People p
    JOIN Contribution c ON p.PeopleId = c.PeopleId
    WHERE c.ContributionDate >= '2024-01-01'
        AND c.ContributionTypeId NOT IN (6, 7, 8, 99)
    GROUP BY p.PeopleId, p.Name, YEAR(c.ContributionDate)
)
SELECT * FROM DonorRanking
WHERE RowNum <= 100; -- Top 100 donors

6. Aggregate Window Functions

-- Running totals and moving averages
WITH AttendanceAnalysis AS (
    SELECT
        MeetingDate,
        OrganizationId,
        COUNT(*) as Attendance,

        -- Running total
        SUM(COUNT(*)) OVER (
            PARTITION BY OrganizationId
            ORDER BY MeetingDate
        ) as RunningTotal,

        -- Moving average (last 4 weeks)
        AVG(COUNT(*)) OVER (
            PARTITION BY OrganizationId
            ORDER BY MeetingDate
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ) as MovingAvg4Week,

        -- Comparison to average
        COUNT(*) - AVG(COUNT(*)) OVER (
            PARTITION BY OrganizationId
        ) as DifferenceFromAvg,

        -- Min/Max in window
        MIN(COUNT(*)) OVER (
            PARTITION BY OrganizationId
            ORDER BY MeetingDate
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) as Min12Week,

        MAX(COUNT(*)) OVER (
            PARTITION BY OrganizationId
            ORDER BY MeetingDate
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) as Max12Week
    FROM Attend
    WHERE AttendanceFlag = 1
        AND MeetingDate >= DATEADD(month, -6, GETDATE())
    GROUP BY MeetingDate, OrganizationId
)
SELECT * FROM AttendanceAnalysis
ORDER BY OrganizationId, MeetingDate;

7. Lead/Lag Functions

-- Compare values with previous/next rows
WITH GivingTrends AS (
    SELECT
        PeopleId,
        YEAR(ContributionDate) as GivingYear,
        SUM(ContributionAmount) as YearlyTotal,

        -- Previous year's giving
        LAG(SUM(ContributionAmount), 1, 0) OVER (
            PARTITION BY PeopleId
            ORDER BY YEAR(ContributionDate)
        ) as PreviousYearTotal,

        -- Next year's giving (if exists)
        LEAD(SUM(ContributionAmount), 1) OVER (
            PARTITION BY PeopleId
            ORDER BY YEAR(ContributionDate)
        ) as NextYearTotal,

        -- Year-over-year change
        SUM(ContributionAmount) - LAG(SUM(ContributionAmount), 1, 0) OVER (
            PARTITION BY PeopleId
            ORDER BY YEAR(ContributionDate)
        ) as YoYChange,

        -- Percentage change
        CASE
            WHEN LAG(SUM(ContributionAmount), 1) OVER (
                PARTITION BY PeopleId ORDER BY YEAR(ContributionDate)
            ) > 0
            THEN CAST(
                (SUM(ContributionAmount) - LAG(SUM(ContributionAmount), 1) OVER (
                    PARTITION BY PeopleId ORDER BY YEAR(ContributionDate)
                )) * 100.0 / LAG(SUM(ContributionAmount), 1) OVER (
                    PARTITION BY PeopleId ORDER BY YEAR(ContributionDate)
                ) as DECIMAL(10,2)
            )
            ELSE NULL
        END as YoYPercentChange
    FROM Contribution
    WHERE ContributionTypeId NOT IN (6, 7, 8, 99)
    GROUP BY PeopleId, YEAR(ContributionDate)
)
SELECT * FROM GivingTrends
WHERE PeopleId IN (SELECT TOP 100 PeopleId FROM People)
ORDER BY PeopleId, GivingYear;

Table-Valued Functions with Temporary Tables

8. Cross Apply Pattern (Simulating Functions)

-- Using CROSS APPLY to simulate a function that gets last N attendances
WITH PersonList AS (
    SELECT TOP 100 PeopleId, Name
    FROM People
    WHERE MemberStatusId = 10
)
SELECT
    pl.PeopleId,
    pl.Name,
    la.LastAttendances
FROM PersonList pl
CROSS APPLY (
    SELECT STRING_AGG(
        CONVERT(VARCHAR(10), MeetingDate, 101), ', '
    ) WITHIN GROUP (ORDER BY MeetingDate DESC) as LastAttendances
    FROM (
        SELECT TOP 5 a.MeetingDate
        FROM Attend a
        WHERE a.PeopleId = pl.PeopleId
            AND a.AttendanceFlag = 1
        ORDER BY a.MeetingDate DESC
    ) last5
) la;

-- Alternative using temp table for reusability
CREATE TABLE #LastAttendances (
    PeopleId INT,
    AttendanceList VARCHAR(MAX)
);

INSERT INTO #LastAttendances
SELECT
    p.PeopleId,
    STRING_AGG(
        CONVERT(VARCHAR(10), a.MeetingDate, 101), ', '
    ) WITHIN GROUP (ORDER BY a.MeetingDate DESC)
FROM People p
CROSS APPLY (
    SELECT TOP 5 MeetingDate
    FROM Attend
    WHERE PeopleId = p.PeopleId
        AND AttendanceFlag = 1
    ORDER BY MeetingDate DESC
) a
WHERE p.MemberStatusId = 10
GROUP BY p.PeopleId;

-- Now use like a function result
SELECT
    p.Name,
    la.AttendanceList
FROM People p
JOIN #LastAttendances la ON p.PeopleId = la.PeopleId;

9. JSON Functions (SQL Server 2016+)

-- Working with JSON data in Extra Value fields
SELECT
    PeopleId,
    Field,
    Data,

    -- Extract JSON values
    JSON_VALUE(Data, '$.firstName') as FirstName,
    JSON_VALUE(Data, '$.lastName') as LastName,
    JSON_VALUE(Data, '$.preferences.emailOptIn') as EmailOptIn,

    -- Query JSON arrays
    JSON_QUERY(Data, '$.children') as ChildrenArray,

    -- Check if valid JSON
    ISJSON(Data) as IsValidJson
FROM PeopleExtra
WHERE Field = 'CustomData'
    AND ISJSON(Data) = 1;

-- Parse JSON array
SELECT
    p.PeopleId,
    p.Name,
    children.ChildName,
    children.ChildAge
FROM People p
CROSS APPLY OPENJSON(
    (SELECT Data FROM PeopleExtra
     WHERE PeopleId = p.PeopleId AND Field = 'Children')
) WITH (
    ChildName NVARCHAR(100) '$.name',
    ChildAge INT '$.age'
) as children;

10. System Functions

-- Useful system functions for debugging and analysis
SELECT
    -- Session information
    @@SERVERNAME as ServerName,
    DB_NAME() as DatabaseName,
    USER_NAME() as CurrentUser,
    GETDATE() as CurrentDateTime,
    @@VERSION as SQLServerVersion,

    -- Query performance helpers
    @@ROWCOUNT as LastRowCount,

    -- Null handling
    ISNULL(NULL, 'Default') as NullReplacement,
    COALESCE(NULL, NULL, 'First Non-Null') as FirstNonNull,
    NULLIF(0, 0) as ReturnsNull,

    -- Type checking
    ISNUMERIC('123.45') as IsNumber,
    ISDATE('2024-01-01') as IsDate;

Practical Function Combinations

Example 1: Engagement Score Calculation

-- Calculate custom engagement score using multiple functions
WITH EngagementData AS (
    SELECT
        p.PeopleId,
        p.Name,

        -- Attendance score (0-40 points)
        CAST(
            CASE
                WHEN att.AttendanceCount >= 12 THEN 40
                WHEN att.AttendanceCount >= 8 THEN 30
                WHEN att.AttendanceCount >= 4 THEN 20
                WHEN att.AttendanceCount >= 1 THEN 10
                ELSE 0
            END as INT
        ) as AttendanceScore,

        -- Giving score (0-30 points)
        CAST(
            CASE
                WHEN giv.TotalGiving >= 1000 THEN 30
                WHEN giv.TotalGiving >= 500 THEN 20
                WHEN giv.TotalGiving >= 100 THEN 10
                WHEN giv.TotalGiving > 0 THEN 5
                ELSE 0
            END as INT
        ) as GivingScore,

        -- Involvement score (0-30 points)
        CAST(
            CASE
                WHEN inv.OrgCount >= 3 THEN 30
                WHEN inv.OrgCount = 2 THEN 20
                WHEN inv.OrgCount = 1 THEN 10
                ELSE 0
            END as INT
        ) as InvolvementScore

    FROM People p
    LEFT JOIN (
        SELECT PeopleId, COUNT(*) as AttendanceCount
        FROM Attend
        WHERE AttendanceFlag = 1
            AND MeetingDate >= DATEADD(month, -3, GETDATE())
        GROUP BY PeopleId
    ) att ON p.PeopleId = att.PeopleId
    LEFT JOIN (
        SELECT PeopleId, SUM(ContributionAmount) as TotalGiving
        FROM Contribution
        WHERE ContributionDate >= DATEADD(month, -3, GETDATE())
            AND ContributionTypeId NOT IN (6, 7, 8, 99)
        GROUP BY PeopleId
    ) giv ON p.PeopleId = giv.PeopleId
    LEFT JOIN (
        SELECT PeopleId, COUNT(DISTINCT OrganizationId) as OrgCount
        FROM OrganizationMembers
        WHERE OrganizationId IN (
            SELECT OrganizationId FROM Organizations
            WHERE OrganizationStatusId = 30
        )
        GROUP BY PeopleId
    ) inv ON p.PeopleId = inv.PeopleId
    WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
)
SELECT
    PeopleId,
    Name,
    AttendanceScore,
    GivingScore,
    InvolvementScore,
    AttendanceScore + GivingScore + InvolvementScore as TotalScore,

    -- Percentile ranking
    PERCENT_RANK() OVER (
        ORDER BY AttendanceScore + GivingScore + InvolvementScore
    ) * 100 as PercentileRank,

    -- Categorization
    CASE
        WHEN AttendanceScore + GivingScore + InvolvementScore >= 70 THEN 'Highly Engaged'
        WHEN AttendanceScore + GivingScore + InvolvementScore >= 40 THEN 'Engaged'
        WHEN AttendanceScore + GivingScore + InvolvementScore >= 20 THEN 'Somewhat Engaged'
        WHEN AttendanceScore + GivingScore + InvolvementScore > 0 THEN 'Minimally Engaged'
        ELSE 'Not Engaged'
    END as EngagementLevel
FROM EngagementData
ORDER BY TotalScore DESC;

Example 2: Family Analysis Functions

-- Comprehensive family analysis using multiple functions
WITH FamilyAnalysis AS (
    SELECT
        f.FamilyId,

        -- Family composition
        COUNT(DISTINCT p.PeopleId) as FamilySize,
        STRING_AGG(p.Name, ', ') WITHIN GROUP (ORDER BY p.PositionInFamilyId, p.Age DESC) as FamilyMembers,

        -- Age statistics
        MIN(p.Age) as YoungestAge,
        MAX(p.Age) as OldestAge,
        AVG(p.Age) as AverageAge,

        -- Count by position
        SUM(CASE WHEN p.PositionInFamilyId = 10 THEN 1 ELSE 0 END) as Adults,
        SUM(CASE WHEN p.PositionInFamilyId = 30 THEN 1 ELSE 0 END) as Children,

        -- Contact information
        MAX(CASE WHEN p.PositionInFamilyId = 10 THEN p.EmailAddress END) as PrimaryEmail,
        MAX(CASE WHEN p.PositionInFamilyId = 10 THEN p.CellPhone END) as PrimaryPhone,

        -- Giving (family total)
        ISNULL(fam_giving.TotalGiving, 0) as FamilyGiving,

        -- Attendance (family average)
        ISNULL(fam_attend.AvgAttendance, 0) as FamilyAttendance

    FROM Families f
    JOIN People p ON f.FamilyId = p.FamilyId
    LEFT JOIN (
        SELECT
            p2.FamilyId,
            SUM(c.ContributionAmount) as TotalGiving
        FROM Contribution c
        JOIN People p2 ON c.PeopleId = p2.PeopleId
        WHERE c.ContributionDate >= DATEADD(year, -1, GETDATE())
            AND c.ContributionTypeId NOT IN (6, 7, 8, 99)
        GROUP BY p2.FamilyId
    ) fam_giving ON f.FamilyId = fam_giving.FamilyId
    LEFT JOIN (
        SELECT
            p3.FamilyId,
            AVG(CAST(person_attend.AttendCount as FLOAT)) as AvgAttendance
        FROM People p3
        JOIN (
            SELECT PeopleId, COUNT(*) as AttendCount
            FROM Attend
            WHERE AttendanceFlag = 1
                AND MeetingDate >= DATEADD(month, -3, GETDATE())
            GROUP BY PeopleId
        ) person_attend ON p3.PeopleId = person_attend.PeopleId
        GROUP BY p3.FamilyId
    ) fam_attend ON f.FamilyId = fam_attend.FamilyId
    WHERE p.IsDeceased = 0
    GROUP BY f.FamilyId, fam_giving.TotalGiving, fam_attend.AvgAttendance
)
SELECT
    *,
    -- Classification
    CASE
        WHEN Adults >= 2 AND Children > 0 THEN 'Traditional Family'
        WHEN Adults = 1 AND Children > 0 THEN 'Single Parent'
        WHEN Adults >= 2 AND Children = 0 THEN 'Couple'
        WHEN Adults = 1 AND Children = 0 THEN 'Single'
        ELSE 'Other'
    END as FamilyType,

    -- Engagement ranking
    DENSE_RANK() OVER (ORDER BY FamilyGiving DESC) as GivingRank,
    DENSE_RANK() OVER (ORDER BY FamilyAttendance DESC) as AttendanceRank
FROM FamilyAnalysis
WHERE FamilySize > 0
ORDER BY FamilyGiving DESC;

Best Practices for Functions in TouchPoint

📦 TouchPoint User-Defined Functions (UDFs)

📊 UDF Categories Overview

🎯 Most Important Functions by Category

👥 People & Demographics Functions

💰 Contribution & Finance Functions

📅 Attendance Functions

🏢 Organization Functions

📱 Contact & Communication Functions

📊 Utility & Helper Functions

🔍 Search & Filter Functions

📋 Complete Function List by Type

Scalar Functions (179 total)

AddressMatch, Age, AgeInMonths, AllDigits, AllRegexMatchs, AttendDesc, AttendItem,
AvgSunAttendance, BaptismAgeRange, BibleFellowshipClassId, Birthday, CompactAttendHistory,
CompleteZip, ComputeAge, ComputePositionInFamily, ContributionAmount, ContributionAmount2,
ContributionChange, ContributionCount, CoupleFlag, CreateForeignKeys, DayAndTime,
DaysBetween12Attend, DaysSinceAttend, DaysSinceContact, DecToBase, DOB, DollarRange,
DonorTotalGifts, DonorTotalGiftsAttrRange, DonorTotalGiftsSize, DonorTotalMean,
DonorTotalMedian, DonorTotalUnits, DonorTotalUnitsAttrRange, DonorTotalUnitsSize,
DropForeignKeys, ElapsedTime, EnrollmentTransactionId, EntryPointId,
FamilyContributionGreetingName, FamilyGreetingName, FamilyMakeup, FindPerson0,
FindResCode, FirstActivity, FirstMeetingDateLastLear, FirstMondayOfMonth, FmtPhone,
GetAttendedTodaysMeeting, GetAttendType, GetCurrentBundleByType, GetCurrentMissionTripBundle,
GetCurrentOnlineBundle, GetCurrentOnlinePledgeBundle, GetDigits, GetEldestFamilyMember,
GetPeopleIdFromACS, GetPeopleIdFromIndividualNumber, GetScheduleDesc, GetScheduleTime,
GetSecurityCode, GetStreet, GetTodaysMeetingHour, GetTodaysMeetingId, GetWeekDayNameOfDate,
HeadOfHouseholdId, HeadOfHouseHoldSpouseId, HomePhone, InSmallGroup, IpVelocity,
IsSmallGroupLeaderOnly, IsValidEmail, LastActive, LastAttend, LastAttended, LastChanged,
LastContact, LastDrop, LastIdInTrans, LastMemberTypeInTrans, MaxMeetingDate, MaxPastMeeting,
MemberDesc, MemberStatusDescription, MemberTypeAsOf, MemberTypeAtLastDrop, MinMeetingDate,
NextAnniversary, NextBirthday, NextChangeTransactionId, NextChangeTransactionId2,
NextTranChangeDate, OneHeadOfHouseholdIsMember, OrganizationLeaderId, OrganizationLeaderName,
OrganizationMemberCount, OrganizationMemberCount2, OrganizationPrevCount,
OrganizationPrevMemberCount, OrganizationProspectCount, OrgCheckedCount, OrgFee,
OrgFilterCheckedCount, ParentNames, ParentNamesAndCells, ParseDate, PersonAttendCountOrg,
PledgeAmount, PledgeCount, PrimaryAddress, PrimaryAddress2, PrimaryBadAddressFlag,
PrimaryCity, PrimaryCountry, PrimaryResCode, PrimaryState, PrimaryZip, RegexMatch,
RemoveDiacritics, ScheduleId, SchoolGrade, SchoolGradeLevel, SmallGroupLeader, SpaceToNull,
SpouseId, SpouseIdJoint, StartsLower, StatusFlag, StatusFlagsAll, SundayForDate,
SundayForWeek, SundayForWeekNumber, Tool_VarbinaryToVarcharHex, TotalPaid, UEmail,
UName, UName2, UserName, UserPeopleIdFromEmail, UserRoleList, VisitAttendStr,
WasDeaconActive2008, WeekNumber, WidowedDate

Inline Table-Valued Functions (118 total)

AttendanceChange, AttendanceChangeDetail, AttendanceTypeAsOf, AttendCntHistory,
AttendCommitments, AttendDaysAfterNthVisitAsOf, AttendedAsOf, CheckInActivitiesSearch,
ConsecutiveAbsents, ContactSummary, ContactTypeTotals, Contributions2, Contributions2ByPerson,
Contributions3, ContributionsPledge, Contributors, Donors, DonorsWithoutPledges,
DownlineDetails, DownlineLevels, DuplicateMergeInfo, EngagementScoresPerson,
EnrollmentHistory, FamilyGiver, FamilyGiverFunds, FamilyMembers, FirstRecurringGiversByDate,
FirstTimeGivers, FirstTimeGiversByDate, FN_ContributionsByPerson, FN_FirstTimeGivers,
GetContributions, GetContributionsDetails, GetContributionsRange,
GetContributionTotalsBothIfJoint, GetPledgedTotalsBothIfJoint, GetPledgedTotalsBothIfJoint2,
GetTotalContributions, GetTotalContributions2, GetTotalContributions3,
GetTotalContributionsAgeRange, GetTotalContributionsDonor, GetTotalContributionsDonor2,
GetTotalContributionsDonorFund, GetTotalContributionsRange, GetTotalPledgesDonor2,
GetTotalPledgesDonor3, GiftsInKind, GiftSummary, GivingChange, GivingChangeFund,
GivingChangeFundQuarters, GivingChangeQuartersFund, GivingChangeQuartersFund2,
GivingCurrentPercentOfFormer, GuestList, GuestList2, HasIncompleteRegistrations,
InvolvementCurrent, InvolvementPrevious, LastAddrElement, LastAttendOrg, LastFamilyOrgAttends,
LastMeetings, MeetingsDataForDateRange, NonTaxContributions, NotAttendedAsOf, OptOuts,
OrgFilterCurrent, OrgFilterGuests, OrgFilterIds, OrgFilterIds3, OrgFilterInactive,
OrgFilterPending, OrgFilterPeople, OrgFilterPeople3, OrgFilterPrevious, OrgFilterProspects,
OrgMember, OrgMemberInfo, OrgMemberQuestions, OrgMembersAsOfDate, OrgMembersAsOfDate2,
OrgMinistryInfo, OrgPeople, OrgPeopleCurrent, OrgPeopleGuests, OrgPeopleIds,
OrgPeopleInactive, OrgPeoplePending, OrgPeoplePrevious, OrgPeopleProspects, OrgSearch,
OrgVisitorsAsOfDate, PeopleIdsFromOrgSearch, PersonStatusFlags, PledgeBalances,
PledgeFulfillment, PledgeReport, PotentialDups, PotentialSubstitutes, RecentAbsents,
RecentAttendance, RecentAttendInDaysByCount, RecentAttendInDaysByCountDesc, RecentGiver,
RecentGiverFund, RecentGiverFunds, RecentRegistrations, RegisterLinksFromMaster,
RegistrationGradeOptions, Registrations, RegistrationSmallGroups, SchedulerPotentialSubstitutes,
SearchDivisions, SenderGifts, SoftCreditContributions, SplitFundIds, SplitInts,
SplitStrings, StatusFlags, StatusFlagsPerson, StockGifts, StringSplitOrdered, TPStats,
UnitPledgeSummary, VisitNumberSinceDate, VisitsAbsents, VolunteerCalendar

Multi-Statement Table-Valued Functions (35 total)

ActiveRecords, ActivityLogSearch, AttendanceCredits, AttendMemberTypeAsOf, CheckinByDate,
CheckinFamilyMembers, CheckinMatch, CheckinMatchBarCodeOnly, ContributionCountTable,
Contributions0, Contributions2SearchIds, ContributionSearch, CsvTable, CurrOrgMembers,
CurrOrgMembers2, DownlineCategories, DownlineSingleTrace, DownlineSummary, FamilyAttendance,
FilterOnlineReg, FilterOrgSearchName, FindPerson, FindPerson2, FindPerson3, FindPerson4,
FindPersonByEmail, FindPersonByExtraValue, FirstLast, GenRanges, GetTodaysMeetingHours,
GetTodaysMeetingHours2, GetTodaysMeetingHours3, MembersAsOf, MembersWhoAttendedOrgs,
MostRecentItems, NormalContributions, OnlineRegMatches, OrgDayStats, OrgFilterPeople2,
OrgMembersGroupFiltered, OrgPeople2, PastAttendanceCredits, PbGetInvolvementJoinedCompleted,
PbGetInvolvementMembersNotInProcess, PeopleWeeklyEngagementScore, Pledges0, PledgesSummary,
RecentAbsents2, RecentAttendMemberType, RecentAttendType, RecentContributionSummaries,
RecentIncompleteRegistrations, RecentIncompleteRegistrations2, RecentNewVisitCount,
RollList, RollListFilteredBySubgroups, RollListHighlight, Split, SplitIntsKey, SundayDates,
TaggedPeople, TransactionSearch, WeeklyAttendsForOrgs

💡 Using UDFs in Queries

Example 1: People Report with Age and Family Info

SELECT TOP 10
    p.PeopleId,
    p.Name,
    dbo.Age(p.PeopleId) as Age,
    dbo.Birthday(p.PeopleId) as Birthday,
    dbo.SpouseId(p.PeopleId) as SpouseId,
    dbo.UName(dbo.SpouseId(p.PeopleId)) as SpouseName,
    dbo.FamilyGreetingName(1, p.PeopleId) as FamilyGreeting  -- Takes (type, peopleId)
FROM People p
WHERE p.MemberStatusId = 10  -- Members only
    AND p.IsDeceased = 0
ORDER BY p.Name;

Example 2: Contribution Analysis Using Contributions2

-- Use the most important UDF: Contributions2
-- This handles spouse contributions, excludes non-tax items, etc.
-- Actual columns: FamilyId, PeopleId, Date, HeadName, SpouseName, Amount, FundName, etc.
SELECT
    c.FamilyId,
    c.HeadName,
    c.SpouseName,
    c.Amount,
    c.Date as ContributionDate,
    c.FundName,
    c.CheckNo,
    c.BundleType
FROM dbo.Contributions2(
    '2024-01-01',     -- @fd (from date)
    '2024-12-31',     -- @td (to date)
    0,                -- @campusid (0 = all campuses)
    0,                -- @pledges (0 = exclude pledges)
    0,                -- @nontaxded (0 = tax-deductible only)
    1,                -- @includeUnclosed (1 = include unclosed bundles)
    NULL              -- @fundids (NULL = all funds, or '1,2,3' for specific)
) c
WHERE c.Amount > 100
ORDER BY c.Amount DESC;

Example 3: Attendance Tracking

-- Note: LastAttend takes (orgid, peopleid) - parameter order matters!
-- Some UDFs like OrgPeopleCurrent require SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER ON;

-- Example 1: Check attendance for a specific organization
DECLARE @orgid INT = 100;  -- Replace with your organization ID

SELECT TOP 10
    p.Name,
    dbo.LastAttend(@orgid, p.PeopleId) as LastAttendDate,
    DATEDIFF(day, dbo.LastAttend(@orgid, p.PeopleId), GETDATE()) as DaysSinceAttended
FROM People p
WHERE p.PeopleId IN (
    SELECT PeopleId FROM dbo.OrgPeopleCurrent(@orgid)
)
    AND dbo.LastAttend(@orgid, p.PeopleId) IS NOT NULL
ORDER BY DaysSinceAttended DESC;

-- Example 2: Check attendance across all organizations (simpler, no variable needed)
SELECT TOP 10
    p.Name,
    dbo.LastAttend(0, p.PeopleId) as LastAttendDate,  -- 0 = all orgs
    DATEDIFF(day, dbo.LastAttend(0, p.PeopleId), GETDATE()) as DaysSinceAttended
FROM People p
WHERE dbo.LastAttend(0, p.PeopleId) IS NOT NULL
ORDER BY DaysSinceAttended DESC;

Example 4: Organization Member Lists

-- Get all current members with contact info
-- Note: OrgPeopleCurrent returns limited columns, JOIN with People for full details
SET QUOTED_IDENTIFIER ON;  -- Required for OrgPeopleCurrent

SELECT TOP 20
    op.PeopleId,
    p.Name2,  -- From People table
    p.EmailAddress,  -- From People table
    dbo.FmtPhone(p.CellPhone) as CellPhone,  -- FmtPhone takes 1 param
    op.MemberType,
    op.AttPct as AttendancePercent,
    dbo.OrganizationLeaderName(101) as LeaderName
FROM dbo.OrgPeopleCurrent(101) op  -- 101 is the OrgId
JOIN People p ON op.PeopleId = p.PeopleId
ORDER BY p.Name2;

Example 5: Tested Real-World UDF Usage

-- Working with Age calculation (BDate column in People table)
SELECT
    p.PeopleId,
    p.Name,
    p.BDate as BirthDate,
    dbo.Age(p.PeopleId) as CalculatedAge
FROM People p
WHERE p.BDate IS NOT NULL
    AND p.IsDeceased = 0
ORDER BY CalculatedAge DESC;

-- Using GetTotalContributions (table-valued function)
-- Note: Returns empty for many people, check contribution records
SELECT *
FROM dbo.GetTotalContributions(16901, NULL)  -- PeopleId, optional date

-- Using LastAttend with correct parameters (orgid, peopleid)
SELECT
    p.PeopleId,
    p.Name,
    dbo.LastAttend(0, p.PeopleId) as LastAttendanceDate  -- 0 = all orgs
FROM People p
WHERE EXISTS (
    SELECT 1 FROM Attend a
    WHERE a.PeopleId = p.PeopleId
    AND a.AttendanceFlag = 1
)
ORDER BY p.PeopleId;

-- Working with Contributions2 - verified parameters
SELECT TOP 100
    FamilyId,
    HeadName,
    SpouseName,
    Date as ContributionDate,
    Amount,
    FundName,
    CheckNo
FROM dbo.Contributions2(
    '2020-01-01',  -- From date
    '2024-12-31',  -- To date
    0,             -- Campus (0=all)
    0,             -- Include pledges (0=no)
    NULL,          -- Non-tax deductible (NULL=default)
    1,             -- Include unclosed bundles
    NULL           -- Fund IDs (NULL=all)
)
WHERE Amount > 0
ORDER BY Date DESC, Amount DESC;

-- Using OrgPeopleCurrent - returns specific columns
-- Columns: PeopleId, Tab, GroupCode, AttPct, LastAttended, Joined,
--          Dropped, InactiveDate, MemberCode, MemberType, Hidden, Groups, Grade
SELECT
    PeopleId,
    AttPct as AttendancePercent,
    LastAttended,
    MemberType
FROM dbo.OrgPeopleCurrent(100)  -- Single parameter: OrgId
WHERE AttPct > 50;  -- Members with >50% attendance

-- DaysSinceContact function
SELECT TOP 20
    p.PeopleId,
    p.Name,
    dbo.DaysSinceContact(p.PeopleId) as DaysSinceLastContact
FROM People p
WHERE p.MemberStatusId = 10  -- Members only
    AND dbo.DaysSinceContact(p.PeopleId) IS NOT NULL
ORDER BY DaysSinceLastContact DESC;

Example 6: Common UDF Patterns and Pitfalls

-- ⚠️ PITFALL: Some functions require SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER ON;
SELECT * FROM dbo.SomeTableValuedFunction(params);

-- ⚠️ PITFALL: Birth date column is 'BDate' not 'BirthDate'
-- The People table stores birth info in: BDate, BirthYear, BirthMonth, BirthDay
SELECT BDate, BirthYear, BirthMonth, BirthDay FROM People WHERE PeopleId = 117;

-- ✅ CORRECT: Table-valued functions need FROM clause
SELECT * FROM dbo.GetTotalContributions(117, NULL);  -- Correct
-- ❌ WRONG: SELECT dbo.GetTotalContributions(117, NULL);  -- Error

-- ✅ CORRECT: Parameter order matters - check sys.parameters
-- LastAttend takes (orgid, peopleid) NOT (peopleid, orgid)
SELECT dbo.LastAttend(0, 117);  -- Correct: orgid first
-- ❌ WRONG: SELECT dbo.LastAttend(117, 0);  -- Wrong order

-- ✅ OPTIMIZATION: Use CROSS APPLY for better performance
SELECT
    p.Name,
    contrib.*
FROM People p
CROSS APPLY dbo.GetTotalContributions(p.PeopleId, NULL) contrib
WHERE p.FamilyId = 8392;

-- ✅ NULL handling in UDFs
-- Many UDFs return NULL for no data - always handle
SELECT
    p.Name,
    ISNULL(dbo.DaysSinceContact(p.PeopleId), 9999) as DaysSince
FROM People p;

🔓 UDF Examples That Typically Work Without Permission Issues

These table-valued functions often have broader permissions:

Working Example 1: Using Contributions2 (Table-Valued Function)

-- Contributions2 is a table-valued function that often works when scalar functions don't
SELECT TOP 20
    c.FamilyId,
    c.HeadName,
    c.SpouseName,
    c.Date as ContributionDate,
    c.Amount,
    c.FundName,
    c.CheckNo
FROM dbo.Contributions2(
    '2024-01-01',  -- From date
    '2024-12-31',  -- To date
    0,             -- Campus (0=all)
    0,             -- Pledges (0=exclude)
    0,             -- Non-tax deductible (0=exclude)
    1,             -- Include unclosed bundles
    NULL           -- Fund IDs (NULL=all)
) c
WHERE c.Amount > 0
ORDER BY c.Date DESC;

Working Example 2: Using Split Function (Table-Valued)

-- Split function for parsing delimited strings
SELECT value
FROM dbo.Split('Apple,Banana,Cherry', ',');

-- Use Split to parse multi-value extra values
SELECT
    p.PeopleId,
    p.Name,
    s.value as Interest
FROM People p
CROSS APPLY dbo.Split(
    (SELECT Data FROM PeopleExtra
     WHERE PeopleId = p.PeopleId
     AND Field = 'Interests'), ',') s
WHERE p.PeopleId = 12345;

Working Example 3: Using PledgeReport (Table-Valued)

-- Get pledge fulfillment data
SELECT *
FROM dbo.PledgeReport(1, '2024-01-01', '2024-12-31')
WHERE AmountPledged > 0;

Working Example 4: Using GetContributionsRange (Table-Valued)

-- Get contribution ranges for analysis (8 parameters)
SELECT TOP 20 *
FROM dbo.GetContributionsRange(
    '2024-01-01',  -- @fd (from date)
    '2024-12-31',  -- @td (to date)
    0,             -- @campusid
    0,             -- @nontaxded
    1,             -- @includeUnclosed
    0,             -- @pledge
    NULL,          -- @fundid
    NULL           -- @fundids
)
WHERE Amount > 100;

📝 Alternative Queries Without UDF Permissions

If you don't have EXECUTE permissions on any UDFs, use these standard SQL alternatives:

Alternative 1: Calculate Age Without Age() Function

-- Calculate age using standard SQL instead of dbo.Age()
SELECT TOP 10
    p.PeopleId,
    p.Name,
    p.BDate,
    -- Calculate age manually
    DATEDIFF(year, p.BDate, GETDATE()) -
        CASE
            WHEN MONTH(p.BDate) > MONTH(GETDATE()) OR
                 (MONTH(p.BDate) = MONTH(GETDATE()) AND DAY(p.BDate) > DAY(GETDATE()))
            THEN 1
            ELSE 0
        END as Age,
    -- Get spouse info using JOIN instead of dbo.SpouseId()
    spouse.PeopleId as SpouseId,
    spouse.Name as SpouseName
FROM People p
LEFT JOIN People spouse ON p.SpouseId = spouse.PeopleId
WHERE p.MemberStatusId = 10
    AND p.IsDeceased = 0
ORDER BY p.Name;

Alternative 2: Get Contributions Without Contributions2() Function

-- Get contributions using direct table query instead of dbo.Contributions2()
SELECT TOP 20
    p.FamilyId,
    p.Name as HeadName,
    c.ContributionDate,
    c.ContributionAmount as Amount,
    cf.FundName
FROM Contribution c
JOIN People p ON c.PeopleId = p.PeopleId
JOIN ContributionFund cf ON c.FundId = cf.FundId
WHERE c.ContributionDate BETWEEN '2024-01-01' AND '2024-12-31'
    AND c.ContributionTypeId = 1  -- Tax-deductible only
    AND c.ContributionStatusId = 0  -- Posted
    AND c.ContributionAmount > 100
ORDER BY c.ContributionAmount DESC;

Alternative 3: Check Attendance Without LastAttend() Function

-- Get last attendance date using subquery instead of dbo.LastAttend()
SELECT TOP 10
    p.PeopleId,
    p.Name,
    (SELECT MAX(m.MeetingDate)
     FROM Attend a
     JOIN Meetings m ON a.MeetingId = m.MeetingId
     WHERE a.PeopleId = p.PeopleId
       AND a.AttendanceFlag = 1) as LastAttendDate,
    DATEDIFF(day,
        (SELECT MAX(m.MeetingDate)
         FROM Attend a
         JOIN Meetings m ON a.MeetingId = m.MeetingId
         WHERE a.PeopleId = p.PeopleId
           AND a.AttendanceFlag = 1),
        GETDATE()) as DaysSinceAttended
FROM People p
WHERE EXISTS (
    SELECT 1 FROM Attend a
    WHERE a.PeopleId = p.PeopleId
    AND a.AttendanceFlag = 1
)
ORDER BY DaysSinceAttended DESC;

Alternative 4: Format Phone Numbers Without FmtPhone() Function

-- Format phone numbers using SQL string functions instead of dbo.FmtPhone()
SELECT TOP 10
    p.PeopleId,
    p.Name,
    p.CellPhone,
    -- Format phone as (XXX) XXX-XXXX
    CASE
        WHEN LEN(REPLACE(REPLACE(REPLACE(p.CellPhone, '-', ''), '(', ''), ')', '')) = 10
        THEN '(' + SUBSTRING(REPLACE(REPLACE(REPLACE(p.CellPhone, '-', ''), '(', ''), ')', ''), 1, 3) + ') ' +
             SUBSTRING(REPLACE(REPLACE(REPLACE(p.CellPhone, '-', ''), '(', ''), ')', ''), 4, 3) + '-' +
             SUBSTRING(REPLACE(REPLACE(REPLACE(p.CellPhone, '-', ''), '(', ''), ')', ''), 7, 4)
        ELSE p.CellPhone
    END as FormattedPhone
FROM People p
WHERE p.CellPhone IS NOT NULL
    AND p.CellPhone != '';

💡 Using Temp Tables Instead of UDFs

Since all users have read-only access with temp table permissions, use temp tables for complex calculations:

Temp Table Example 1: Calculate Ages Using Temp Table

-- Create temp table with age calculations (replaces dbo.Age() function)
SELECT
    PeopleId,
    Name,
    BDate,
    DATEDIFF(year, BDate, GETDATE()) -
        CASE
            WHEN MONTH(BDate) > MONTH(GETDATE()) OR
                 (MONTH(BDate) = MONTH(GETDATE()) AND DAY(BDate) > DAY(GETDATE()))
            THEN 1
            ELSE 0
        END as Age
INTO #PeopleWithAges
FROM People
WHERE BDate IS NOT NULL
    AND IsDeceased = 0;

-- Now use the temp table for analysis
SELECT
    Age,
    COUNT(*) as PeopleCount
FROM #PeopleWithAges
GROUP BY Age
ORDER BY Age;

-- Clean up
DROP TABLE #PeopleWithAges;

Temp Table Example 2: Build Custom Contribution Summary

-- Create contribution summary temp table (replaces complex UDF logic)
WITH ContributionSummary AS (
    SELECT
        p.FamilyId,
        p.Name as HeadOfHousehold,
        c.ContributionDate,
        c.ContributionAmount,
        cf.FundName,
        ROW_NUMBER() OVER (PARTITION BY p.FamilyId ORDER BY c.ContributionDate DESC) as RowNum
    FROM Contribution c
    JOIN People p ON c.PeopleId = p.PeopleId
    JOIN ContributionFund cf ON c.FundId = cf.FundId
    WHERE c.ContributionDate >= DATEADD(year, -1, GETDATE())
        AND c.ContributionTypeId = 1  -- Tax deductible only
        AND c.ContributionStatusId = 0  -- Posted
)
SELECT *
INTO #FamilyGiving
FROM ContributionSummary;

-- Analyze family giving patterns
SELECT
    FamilyId,
    HeadOfHousehold,
    COUNT(*) as GiftCount,
    SUM(ContributionAmount) as TotalGiving,
    AVG(ContributionAmount) as AverageGift,
    MAX(ContributionDate) as LastGiftDate
FROM #FamilyGiving
GROUP BY FamilyId, HeadOfHousehold
HAVING SUM(ContributionAmount) > 1000
ORDER BY TotalGiving DESC;

-- Clean up
DROP TABLE #FamilyGiving;

Temp Table Example 3: Attendance Analysis Without LastAttend()

-- Build attendance summary in temp table
SELECT
    a.PeopleId,
    p.Name,
    MAX(m.MeetingDate) as LastAttendDate,
    COUNT(DISTINCT m.MeetingId) as MeetingsAttended,
    COUNT(DISTINCT DATEPART(week, m.MeetingDate)) as WeeksAttended
INTO #AttendanceSummary
FROM Attend a
JOIN People p ON a.PeopleId = p.PeopleId
JOIN Meetings m ON a.MeetingId = m.MeetingId
WHERE a.AttendanceFlag = 1
    AND m.MeetingDate >= DATEADD(month, -3, GETDATE())
GROUP BY a.PeopleId, p.Name;

-- Analyze attendance patterns
SELECT
    *,
    DATEDIFF(day, LastAttendDate, GETDATE()) as DaysSinceLastAttend
FROM #AttendanceSummary
ORDER BY DaysSinceLastAttend DESC;

-- Find irregular attenders
SELECT * FROM #AttendanceSummary
WHERE WeeksAttended BETWEEN 2 AND 8  -- Attended 2-8 weeks in last 3 months
ORDER BY LastAttendDate DESC;

-- Clean up
DROP TABLE #AttendanceSummary;

View Metadata Queries

-- Check if a view exists
SELECT COUNT(*) as ViewExists
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'lookup'
    AND TABLE_NAME = 'MemberStatus';

-- Get view definition (if permissions allow)
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'vw_ActiveAdultMembers';

-- List all columns in a view with data types
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    NUMERIC_SCALE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'lookup'
    AND TABLE_NAME = 'MemberStatus'
ORDER BY ORDINAL_POSITION;

-- Find views that reference a specific table
SELECT DISTINCT
    v.TABLE_SCHEMA,
    v.TABLE_NAME as ViewName
FROM INFORMATION_SCHEMA.VIEWS v
WHERE v.VIEW_DEFINITION LIKE '%People%'
    AND v.TABLE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY v.TABLE_SCHEMA, v.TABLE_NAME;

Common View Patterns by Use Case

🔍 Key View SQL Examples

OrganizationStructure Examples

-- Example 1: Organization analysis with visitor percentage
-- NOTE: Column is misspelled as 'Vistors' in the view
SELECT
    Program,
    Division,
    Organization,
    OrgStatus,
    Members,
    Previous,
    Vistors,  -- Note: typo in actual view column name
    Meetings,
    CAST(ISNULL(Vistors * 100.0 / NULLIF(Members, 0), 0) as DECIMAL(10,2)) as VisitorPercent
FROM OrganizationStructure
WHERE ProgId = 1115  -- Adults program (verify your ProgIds!)
    AND Members > 0
ORDER BY Program, Division, Organization;

-- Example 2: Program summary statistics
SELECT
    Program,
    COUNT(DISTINCT OrgId) as TotalOrgs,
    SUM(Members) as TotalMembers,
    SUM(Vistors) as TotalVisitors,
    SUM(Meetings) as TotalMeetings,
    CAST(AVG(CAST(Members as FLOAT)) as DECIMAL(10,2)) as AvgMembersPerOrg
FROM OrganizationStructure
WHERE OrgStatus = 'Active'
GROUP BY ProgId, Program
ORDER BY TotalMembers DESC;

-- Example 3: Find high-visitor organizations (growth opportunities)
SELECT TOP 20
    Program,
    Division,
    Organization,
    Members,
    Vistors,
    CAST(Vistors * 100.0 / NULLIF(Members, 0) as DECIMAL(10,2)) as VisitorRate
FROM OrganizationStructure
WHERE OrgStatus = 'Active'
    AND Members > 5  -- Exclude very small groups
    AND Vistors > 0
ORDER BY VisitorRate DESC;

CheckInActivities Examples

-- Real-time check-in monitoring (last 3 hours)
SELECT
    PeopleId,
    Name,
    Activities,
    AccessType,
    CheckInTime,
    DATEDIFF(minute, CheckInTime, GETDATE()) as MinutesAgo
FROM CheckInActivities
WHERE CheckInTime >= DATEADD(hour, -3, GETDATE())
ORDER BY CheckInTime DESC;

-- Check-in patterns by hour of day
SELECT
    DATEPART(hour, CheckInTime) as Hour,
    Activities,  -- Note: plural form
    COUNT(*) as CheckInCount,
    COUNT(DISTINCT PeopleId) as UniquePeople
FROM CheckInActivities
WHERE CheckInTime >= DATEADD(day, -7, GETDATE())
GROUP BY DATEPART(hour, CheckInTime), Activities
ORDER BY Hour, Activities;

Lookup View Examples

-- Member status distribution with percentages
SELECT
    ms.Description as Status,
    COUNT(*) as Count,
    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as DECIMAL(5,2)) as Percentage
FROM People p
JOIN lookup.MemberStatus ms ON p.MemberStatusId = ms.Id
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
GROUP BY ms.Id, ms.Description
ORDER BY ms.Id;

-- Guest retention analysis using AttendType
WITH GuestAttendance AS (
    SELECT
        a.PeopleId,
        MIN(a.MeetingDate) as FirstVisit,
        MAX(a.MeetingDate) as LastVisit,
        COUNT(*) as VisitCount
    FROM Attend a
    WHERE a.AttendanceTypeId IN (30, 50, 60)  -- Guest types
        AND a.AttendanceFlag = 1
        AND a.MeetingDate >= DATEADD(month, -6, GETDATE())
    GROUP BY a.PeopleId
)
SELECT
    CASE
        WHEN VisitCount = 1 THEN 'One-time visitor'
        WHEN VisitCount BETWEEN 2 AND 3 THEN 'Returning guest'
        WHEN VisitCount > 3 THEN 'Regular attender'
    END as Category,
    COUNT(*) as PeopleCount
FROM GuestAttendance
GROUP BY
    CASE
        WHEN VisitCount = 1 THEN 'One-time visitor'
        WHEN VisitCount BETWEEN 2 AND 3 THEN 'Returning guest'
        WHEN VisitCount > 3 THEN 'Regular attender'
    END;

Financial Views Examples

-- Mission trip funding status using MissionTripTotals
        -- MissionTripTotals table lookup is slow due to how it updates the views
SELECT
    Trip,
    COUNT(*) as Participants,
    SUM(TripCost) as TotalCost,
    SUM(Raised) as TotalRaised,
    SUM(Due) as TotalDue,
    CAST(SUM(Raised) * 100.0 / NULLIF(SUM(TripCost), 0) as DECIMAL(5,2)) as PercentRaised
FROM MissionTripTotals
WHERE Due > 0
GROUP BY Trip
ORDER BY TotalDue DESC;

-- Outstanding balances by organization using TransactionSummary
SELECT
    o.OrganizationName,
    COUNT(DISTINCT ts.PeopleId) as PeopleWithBalances,
    SUM(ts.IndDue - ts.IndAmt) as TotalOutstanding
FROM TransactionSummary ts
JOIN Organizations o ON ts.OrganizationId = o.OrganizationId
WHERE ts.IsLatestTransaction = 1
    AND (ts.IndDue - ts.IndAmt) > 0
GROUP BY o.OrganizationId, o.OrganizationName
HAVING SUM(ts.IndDue - ts.IndAmt) > 0
ORDER BY TotalOutstanding DESC;

📊 Detailed View Documentation with Examples

🏃 Attendance & Activity Views

-- Find people who haven't attended in 90+ days
SELECT
    la.Name2,
    la.OrganizationName,
    la.LastAttend,
    la.EmailAddress,
    la.CellPhone,
    DATEDIFF(day, la.LastAttend, GETDATE()) as DaysSinceAttended
FROM LastAttends la
WHERE la.LastAttend < DATEADD(day, -90, GETDATE())
    AND la.HasEmail = 1  -- Has email for follow-up
ORDER BY la.LastAttend DESC;

-- Track first-time visitors by organization
SELECT
    fa.OrganizationName,
    COUNT(*) as FirstTimeVisitors,
    MIN(fa.FirstAttend) as EarliestVisit,
    MAX(fa.FirstAttend) as LatestVisit
FROM FirstAttends fa
WHERE fa.FirstAttend >= DATEADD(month, -1, GETDATE())
GROUP BY fa.OrganizationId, fa.OrganizationName
ORDER BY FirstTimeVisitors DESC;

-- Attendance credits by week
-- NOTE: Attended is a BIT column, use CASE for aggregation
SELECT
    ac.WeekDate,
    COUNT(DISTINCT ac.PeopleId) as UniqueAttendees,
    SUM(CASE WHEN ac.Attended = 1 THEN 1 ELSE 0 END) as TotalCredits,
    CAST(SUM(CASE WHEN ac.Attended = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as DECIMAL(5,2)) as AttendanceRate
FROM AttendCredits ac
WHERE ac.WeekDate >= DATEADD(week, -4, GETDATE())
GROUP BY ac.WeekDate
ORDER BY ac.WeekDate DESC;

💰 Financial & Transaction Views

-- Mission trip funding status
SELECT
    Trip,
    COUNT(DISTINCT PeopleId) as Participants,
    SUM(TripCost) as TotalCost,
    SUM(Raised) as TotalRaised,
    SUM(Due) as TotalDue,
    CAST(SUM(Raised) * 100.0 / NULLIF(SUM(TripCost), 0) as DECIMAL(5,2)) as PercentRaised
FROM MissionTripTotals
GROUP BY Trip
HAVING SUM(Due) > 0
ORDER BY SUM(Due) DESC;

-- Outstanding balances summary (requires SET QUOTED_IDENTIFIER ON)
SET QUOTED_IDENTIFIER ON;
SELECT
    COUNT(*) as TransactionsWithBalance,
    SUM(TotDue - TotalPayment) as TotalOutstanding,
    AVG(TotDue - TotalPayment) as AvgBalance
FROM TransactionBalances
WHERE TotDue > TotalPayment;

-- Recurring giving analysis
-- NOTE: View is already filtered for "today", columns are PeopleId, Name2, Amt
SELECT
    COUNT(*) as ScheduledGiftsToday,
    SUM(Amt) as ExpectedTotalToday,
    MIN(Amt) as SmallestGift,
    MAX(Amt) as LargestGift
FROM RecurringGivingDueForToday;

👥 Member & People Views

-- Member analysis with decisions
SELECT
    md.First + ' ' + md.Last as FullName,
    md.Age,
    md.Marital,
    md.Decision,
    md.Baptism,
    CASE
        WHEN md.JoinDt IS NOT NULL THEN DATEDIFF(year, md.JoinDt, GETDATE())
        ELSE NULL
    END as YearsMember
FROM MemberData md
WHERE md.JoinDt IS NOT NULL
ORDER BY md.JoinDt DESC;

-- Find households for communication
SELECT
    p.Name2,
    p.EmailAddress
FROM People p
WHERE p.PeopleId IN (SELECT PeopleId FROM HeadOrSpouseWithEmail)
    AND p.EmailAddress IS NOT NULL;

-- Compare current vs previous member counts
-- NOTE: Percentage can exceed 999%, so using DECIMAL(10,2)
SELECT TOP 100
    os.Organization,
    os.Members as CurrentMembers,
    ISNULL(pmc.prevcount, 0) as PreviousMembers,
    os.Members - ISNULL(pmc.prevcount, 0) as Change,
    CASE
        WHEN pmc.prevcount > 0 THEN
            CAST((os.Members - pmc.prevcount) * 100.0 / pmc.prevcount as DECIMAL(10,2))
        WHEN os.Members > 0 AND pmc.prevcount IS NULL THEN 100.00
        ELSE 0
    END as PercentChange
FROM OrganizationStructure os
LEFT JOIN PreviousMemberCounts pmc ON os.OrgId = pmc.OrganizationId
WHERE os.OrgStatus = 'Active'
    AND os.Members IS NOT NULL
ORDER BY ABS(os.Members - ISNULL(pmc.prevcount, 0)) DESC;

📧 Email & Communication Views

-- Recent email failures for follow-up
-- Columns: time (lowercase), Id, PeopleId, Fail
SELECT TOP 100
    fe.Id as EmailQueueId,
    p.Name2,
    p.EmailAddress,
    fe.time as FailureTime,
    fe.Fail as FailureReason,
    DATEDIFF(hour, fe.time, GETDATE()) as HoursAgo
FROM FailedEmails fe
JOIN People p ON fe.PeopleId = p.PeopleId
WHERE fe.time >= DATEADD(day, -7, GETDATE())
ORDER BY fe.time DESC;

-- Email delegation permissions
-- Shows who can send emails on behalf of other accounts
SELECT
    ed.CanEmailName2 as PersonWhoCanEmail,
    ed.OnBehalfOfNames as CanEmailOnBehalfOf,
    LEN(ed.OnBehalfOfIds) - LEN(REPLACE(ed.OnBehalfOfIds, ',', '')) + 1 as NumberOfDelegations
FROM EmailDelegation ed
WHERE ed.OnBehalfOfIds IS NOT NULL
ORDER BY ed.CanEmailName2;

🎯 Registration Views

-- Find abandoned registrations for follow-up
-- NOTE: Requires SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER ON;
SELECT TOP 100
    ipr.RegDataId,
    ipr.Name,
    p.EmailAddress,
    ipr.OrganizationName,
    ipr.Stamp as StartedTime,
    DATEDIFF(day, ipr.Stamp, GETDATE()) as DaysAgo
FROM InProgressRegistrations ipr
LEFT JOIN People p ON ipr.PeopleId = p.PeopleId
WHERE ipr.Stamp < DATEADD(day, -3, GETDATE())
ORDER BY ipr.Stamp DESC;

-- Active registrations by organization
SELECT
    o.OrganizationName,
    COUNT(*) as ActiveRegCount
FROM ActiveRegistrations ar
JOIN Organizations o ON ar.OrganizationId = o.OrganizationId
GROUP BY o.OrganizationId, o.OrganizationName
ORDER BY ActiveRegCount DESC;

🔧 System & Admin Views

-- Overdue tasks report
SELECT
    it.Id as TaskId,
    it.Description,
    owner.Name2 as Owner,
    about.Name2 as AboutPerson,
    it.Due,
    DATEDIFF(day, it.Due, GETDATE()) as DaysOverdue
FROM IncompleteTasks it
LEFT JOIN People owner ON it.OwnerId = owner.PeopleId
LEFT JOIN People about ON it.WhoId = about.PeopleId
WHERE it.Due < GETDATE()
ORDER BY it.Due;

-- User activity analysis
SELECT
    ul.Username,
    p.Name2,
    ul.LastActivityDate,
    DATEDIFF(day, ul.LastActivityDate, GETDATE()) as DaysInactive
FROM UserList ul
LEFT JOIN People p ON ul.PeopleId = p.PeopleId
WHERE ul.LastActivityDate < DATEADD(day, -30, GETDATE())
ORDER BY ul.LastActivityDate;

-- Recent changes from ChangeLog (actual audit trail)
SELECT TOP 100
    cl.Created,
    u.Name2 as ChangedBy,
    p.Name2 as PersonChanged,
    cl.Field,
    cl.Before,
    cl.After
FROM ChangeLog cl
LEFT JOIN People p ON cl.PeopleId = p.PeopleId
LEFT JOIN People u ON cl.UserPeopleId = u.PeopleId
WHERE cl.Created >= DATEADD(day, -7, GETDATE())
ORDER BY cl.Created DESC;

🌐 Export Schema Views (Data Integration)

-- Export active members with all details
SELECT *
FROM export.XpPeople
WHERE MemberStatus = 'Member'  -- Text value, not ID
    AND DeceasedDate IS NULL;  -- Not deceased

-- Export family units with size calculation
SELECT TOP 1000
    f.*,
    (SELECT COUNT(*) FROM export.XpPeople p WHERE p.FamilyId = f.FamilyId) as FamilySize
FROM export.XpFamily f
WHERE f.HeadOfHouseholdId IS NOT NULL;

-- Export contribution data for year
SELECT TOP 1000
    c.*,
    p.FirstName,
    p.LastName
FROM export.XpContribution c
JOIN export.XpPeople p ON c.PeopleId = p.PeopleId
WHERE c.Date >= '2024-01-01'
ORDER BY c.Date;

🎨 Specialized & Utility Views

-- Discover all views in your database
SELECT
    v.TABLE_SCHEMA,
    v.TABLE_NAME,
    COUNT(c.COLUMN_NAME) as ColumnCount
FROM INFORMATION_SCHEMA.VIEWS v
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
    ON v.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND v.TABLE_NAME = c.TABLE_NAME
WHERE v.TABLE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA')
GROUP BY v.TABLE_SCHEMA, v.TABLE_NAME
ORDER BY v.TABLE_SCHEMA, v.TABLE_NAME;

-- Analyze view usage (requires appropriate permissions)
SELECT
    OBJECT_NAME(v.object_id) as ViewName,
    s.last_user_seek,
    s.last_user_scan,
    s.user_seeks + s.user_scans as TotalReads
FROM sys.views v
LEFT JOIN sys.dm_db_index_usage_stats s
    ON v.object_id = s.object_id
WHERE s.database_id = DB_ID()
ORDER BY TotalReads DESC;

💰 Financial & Transaction Management

Transaction Table (85K+ records)

EnrollmentTransaction Table (317K+ records)

Financial Queries

-- Monthly giving summary by fund
SELECT 
    YEAR(c.ContributionDate) as Year,
    MONTH(c.ContributionDate) as Month,
    cf.FundName,
    COUNT(DISTINCT c.PeopleId) as Givers,
    COUNT(*) as Gifts,
    SUM(c.ContributionAmount) as TotalAmount,
    AVG(c.ContributionAmount) as AvgGift
FROM Contribution c
JOIN ContributionFund cf ON c.FundId = cf.FundId
WHERE c.ContributionDate >= DATEADD(month, -12, GETDATE())
  AND c.ContributionStatusId = 0  -- Posted
GROUP BY YEAR(c.ContributionDate), MONTH(c.ContributionDate), cf.FundName
ORDER BY Year DESC, Month DESC, TotalAmount DESC

-- Registration POTENTIAL revenue (based on enrollment and fee structure)
-- Shows what should be collected based on current enrollments
SELECT 
    o.OrganizationName,
    o.RegFeePerPerson as FeePerPerson,
    o.RegDepositAmount as DepositRequired,
    COUNT(om.PeopleId) as CurrentEnrollment,
    o.RegFeePerPerson * COUNT(om.PeopleId) as TotalPotentialRevenue,
    CASE 
        WHEN o.RegistrationTypeId = 6 THEN 'Mission Trip'
        WHEN o.RegistrationTypeId = 8 THEN 'Event'
        ELSE 'Other'
    END as RegistrationType
FROM Organizations o
JOIN OrganizationMembers om ON o.OrganizationId = om.OrganizationId
WHERE o.RegFeePerPerson > 0 
  AND om.MemberTypeId NOT IN (310, 320)  -- Exclude leaders/assistants
  AND om.InactiveDate IS NULL  -- Active members only
GROUP BY o.OrganizationId, o.OrganizationName, o.RegFeePerPerson, 
         o.RegDepositAmount, o.RegistrationTypeId
ORDER BY TotalPotentialRevenue DESC

-- BEST: Actual payments received using TransactionSummary
-- This is what my payment management systems use and you need to be adding CSH CHK to the description prefix
-- Shows REAL money collected, not just online transactions.
SELECT 
    o.OrganizationName,
    COUNT(DISTINCT ts.PeopleId) as PayingParticipants,
    SUM(CASE WHEN t.amt > 0 THEN t.amt ELSE 0 END) as TotalPaid,
    COUNT(DISTINCT t.Id) as NumTransactions,
    MIN(t.TransactionDate) as FirstPayment,
    MAX(t.TransactionDate) as LastPayment,
    -- Payment type breakdown
    SUM(CASE WHEN t.Message LIKE 'CHK%' THEN t.amt ELSE 0 END) as CheckPayments,
    SUM(CASE WHEN t.Message LIKE 'CSH%' THEN t.amt ELSE 0 END) as CashPayments,
    SUM(CASE WHEN t.Message LIKE 'Response%' THEN t.amt ELSE 0 END) as CreditCardPayments
FROM TransactionSummary ts
JOIN Organizations o ON ts.OrganizationId = o.OrganizationId
LEFT JOIN [Transaction] t ON ts.RegId = t.OriginalId 
    AND t.amt > 0 
    AND t.Approved = 1
WHERE (ts.TotDue > 0 OR ts.TotPaid > 0)  -- Has financial activity
  AND t.TransactionDate >= DATEADD(year, -1, GETDATE())
GROUP BY o.OrganizationId, o.OrganizationName
HAVING SUM(CASE WHEN t.amt > 0 THEN t.amt ELSE 0 END) > 0
ORDER BY TotalPaid DESC

📝 Registration & Event Management

Registration Tables Overview

New System: Structured Registration Tables

New System Queries

-- Registration answers from new structured tables
SELECT
    rp.PeopleId,
    p.Name,
    rq.Label as Question,
    rq.[Order] as QuestionOrder,
    ra.AnswerValue as Answer
FROM Registration r WITH (NOLOCK)
JOIN RegPeople rp WITH (NOLOCK) ON r.RegistrationId = rp.RegistrationId
LEFT JOIN RegAnswer ra WITH (NOLOCK) ON rp.RegPeopleId = ra.RegPeopleId
LEFT JOIN RegQuestion rq WITH (NOLOCK) ON ra.RegQuestionId = rq.RegQuestionId
JOIN People p ON rp.PeopleId = p.PeopleId
WHERE r.OrganizationId = 123  -- Your org
  AND rq.Label IS NOT NULL
ORDER BY p.Name, rq.[Order]

Legacy System: RegistrationData (XML)

-- Legacy: Event registration summary from RegistrationData
SELECT
    o.OrganizationName,
    COUNT(DISTINCT rd.PeopleId) as Registrants,
    SUM(CASE WHEN rd.completed = 1 THEN 1 ELSE 0 END) as Completed,
    SUM(CASE WHEN rd.completed IS NULL OR rd.completed = 0 THEN 1 ELSE 0 END) as Abandoned,
    MIN(rd.Stamp) as FirstRegistration,
    MAX(rd.Stamp) as LastRegistration
FROM RegistrationData rd
JOIN Organizations o ON rd.OrganizationId = o.OrganizationId
WHERE rd.Stamp >= DATEADD(month, -6, GETDATE())
GROUP BY o.OrganizationName
ORDER BY Registrants DESC

-- Abandoned registrations (started but never completed)
SELECT
    o.OrganizationName,
    p.Name,
    p.EmailAddress,
    rd.Stamp as StartedDate,
    DATEDIFF(day, rd.Stamp, GETDATE()) as DaysAgo
FROM RegistrationData rd
JOIN Organizations o ON rd.OrganizationId = o.OrganizationId
JOIN People p ON rd.PeopleId = p.PeopleId
WHERE (rd.completed IS NULL OR rd.completed = 0)  -- Never finished
  AND rd.Stamp >= DATEADD(month, -3, GETDATE())
  AND o.OrganizationStatusId = 30
ORDER BY rd.Stamp DESC

Medical & Emergency Data (RecReg Table)

-- Medical/emergency data for org members
SELECT
    p.Name, p.Age,
    rr.emcontact as EmergencyContact,
    rr.emphone as EmergencyPhone,
    rr.doctor, rr.docphone as DoctorPhone,
    rr.insurance, rr.policy as PolicyNumber,
    rr.MedicalDescription as Allergies,
    rr.MedAllergy as HasAllergy,
    ISNULL(p.CustodyIssue, 0) as CustodyIssue
FROM OrganizationMembers om
JOIN People p ON om.PeopleId = p.PeopleId
LEFT JOIN RecReg rr ON rr.PeopleId = p.PeopleId
WHERE om.OrganizationId = 123
  AND om.InactiveDate IS NULL
ORDER BY p.Name2

-- Authorized pickup people (children's ministry)
SELECT p.Name as Child, auth.Name2 as AuthorizedPerson
FROM PeopleAuthorizedCheckOut ac
JOIN People p ON p.PeopleId = ac.PeopleId
JOIN People auth ON auth.PeopleId = ac.AuthorizedPeopleId
WHERE ac.PeopleId IN (SELECT PeopleId FROM OrganizationMembers WHERE OrganizationId = 123)

-- Approved medications (new system with lookup table)
SELECT p.Name, md.Description as Medication
FROM PersonMedication pm
JOIN People p ON p.PeopleId = pm.PeopleId
JOIN lookup.Medication md ON md.Id = pm.MedicationId
WHERE pm.PeopleId IN (SELECT PeopleId FROM OrganizationMembers WHERE OrganizationId = 123)
ORDER BY p.Name, md.Description

Registration Table Structure

Registration Table

RegPeople Table

Event Registration Details

-- Event registration details
SELECT TOP 500
    o.OrganizationName as Event,
    r.RegistrationId,
    registrant.Name as RegisteredBy,
    r.CreatedDate as RegistrationStarted,
    r.CompletedDate as RegistrationCompleted,
    rp.FirstName + ' ' + rp.LastName as Participant,
    rp.Email,
    rp.Fee,
    CASE 
        WHEN rp.CompletedDate IS NOT NULL THEN 'Complete'
        ELSE 'Incomplete'
    END as Status
FROM Registration r
JOIN Organizations o ON r.OrganizationId = o.OrganizationId
JOIN People registrant ON r.PeopleId = registrant.PeopleId
JOIN RegPeople rp ON r.RegistrationId = rp.RegistrationId
WHERE o.RegistrationTypeId > 0  -- Has registration
  AND r.CreatedDate >= DATEADD(month, -3, GETDATE())
ORDER BY r.CreatedDate DESC

Registration Questions & Answers

-- Custom form responses
SELECT TOP 1000
    o.OrganizationName,
    p.Name as Participant,
    rq.Label as Question,
    rq.[Order] as QuestionOrder,
    ra.AnswerValue as Answer,
    CASE 
        WHEN rq.IsRequired = 1 THEN 'Required'
        ELSE 'Optional'
    END as QuestionType
FROM RegAnswer ra
JOIN RegPeople rp ON ra.RegPeopleId = rp.RegPeopleId
JOIN People p ON rp.PeopleId = p.PeopleId
JOIN RegQuestion rq ON ra.RegQuestionId = rq.RegQuestionId
JOIN Registration r ON rp.RegistrationId = r.RegistrationId
JOIN Organizations o ON r.OrganizationId = o.OrganizationId
WHERE r.CompletedDate >= DATEADD(month, -1, GETDATE())
ORDER BY o.OrganizationName, p.Name, rq.[Order]

🏢 Resource & Facility Management

Resource System Tables

Facility Queries

-- Room utilization analysis
SELECT 
    r.Name as ResourceName,
    r.Type,
    r.Capacity,
    COUNT(c.Id) as TotalReservations,
    SUM(DATEDIFF(hour, c.StartTime, c.EndTime)) as TotalHours,
    AVG(DATEDIFF(hour, c.StartTime, c.EndTime)) as AvgHours
FROM Resource r
LEFT JOIN Calendar c ON r.ResourceId = c.ResourceId
WHERE c.StartTime >= DATEADD(month, -3, GETDATE())
GROUP BY r.Name, r.Type, r.Capacity
ORDER BY TotalReservations DESC

📝 Task Management Tables

TaskNote Table Structure

Common Task Queries

-- Recently created open tasks (more useful than future due dates)
-- ⚠️ NOTE: Most TaskNote records have due dates in the past (162K+ overdue vs 1 future)
-- This query shows recently CREATED tasks regardless of due date
SELECT TOP 500
    owner.Name as Owner,
    about.Name as AboutPerson,
    tn.Notes,
    tn.DueDate,
    tn.CreatedDate,
    CASE 
        WHEN tn.DueDate > GETDATE() THEN DATEDIFF(day, GETDATE(), tn.DueDate)
        ELSE -DATEDIFF(day, tn.DueDate, GETDATE())
    END as DaysFromDue,
    ts.Description as TaskStatus
FROM TaskNote tn
JOIN People owner ON tn.OwnerId = owner.PeopleId
JOIN People about ON tn.AboutPersonId = about.PeopleId
LEFT JOIN lookup.TaskStatus ts ON tn.StatusId = ts.Id
WHERE tn.CompletedDate IS NULL
  AND tn.CreatedDate >= DATEADD(month, -3, GETDATE())  -- Created in last 3 months
ORDER BY tn.CreatedDate DESC

-- Recently overdue tasks (practical date range)
-- Filters to tasks due within last 2 years to avoid ancient tasks
SELECT TOP 100
    owner.Name as Owner,
    about.Name as AboutPerson,
    tn.Notes,
    tn.DueDate,
    DATEDIFF(day, tn.DueDate, GETDATE()) as DaysOverdue
FROM TaskNote tn
JOIN People owner ON tn.OwnerId = owner.PeopleId
JOIN People about ON tn.AboutPersonId = about.PeopleId
WHERE tn.CompletedDate IS NULL
  AND tn.DueDate < GETDATE()
  AND tn.DueDate > DATEADD(year, -2, GETDATE())  -- Due within last 2 years
ORDER BY tn.DueDate DESC

-- Task completion metrics
SELECT 
    YEAR(CreatedDate) as Year,
    MONTH(CreatedDate) as Month,
    COUNT(*) as TasksCreated,
    SUM(CASE WHEN CompletedDate IS NOT NULL THEN 1 ELSE 0 END) as TasksCompleted,
    AVG(DATEDIFF(day, CreatedDate, CompletedDate)) as AvgDaysToComplete
FROM TaskNote
WHERE CreatedDate >= DATEADD(year, -1, GETDATE())
GROUP BY YEAR(CreatedDate), MONTH(CreatedDate)
ORDER BY Year DESC, Month DESC

-- ENHANCED: Top task creators/owners/completers (from TaskNote Activity Dashboard)
-- Shows who is most active in the task system
SELECT TOP 20
    p.Name,
    COUNT(DISTINCT CASE WHEN tn.OwnerId = p.PeopleId THEN tn.TaskNoteId END) AS TasksCreated,
    COUNT(DISTINCT CASE WHEN tn.AssigneeId = p.PeopleId THEN tn.TaskNoteId END) AS TasksAssigned,
    COUNT(DISTINCT CASE WHEN tn.CompletedBy = p.PeopleId THEN tn.TaskNoteId END) AS TasksCompleted,
    COUNT(DISTINCT CASE WHEN tn.OwnerId = p.PeopleId AND tn.IsNote = 1 THEN tn.TaskNoteId END) AS NotesCreated
FROM People p
JOIN TaskNote tn ON p.PeopleId IN (tn.OwnerId, tn.AssigneeId, tn.CompletedBy)
WHERE tn.CreatedDate >= DATEADD(month, -3, GETDATE())
GROUP BY p.PeopleId, p.Name
ORDER BY (COUNT(DISTINCT CASE WHEN tn.OwnerId = p.PeopleId THEN tn.TaskNoteId END) + 
          COUNT(DISTINCT CASE WHEN tn.AssigneeId = p.PeopleId THEN tn.TaskNoteId END) + 
          COUNT(DISTINCT CASE WHEN tn.CompletedBy = p.PeopleId THEN tn.TaskNoteId END)) DESC

-- Overdue tasks by assignee with workload analysis
SELECT 
    p.Name AS Assignee,
    COUNT(*) AS OverdueTasks,
    MIN(tn.DueDate) AS OldestDueDate,
    DATEDIFF(day, MIN(tn.DueDate), GETDATE()) AS MaxDaysOverdue,
    AVG(DATEDIFF(day, tn.DueDate, GETDATE())) AS AvgDaysOverdue
FROM TaskNote tn
JOIN People p ON p.PeopleId = tn.AssigneeId
WHERE tn.DueDate IS NOT NULL
  AND tn.DueDate < GETDATE()
  AND tn.StatusId NOT IN (1, 5)  -- Not completed or archived
  AND tn.IsNote = 0  -- Tasks only, not notes
  AND tn.DueDate > DATEADD(year, -2, GETDATE())  -- Practical date range
GROUP BY p.PeopleId, p.Name
HAVING COUNT(*) > 0
ORDER BY COUNT(*) DESC

-- Task/Note activity trend by month
WITH MonthlyActivity AS (
    SELECT 
        FORMAT(CreatedDate, 'yyyy-MM') AS MonthYear,
        COUNT(CASE WHEN IsNote = 0 THEN 1 END) AS Tasks,
        COUNT(CASE WHEN IsNote = 1 THEN 1 END) AS Notes,
        COUNT(CASE WHEN CompletedDate IS NOT NULL AND IsNote = 0 THEN 1 END) AS TasksCompleted
    FROM TaskNote
    WHERE CreatedDate >= DATEADD(month, -12, GETDATE())
    GROUP BY FORMAT(CreatedDate, 'yyyy-MM')
)
SELECT 
    MonthYear,
    Tasks,
    Notes,
    TasksCompleted,
    CASE 
        WHEN Tasks > 0 THEN CAST(TasksCompleted AS float) / Tasks * 100 
        ELSE 0 
    END AS CompletionRate
FROM MonthlyActivity
ORDER BY MonthYear DESC

Ministry Notes

-- Recent notes about people
SELECT TOP 500
    about.Name as AboutPerson,
    creator.Name as CreatedBy,
    tn.CreatedDate,
    tn.Instructions as NoteContent,
    tn.Notes as AdditionalInfo
FROM TaskNote tn
JOIN People about ON tn.AboutPersonId = about.PeopleId
JOIN People creator ON tn.CreatedBy = creator.PeopleId
WHERE tn.IsNote = 1  -- Notes only
  AND tn.CreatedDate >= DATEADD(month, -1, GETDATE())
ORDER BY tn.CreatedDate DESC

🏛️ Program & Division Tables (Organizational Hierarchy)

Program Table

Division Table

Hierarchy Queries

-- Complete organizational structure
SELECT 
    p.Name as Program,
    d.Name as Division,
    o.OrganizationName,
    COUNT(om.PeopleId) as MemberCount
FROM Program p
JOIN Division d ON p.Id = d.ProgId
JOIN Organizations o ON d.Id = o.DivisionId
LEFT JOIN OrganizationMembers om ON o.OrganizationId = om.OrganizationId
    AND om.InactiveDate IS NULL
WHERE o.OrganizationStatusId = 30
GROUP BY p.Id, p.Name, d.Id, d.Name, o.OrganizationId, o.OrganizationName
ORDER BY p.SortOrder, d.SortOrder, o.OrganizationName

-- Programs with most participation
SELECT 
    p.Name as Program,
    COUNT(DISTINCT d.Id) as Divisions,
    COUNT(DISTINCT o.OrganizationId) as Organizations,
    COUNT(DISTINCT om.PeopleId) as TotalPeople
FROM Program p
LEFT JOIN Division d ON p.Id = d.ProgId
LEFT JOIN Organizations o ON d.Id = o.DivisionId
    AND o.OrganizationStatusId = 30
LEFT JOIN OrganizationMembers om ON o.OrganizationId = om.OrganizationId
    AND om.InactiveDate IS NULL
GROUP BY p.Id, p.Name
ORDER BY COUNT(DISTINCT om.PeopleId) DESC

👨‍👩‍👧‍👦 Family Relationships & Household Management

Understanding TouchPoint's Family Model

Family Structure Query

-- Complete family units with all members
-- Note: Both spouses are Primary Adults (10), distinguished by HeadOfHouseholdId/HeadOfHouseholdSpouseId
WITH FamilyStructure AS (
    SELECT TOP 500
        f.FamilyId,
        hoh.Name as HeadOfHousehold,
        spouse.Name as Spouse,
        STRING_AGG(CASE WHEN p.PositionInFamilyId = 30 THEN p.Name END, ', ') as Children,
        STRING_AGG(CASE WHEN p.PositionInFamilyId = 20 THEN p.Name END, ', ') as OtherAdults,
        COUNT(DISTINCT p.PeopleId) as FamilySize,
        COUNT(DISTINCT CASE WHEN p.PositionInFamilyId = 30 THEN p.PeopleId END) as NumChildren
    FROM Families f
    LEFT JOIN People hoh ON f.HeadOfHouseholdId = hoh.PeopleId
        AND hoh.IsDeceased = 0
    LEFT JOIN People spouse ON f.HeadOfHouseholdSpouseId = spouse.PeopleId
        AND spouse.IsDeceased = 0
    LEFT JOIN People p ON f.FamilyId = p.FamilyId
        AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
    GROUP BY f.FamilyId, hoh.Name, spouse.Name
)
SELECT 
    FamilyId,
    HeadOfHousehold,
    Spouse,
    Children,
    OtherAdults,
    FamilySize,
    NumChildren
FROM FamilyStructure
WHERE FamilySize > 1  -- Multi-person families only
ORDER BY FamilySize DESC, NumChildren DESC

Spouse Relationships

-- Direct spouse relationships (most reliable)
SELECT 
    p1.PeopleId as Person1Id,
    p1.Name as Person1,
    p2.PeopleId as Person2Id,
    p2.Name as Person2,
    p1.WeddingDate,
    DATEDIFF(year, p1.WeddingDate, GETDATE()) as YearsMarried
FROM People p1
JOIN People p2 ON p1.SpouseId = p2.PeopleId
WHERE p1.IsDeceased = 0 AND p1.ArchivedFlag = 0
  AND p2.IsDeceased = 0 AND p2.ArchivedFlag = 0
  AND p1.PeopleId < p2.PeopleId  -- Avoid duplicates
ORDER BY p1.Name

Children and Parents

-- Find all children with their parents
-- IMPORTANT: Both parents are Primary Adults (10), distinguished by HeadOfHouseholdId/HeadOfHouseholdSpouseId
-- Secondary Adult (20) is for non-nuclear family members (grandparents, adult siblings living with family)
SELECT TOP 500
    child.Name as ChildName,
    child.Age,
    primary_adult.Name as Parent1,
    secondary_adult.Name as Parent2,
    f.FamilyId
FROM People child
JOIN Families f ON child.FamilyId = f.FamilyId
LEFT JOIN People primary_adult ON primary_adult.PeopleId = f.HeadOfHouseholdId
    AND primary_adult.IsDeceased = 0 
LEFT JOIN People secondary_adult ON secondary_adult.PeopleId = f.HeadOfHouseholdSpouseId
    AND secondary_adult.IsDeceased = 0 
WHERE child.PositionInFamilyId = 30  -- Child
  AND child.Age < 18
  AND child.IsDeceased = 0 AND child.ArchivedFlag = 0
ORDER BY child.Age DESC, child.Name

📞 Contact Information & Communication Permissions

Safe Email List

-- People who CAN receive emails
SELECT TOP 1000
    p.PeopleId,
    p.Name,
    p.EmailAddress,
    p.EmailAddress2,
    CASE 
        WHEN p.SendEmailAddress1 = 1 AND p.EmailAddress IS NOT NULL THEN p.EmailAddress
        WHEN p.SendEmailAddress2 = 1 AND p.EmailAddress2 IS NOT NULL THEN p.EmailAddress2
        ELSE NULL
    END as PreferredEmail
FROM People p
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
  AND p.DoNotMailFlag = 0  -- General mail permission
  AND (
    (p.SendEmailAddress1 = 1 AND p.EmailAddress IS NOT NULL AND p.EmailAddress != '')
    OR 
    (p.SendEmailAddress2 = 1 AND p.EmailAddress2 IS NOT NULL AND p.EmailAddress2 != '')
  )
  AND p.PrimaryBadAddrFlag = 0  -- Good address on file
ORDER BY p.Name

Safe Text/SMS List

-- People who CAN receive text messages
SELECT TOP 1000
    p.PeopleId,
    p.Name,
    p.CellPhone,
    p.CellPhoneValidated,
    p.CellPhoneIsValid
FROM People p
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
  AND p.DoNotCallFlag = 0      -- Can call/text
  AND p.ReceiveSMS = 1         -- SMS specifically allowed
  AND p.CellPhoneIsValid = 1   -- Valid phone number
  AND p.CellPhone IS NOT NULL AND p.CellPhone != ''
ORDER BY p.Name

Safe Mailing List

-- People who CAN receive postal mail
SELECT TOP 1000
    p.PeopleId,
    p.Name,
    p.PrimaryAddress,
    p.PrimaryAddress2,
    p.PrimaryCity,
    p.PrimaryState,
    p.PrimaryZip
FROM People p
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
  AND p.DoNotMailFlag = 0        -- Mail allowed
  AND p.PrimaryBadAddrFlag = 0   -- Valid address
  AND p.PrimaryAddress IS NOT NULL AND p.PrimaryAddress != ''
ORDER BY p.PrimaryZip, p.Name

Visitation List

-- People who CAN be visited
  SELECT TOP 1000
      p.PeopleId,
      p.Name,
      p.PrimaryAddress,
      p.PrimaryCity,
      p.CellPhone,
      rc.Description as ResidentCode
  FROM People p
  LEFT JOIN lookup.ResidentCode rc ON p.ResCodeId = rc.Id
  WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
    AND p.DoNotVisitFlag = 0       -- Visits allowed
    AND p.PrimaryBadAddrFlag = 0   -- Valid address
    AND p.ResCodeId IN (10, 20)    -- Metro or Marginal (close enough to visit)
  ORDER BY p.PrimaryCity, p.Name

🏛️ Church Membership Status & Workflow

Membership Status Values

Membership Journey Tracking

-- Complete membership journey analysis
SELECT TOP 500
    p.PeopleId,
    p.Name,
    ms.Description as CurrentStatus,
    
    -- Key Dates
    p.CreatedDate as FirstRecorded,
    p.DecisionDate,
    p.BaptismDate,
    p.NewMemberClassDate,
    p.JoinDate,
    p.DropDate,
    
    -- Status Details
    dt.Description as DecisionType,
    bs.Description as BaptismStatus,
    nmcs.Description as NewMemberClassStatus,
    drop_type.Description as DropReason,
    
    -- Journey Metrics
    DATEDIFF(day, p.CreatedDate, p.JoinDate) as DaysToMembership,
    DATEDIFF(day, p.DecisionDate, p.BaptismDate) as DaysToBaptism,
    CASE 
        WHEN p.DropDate IS NOT NULL THEN 'Inactive'
        WHEN p.JoinDate IS NOT NULL THEN 'Member'
        WHEN p.BaptismDate IS NOT NULL THEN 'Baptized'
        WHEN p.DecisionDate IS NOT NULL THEN 'Decided'
        ELSE 'Exploring'
    END as JourneyStage
    
FROM People p
LEFT JOIN lookup.MemberStatus ms ON p.MemberStatusId = ms.Id
LEFT JOIN lookup.DecisionType dt ON p.DecisionTypeId = dt.Id
LEFT JOIN lookup.BaptismStatus bs ON p.BaptismStatusId = bs.Id
LEFT JOIN lookup.NewMemberClassStatus nmcs ON p.NewMemberClassStatusId = nmcs.Id
LEFT JOIN lookup.DropType drop_type ON p.DropCodeId = drop_type.Id
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY p.JoinDate DESC

New Member Pipeline

-- Track new member pipeline
SELECT TOP 500
    CASE 
        WHEN p.JoinDate IS NOT NULL THEN '5. Member'
        WHEN p.NewMemberClassDate IS NOT NULL THEN '4. Class Complete'
        WHEN p.BaptismDate IS NOT NULL THEN '3. Baptized'
        WHEN p.DecisionDate IS NOT NULL THEN '2. Decision Made'
        WHEN p.MemberStatusId = 20 THEN '1. Prospect'
        ELSE '0. Visitor'
    END as Stage,
    COUNT(*) as PeopleCount,
    AVG(DATEDIFF(day, p.CreatedDate, GETDATE())) as AvgDaysInStage
FROM People p
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
  AND p.CreatedDate >= DATEADD(year, -1, GETDATE())
  AND p.MemberStatusId IN (20, 50)  -- Prospects and Just Added
GROUP BY 
    CASE 
        WHEN p.JoinDate IS NOT NULL THEN '5. Member'
        WHEN p.NewMemberClassDate IS NOT NULL THEN '4. Class Complete'
        WHEN p.BaptismDate IS NOT NULL THEN '3. Baptized'
        WHEN p.DecisionDate IS NOT NULL THEN '2. Decision Made'
        WHEN p.MemberStatusId = 20 THEN '1. Prospect'
        ELSE '0. Visitor'
    END
ORDER BY Stage

🏛️ Organizations Table (Involvements)

Purpose: Groups, classes, ministries, events (called "Involvements" in UI)
Primary Key: OrganizationId
Scale: 188 columns with 16 foreign key relationships
Hierarchy: Program → Division → Organization

Core Organization Fields

Leadership & Management

Membership & Capacity

Registration System Fields Important

Check-In System Fields Important

Mobile App Integration New

Mission Trips Special

👥 Organization Membership & Roles

Purpose: Links people to organizations with specific roles
Composite Key: OrganizationId + PeopleId
Critical Rule: Use InactiveDate IS NULL for current members

OrganizationMembers Table

Organization Roster Query

-- Complete organization roster with roles
SELECT TOP 500
    o.OrganizationName,
    p.Name,
    mt.Description as Role,
    at.Description as RoleCategory,
    om.EnrollmentDate,
    om.LastAttended,
    om.AttendPct,
    CASE 
        WHEN at.Worker = 1 THEN 'Worker'
        WHEN at.Guest = 1 THEN 'Guest'
        ELSE 'Member'
    END as PersonType
FROM OrganizationMembers om
JOIN People p ON om.PeopleId = p.PeopleId
JOIN Organizations o ON om.OrganizationId = o.OrganizationId
JOIN lookup.MemberType mt ON om.MemberTypeId = mt.Id
JOIN lookup.AttendType at ON mt.AttendanceTypeId = at.Id
WHERE om.InactiveDate IS NULL  -- Current members only
  AND o.OrganizationStatusId = 30  -- Active orgs
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY o.OrganizationName, at.Worker DESC, p.Name

Leadership Analysis

  -- Find all leaders across organizations
  SELECT TOP 500
      p.Name as LeaderName,
      STRING_AGG(o.OrganizationName, ', ') as Organizations,
      COUNT(*) as LeadershipRoles
  FROM OrganizationMembers om
  JOIN People p ON om.PeopleId = p.PeopleId
  JOIN Organizations o ON om.OrganizationId = o.OrganizationId
  JOIN lookup.MemberType mt ON om.MemberTypeId = mt.Id
  WHERE om.InactiveDate IS NULL
    AND mt.AttendanceTypeId = 10  -- Leaders (not volunteers)
    AND o.OrganizationStatusId = 30
    AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
  GROUP BY p.PeopleId, p.Name
  HAVING COUNT(*) > 1  -- Leading multiple orgs
  ORDER BY COUNT(*) DESC

📊 Program → Division → Organization Structure

Organization Structure View

-- Complete organizational hierarchy
SELECT TOP 500
    Program,        -- Top level
    Division,       -- Second level
    Organization,   -- Third level (actual group)
    OrgStatus,
    Members,
    Previous,
    Vistors,
    Meetings,
    ProgId,         -- Program ID for joins
    DivId,          -- Division ID for joins
    OrgId           -- Organization ID for joins
FROM OrganizationStructure
WHERE OrgStatus = 'Active'
ORDER BY Program, Division, Organization

Program Summary

-- Program-level statistics
SELECT TOP 500
    Program,
    COUNT(DISTINCT DivId) as Divisions,
    COUNT(DISTINCT OrgId) as Organizations,
    SUM(Members) as TotalMembers,
    SUM(Vistors) as TotalVisitors,
    SUM(Meetings) as TotalMeetings
FROM OrganizationStructure
WHERE OrgStatus = 'Active'
GROUP BY Program, ProgId
ORDER BY SUM(Members) DESC

Division Analysis

-- Division-level breakdown
SELECT TOP 500
    Program,
    Division,
    COUNT(OrgId) as OrgCount,
    SUM(Members) as TotalMembers,
    AVG(Members) as AvgOrgSize,
    MAX(Members) as LargestOrg,
    SUM(CASE WHEN Members >= Limit AND Limit > 0 THEN 1 ELSE 0 END) as AtCapacity
FROM OrganizationStructure os
JOIN Organizations o ON os.OrgId = o.OrganizationId
WHERE os.OrgStatus = 'Active'
GROUP BY Program, Division, DivId
ORDER BY Program, Division

📅 Attend Table - The AttendanceFlag Rule

Attend Table Structure

Basic Attendance Query

-- Attendance records with full details
SELECT TOP 500
    p.Name,
    o.OrganizationName,
    m.MeetingDate,
    m.Location,
    at.Description as AttendanceType,
    mt.Description as SpecificRole,
    a.SubGroupName,
    CASE a.Commitment
        WHEN 1 THEN 'Attending'
        WHEN 0 THEN 'Regrets'
        WHEN 2 THEN 'Find Sub'
        WHEN 3 THEN 'Sub Found'
        ELSE 'Uncommitted'
    END as CommitmentStatus
FROM Attend a
JOIN People p ON a.PeopleId = p.PeopleId
JOIN Organizations o ON a.OrganizationId = o.OrganizationId
JOIN Meetings m ON a.MeetingId = m.MeetingId
JOIN lookup.AttendType at ON a.AttendanceTypeId = at.Id
JOIN lookup.MemberType mt ON a.MemberTypeId = mt.Id
WHERE a.AttendanceFlag = 1  -- CRITICAL: Only actual attendance
  AND m.MeetingDate >= DATEADD(month, -1, GETDATE())
  AND m.DidNotMeet = 0  -- Meeting actually happened
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY m.MeetingDate DESC, o.OrganizationName, p.Name

Attendance Patterns

-- Individual attendance patterns
WITH AttendanceHistory AS (
    SELECT 
        p.PeopleId,
        p.Name,
        COUNT(*) as TimesAttended,
        COUNT(DISTINCT a.OrganizationId) as DifferentOrgs,
        COUNT(DISTINCT CAST(a.MeetingDate as DATE)) as UniqueDays,
        MIN(a.MeetingDate) as FirstAttended,
        MAX(a.MeetingDate) as LastAttended
    FROM Attend a
    JOIN People p ON a.PeopleId = p.PeopleId
    WHERE a.AttendanceFlag = 1
      AND a.MeetingDate >= DATEADD(month, -6, GETDATE())
      AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
    GROUP BY p.PeopleId, p.Name
)
SELECT 
    Name,
    TimesAttended,
    DifferentOrgs,
    UniqueDays,
    DATEDIFF(day, FirstAttended, LastAttended) as DaySpan,
    CAST(TimesAttended as float) / NULLIF(DATEDIFF(week, FirstAttended, LastAttended), 0) as AvgPerWeek
FROM AttendanceHistory
WHERE TimesAttended > 1
ORDER BY TimesAttended DESC

📅 Meetings & Scheduling

Meetings Table Structure

Meeting Schedule Query

-- Upcoming meetings schedule
SELECT 
    o.OrganizationName,
    m.MeetingDate,
    m.MeetingEnd,
    m.Location,
    DATEDIFF(minute, m.MeetingDate, m.MeetingEnd) as DurationMinutes,
    p.Name as Leader
FROM Meetings m
JOIN Organizations o ON m.OrganizationId = o.OrganizationId
LEFT JOIN People p ON o.LeaderId = p.PeopleId
WHERE m.MeetingDate >= GETDATE()
  AND m.MeetingDate <= DATEADD(week, 2, GETDATE())
  AND m.DidNotMeet = 0  -- Not cancelled
  AND o.OrganizationStatusId = 30  -- Active orgs
ORDER BY m.MeetingDate

Meeting Analytics

-- Meeting attendance analytics
SELECT 
    o.OrganizationName,
    COUNT(*) as MeetingsHeld,
    AVG(m.NumPresent) as AvgAttendance,
    MAX(m.NumPresent) as MaxAttendance,
    MIN(m.NumPresent) as MinAttendance,
    SUM(m.NumNewVisit) as TotalNewVisitors,
    SUM(m.NumRepeatVst) as TotalRepeatVisitors,
    AVG(CAST(m.NumPresent as float) / NULLIF(o.MemberCount, 0)) * 100 as AvgAttendancePct
FROM Meetings m
JOIN Organizations o ON m.OrganizationId = o.OrganizationId
WHERE m.MeetingDate >= DATEADD(month, -3, GETDATE())
  AND m.DidNotMeet = 0  -- Actually met
  AND o.OrganizationStatusId = 30
GROUP BY o.OrganizationId, o.OrganizationName, o.MemberCount
HAVING COUNT(*) > 3  -- At least 3 meetings
ORDER BY AVG(m.NumPresent) DESC

💰 Contribution Table - The TypeId≠99 Rule

Contribution Table Structure

Giving Analysis Query

-- Individual giving summary
SELECT 
    g.Name,
    COUNT(*) AS GiftCount,
    SUM(g.ContributionAmount) AS TotalGiven,
    AVG(g.ContributionAmount) AS AvgGift,
    MIN(g.ContributionDate) AS FirstGift,
    MAX(g.ContributionDate) AS LastGift,
    STRING_AGG(CAST(df.FundName AS nvarchar(max)), ',') AS Funds
FROM (
    -- All gift rows for normal counts/sums
    SELECT 
        p.PeopleId,
        p.Name,
        c.ContributionAmount,
        c.ContributionDate,
        cf.FundName
    FROM Contribution c
    JOIN People p ON c.PeopleId = p.PeopleId
    JOIN ContributionFund cf ON c.FundId = cf.FundId
    WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)
      AND c.ContributionStatusId = 0
      AND c.ContributionDate >= DATEADD(year, -1, GETDATE())
      AND p.IsDeceased = 0
      AND p.ArchivedFlag = 0
) AS g
JOIN (
    -- Distinct funds per person
    SELECT DISTINCT
        p.PeopleId,
        cf.FundName
    FROM Contribution c
    JOIN People p ON c.PeopleId = p.PeopleId
    JOIN ContributionFund cf ON c.FundId = cf.FundId
    WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)
      AND c.ContributionStatusId = 0
      AND c.ContributionDate >= DATEADD(year, -1, GETDATE())
      AND p.IsDeceased = 0
      AND p.ArchivedFlag = 0
) AS df
    ON g.PeopleId = df.PeopleId
GROUP BY g.PeopleId, g.Name
HAVING SUM(g.ContributionAmount) > 0
ORDER BY SUM(g.ContributionAmount) DESC;

Fund Analysis

-- Giving by fund
SELECT 
    cf.FundName,
    COUNT(DISTINCT c.PeopleId) as Givers,
    COUNT(*) as Gifts,
    SUM(c.ContributionAmount) as TotalAmount,
    AVG(c.ContributionAmount) as AvgGift,
    MAX(c.ContributionAmount) as LargestGift
FROM Contribution c
JOIN ContributionFund cf ON c.FundId = cf.FundId
WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)
  AND c.ContributionStatusId = 0
  AND c.ContributionDate >= DATEADD(month, -3, GETDATE())
GROUP BY cf.FundId, cf.FundName
ORDER BY SUM(c.ContributionAmount) DESC

Giving Trends

-- Monthly giving trends
SELECT 
    YEAR(c.ContributionDate) as Year,
    MONTH(c.ContributionDate) as Month,
    DATENAME(month, c.ContributionDate) as MonthName,
    COUNT(DISTINCT c.PeopleId) as UniqueGivers,
    COUNT(*) as TotalGifts,
    SUM(c.ContributionAmount) as TotalAmount,
    AVG(c.ContributionAmount) as AvgGift
FROM Contribution c
WHERE c.ContributionTypeId NOT IN (6, 7, 8, 99)
  AND c.ContributionStatusId = 0
  AND c.ContributionDate >= DATEADD(year, -2, GETDATE())
GROUP BY YEAR(c.ContributionDate), MONTH(c.ContributionDate), DATENAME(month, c.ContributionDate)
ORDER BY Year DESC, Month DESC

🎫 Check-In System Overview

TouchPoint's check-in system provides secure child and event check-in with barcode scanning, security labels, and real-time tracking.

Check-In Tables

CheckInActivities Table

CheckInTimes Table

Check-In Activity Query

  -- Recent check-in activity
  SELECT TOP 100
      cia.Name as PersonName,
      o.OrganizationName,
      cia.CheckInTime,
      cia.AccessType,
      cia.Location,
      DATEDIFF(minute, cia.ActivityTime, cia.CheckInTime) as MinutesEarlyLate
  FROM CheckInActivities cia
  JOIN Organizations o ON cia.OrganizationId = o.OrganizationId
  WHERE cia.CheckInTime >= DATEADD(day, -7, GETDATE())
  ORDER BY cia.CheckInTime DESC

Check-In Statistics

  -- Check-in statistics by organization
  SELECT TOP 500
      o.OrganizationName,
      COUNT(DISTINCT cia.PeopleId) as UniqueCheckIns,
      COUNT(*) as TotalCheckIns,
      MIN(cia.CheckInTime) as EarliestCheckIn,
      MAX(cia.CheckInTime) as LatestCheckIn
  FROM CheckInActivities cia
  JOIN Organizations o ON cia.OrganizationId = o.OrganizationId
  WHERE cia.CheckInTime >= DATEADD(month, -1, GETDATE())
  GROUP BY o.OrganizationId, o.OrganizationName
  ORDER BY COUNT(*) DESC

📧 Email Marketing System

Email System Tables

EmailQueue Table

Email Campaign Analytics

 -- Email campaign performance (with date filter for performance)
  SELECT TOP 100
      eq.Id,
      eq.Subject,
      eq.SendWhen,
      sender.Name as SentBy,
      COUNT(DISTINCT eqt.PeopleId) as Recipients,
      COUNT(DISTINCT CASE WHEN er.Type = 'o' THEN er.PeopleId END) as UniqueOpens,
      COUNT(DISTINCT CASE WHEN eqt.Bounced = 1 THEN eqt.PeopleId END) as Bounces,
      CAST(COUNT(DISTINCT CASE WHEN er.Type = 'o' THEN er.PeopleId END) as float) /
          NULLIF(COUNT(DISTINCT eqt.PeopleId), 0) * 100 as OpenRate
  FROM EmailQueue eq
  JOIN People sender ON eq.QueuedBy = sender.PeopleId
  LEFT JOIN EmailQueueTo eqt ON eq.Id = eqt.Id
  LEFT JOIN EmailResponses er ON eq.Id = er.EmailQueueId
  WHERE eq.SendWhen >= DATEADD(month, -1, GETDATE())  -- REQUIRED for performance
  GROUP BY eq.Id, eq.Subject, eq.SendWhen, sender.Name
  ORDER BY eq.SendWhen DESC

Email Engagement by Person

-- Individual email engagement (limited timeframe for performance)
  SELECT TOP 500
      p.Name,
      p.EmailAddress,
      COUNT(DISTINCT eq.Id) as EmailsReceived,
      COUNT(DISTINCT CASE WHEN er.Type = 'o' THEN eq.Id END) as EmailsOpened,
      MAX(CASE WHEN er.Type = 'o' THEN er.Dt END) as LastOpened,
      CAST(CASE
          WHEN COUNT(DISTINCT eq.Id) > 0
          THEN COUNT(DISTINCT CASE WHEN er.Type = 'o' THEN eq.Id END) * 100.0 / COUNT(DISTINCT eq.Id)
          ELSE 0
      END AS DECIMAL(5,2)) as OpenRate
  FROM People p
  JOIN EmailQueueTo eqt ON p.PeopleId = eqt.PeopleId
  JOIN EmailQueue eq ON eqt.Id = eq.Id
  LEFT JOIN EmailResponses er ON eq.Id = er.EmailQueueId AND p.PeopleId = er.PeopleId
  WHERE eq.SendWhen >= DATEADD(month, -3, GETDATE())  -- REQUIRED
    AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
  GROUP BY p.PeopleId, p.Name, p.EmailAddress
  HAVING COUNT(DISTINCT eq.Id) > 0
  ORDER BY COUNT(DISTINCT CASE WHEN er.Type = 'o' THEN eq.Id END) DESC

📊 Engagement Scoring System

Engagement Tables

EngagementScore Table

Engagement Analysis

  -- Top engaged members (recent)
  SELECT TOP 100
      p.Name,
      SUM(es.TotalScore) as TotalEngagement,
      AVG(es.TotalScore) as AvgWeeklyScore,
      COUNT(DISTINCT es.WeekDate) as ActiveWeeks,
      STRING_AGG(sfl.Name, ', ') as Activities
  FROM EngagementScore es
  JOIN People p ON es.PeopleId = p.PeopleId
  LEFT JOIN EngagementScoreTag est ON es.Id = est.EngagementScoreId
  LEFT JOIN StatusFlagList sfl ON est.StatusFlagId = sfl.Flag
  WHERE es.WeekDate >= DATEADD(month, -3, GETDATE())  -- REQUIRED
    AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
  GROUP BY p.PeopleId, p.Name
  HAVING SUM(es.TotalScore) > 0

Engagement Trends

-- Weekly engagement trends
SELECT 
    es.WeekDate,
    COUNT(DISTINCT es.PeopleId) as EngagedPeople,
    SUM(es.TotalScore) as TotalPoints,
    AVG(es.TotalScore) as AvgScore,
    MAX(es.TotalScore) as MaxScore
FROM EngagementScore es
WHERE es.WeekDate >= DATEADD(month, -6, GETDATE())
GROUP BY es.WeekDate
ORDER BY es.WeekDate DESC

🏷️ Tag System

Tag System Tables

Tag Table

Safe Tag Query Pattern

-- SAFE: Query specific tag with date filter
  SELECT TOP 100
      t.Name as TagName,
      p.Name as PersonName,
      tp.DateCreated
  FROM TagPerson tp
  JOIN Tag t ON tp.Id = t.Id
  JOIN People p ON tp.PeopleId = p.PeopleId
  WHERE t.Name = 'Volunteer'  -- Specific tag
    AND tp.DateCreated >= DATEADD(month, -1, GETDATE())  -- REQUIRED
    AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
  ORDER BY tp.DateCreated DESC

-- NEVER DO THIS:
-- SELECT * FROM TagPerson  -- 138M+ records!

Tag Summary

 -- Tag usage summary (aggregated, not detail)
  SELECT TOP 50
      t.Name as TagName,
      COUNT(*) as PeopleTagged,
      MIN(tp.DateCreated) as FirstUsed,
      MAX(tp.DateCreated) as LastUsed
  FROM Tag t
  JOIN TagPerson tp ON t.Id = tp.Id
  WHERE tp.DateCreated >= DATEADD(month, -6, GETDATE())  -- REQUIRED
  GROUP BY t.Id, t.Name
  HAVING COUNT(*) > 10
  ORDER BY COUNT(*) DESC

🎛️ Extra Value Tables (Custom Fields) - Additional Details

TouchPoint provides 17 Extra Value tables allowing churches to add unlimited custom fields without modifying the database schema.

Extra Value Table Types

Discovering Custom Fields in Your Database

-- Find all custom fields in use
SELECT 
    'PeopleExtra' as TableType,
    Field,
    COUNT(*) as RecordCount,
    COUNT(DISTINCT PeopleId) as PeopleWithValue,
    MIN(TransactionTime) as FirstUsed,
    MAX(TransactionTime) as LastUsed
FROM PeopleExtra
GROUP BY Field
HAVING COUNT(*) > 10  -- Only show fields with significant usage
ORDER BY COUNT(*) DESC

-- Find custom fields for a specific person
SELECT 
    p.Name,
    'PeopleExtra' as Source,
    pev.Field,
    pev.Data as Value,
    pev.TransactionTime as LastUpdated
FROM People p
JOIN PeopleExtra pev ON p.PeopleId = pev.PeopleId
WHERE p.PeopleId = 12345  -- Specific person
ORDER BY pev.Field

Working with Custom Fields

-- Example: Church-specific membership covenant field
SELECT TOP 500
    p.Name,
    p.MemberStatusId,
    pev.Data as MembershipCovenantDate
FROM People p
LEFT JOIN PeopleExtra pev ON p.PeopleId = pev.PeopleId 
    AND pev.Field = 'MembershipCovenant'
WHERE p.MemberStatusId = 10  -- Members only
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY p.Name

-- Example: Multiple custom fields
SELECT 
    p.Name,
    MAX(CASE WHEN pev.Field = 'BackgroundCheck' THEN pev.Data END) as BackgroundCheckDate,
    MAX(CASE WHEN pev.Field = 'SafetyTraining' THEN pev.Data END) as SafetyTrainingDate,
    MAX(CASE WHEN pev.Field = 'VolunteerAgreement' THEN pev.Data END) as VolunteerAgreement
FROM People p
LEFT JOIN PeopleExtra pev ON p.PeopleId = pev.PeopleId
WHERE pev.Field IN ('BackgroundCheck', 'SafetyTraining', 'VolunteerAgreement')
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
GROUP BY p.PeopleId, p.Name
HAVING MAX(CASE WHEN pev.Field = 'BackgroundCheck' THEN pev.Data END) IS NOT NULL

Organization Extra Values

  -- Find custom fields on organizations
  SELECT
      o.OrganizationName,
      oev.Field,
      oev.Data,
      oev.StrValue,
      oev.DateValue,
      oev.IntValue,
      oev.BitValue,
      oev.TransactionTime
  FROM Organizations o
  JOIN OrganizationExtra oev ON o.OrganizationId = oev.OrganizationId
  WHERE o.OrganizationStatusId = 30
  ORDER BY o.OrganizationName, oev.Field

Get All Custom Fields for a Person

-- Get all custom fields for a person with proper type handling
SELECT 
    Field,
    CASE Type
        WHEN 'Text' THEN Data
        WHEN 'Date' THEN CONVERT(nvarchar, Date, 101)
        WHEN 'Int' THEN CAST(IntValue as nvarchar)
        WHEN 'Bit' THEN CASE BitValue WHEN 1 THEN 'Yes' ELSE 'No' END
    END as Value
FROM PeopleExtra
WHERE PeopleId = @PeopleId
ORDER BY Field

📱 SMS/Text Messaging System

SMS System Overview

SMS System Tables

SMSList Table (Sent Messages)

SmsReceived Table (Incoming Messages)

SMS Analytics Query

-- SMS engagement analytics
WITH SMSMetrics AS (
    SELECT 
        -- Sent messages
        (SELECT COUNT(*) FROM SMSList WHERE Sent = 1 AND SendAt >= DATEADD(month, -1, GETDATE())) as MessagesSent,
        (SELECT SUM(SentSMS) FROM SMSList WHERE Sent = 1 AND SendAt >= DATEADD(month, -1, GETDATE())) as TotalRecipients,
        
        -- Received messages
        (SELECT COUNT(*) FROM SmsReceived WHERE DateReceived >= DATEADD(month, -1, GETDATE())) as MessagesReceived,
        (SELECT COUNT(DISTINCT FromPeopleId) FROM SmsReceived WHERE DateReceived >= DATEADD(month, -1, GETDATE()) AND FromPeopleId IS NOT NULL) as UniqueResponders
)
SELECT 
    MessagesSent,
    TotalRecipients,
    MessagesReceived,
    UniqueResponders,
    CASE 
        WHEN TotalRecipients > 0 
        THEN CAST(UniqueResponders as float) / TotalRecipients * 100 
        ELSE 0 
    END as ResponseRate
FROM SMSMetrics

Text Conversation Threading

-- SMS conversation history for a person
SELECT TOP 100
    COALESCE(p.Name, 'Unknown') as Person,
    CASE 
        WHEN sl.ID IS NOT NULL THEN 'Sent'
        ELSE 'Received'
    END as Direction,
    COALESCE(sl.Message, sr.Body) as Message,
    COALESCE(sl.SendAt, sr.DateReceived) as MessageTime
FROM (
    SELECT SenderID as PeopleId, Message, SendAt, NULL as DateReceived 
    FROM SMSList 
    WHERE Sent = 1 AND SendAt >= DATEADD(month, -6, GETDATE())
    UNION ALL
    SELECT FromPeopleId, Body, NULL, DateReceived 
    FROM SmsReceived
    WHERE DateReceived >= DATEADD(month, -6, GETDATE())
) messages
LEFT JOIN People p ON messages.PeopleId = p.PeopleId
LEFT JOIN SMSList sl ON messages.SendAt IS NOT NULL
LEFT JOIN SmsReceived sr ON messages.DateReceived IS NOT NULL
WHERE p.PeopleId = 12345  -- Specific person
ORDER BY MessageTime DESC

📱 Mobile App Configuration

Mobile Configuration Tables

Mobile App Actions View

Mobile-Enabled Organizations

  -- Organizations visible in mobile app
  SELECT TOP 500
      o.OrganizationName,
      cm.Description as MobileCategory,
      o.AllowMobileView,
      o.RecommendInMobile,
      o.ShowInSites,
      o.ShowMobileRegStart,
      o.ShowMobileRegEnd,
      o.ShowMobileButtonText
  FROM Organizations o
  LEFT JOIN lookup.CategoryMobile cm ON o.CategoryMobileId = cm.Id
  WHERE o.AllowMobileView = 1
    AND o.OrganizationStatusId = 30
  ORDER BY cm.SortOrder, o.OrganizationName

  -- Active mobile app actions
  SELECT
      id,
      type,
      title,
      loginType,
      enabled,
      [order]
  FROM MobileAppActions
  WHERE enabled = 1
  ORDER BY [order]

🖼️ Picture & Media Management

Picture Table Structure

Picture Usage Query

-- People with pictures
SELECT TOP 1000
    p.Name,
    pic.PictureId,
    pic.ThumbUrl,
    pic.MediumUrl,
    pic.LargeUrl,
    CASE 
        WHEN pic.ThumbUrl IS NOT NULL THEN 'Processed'
        ELSE 'Processing'
    END as Status
FROM People p
JOIN Picture pic ON p.PictureId = pic.PictureId
WHERE p.IsDeceased = 0 AND p.ArchivedFlag = 0
ORDER BY p.Name

🔐 Users, Roles & Permissions

Users Table Structure

Roles Table

User Security Report

-- Active users with roles
SELECT TOP 500
    u.UserId,
    u.Username,
    u.Name,
    u.EmailAddress,
    u.LastLoginDate,
    DATEDIFF(day, u.LastLoginDate, GETDATE()) as DaysSinceLogin,
    u.MFAEnabled,
    STRING_AGG(r.RoleName, ', ') as Roles
FROM Users u
LEFT JOIN UserRole ur ON u.UserId = ur.UserId
LEFT JOIN Roles r ON ur.RoleId = r.RoleId
WHERE u.IsApproved = 1
GROUP BY u.UserId, u.Username, u.Name, u.EmailAddress, u.LastLoginDate, u.MFAEnabled
ORDER BY u.LastLoginDate DESC

-- Users who haven't logged in recently
SELECT TOP 100
    u.Name,
    u.Username,
    u.LastLoginDate,
    DATEDIFF(day, u.LastLoginDate, GETDATE()) as DaysInactive
FROM Users u
WHERE u.IsApproved = 1
  AND u.LastLoginDate < DATEADD(month, -3, GETDATE())
ORDER BY u.LastLoginDate

BackgroundChecks Table

Background Check Status

-- Background check status for volunteers
SELECT 
    p.Name,
    bc.Status,
    bc.DateCompleted,
    bc.ExpirationDate,
    DATEDIFF(day, GETDATE(), bc.ExpirationDate) as DaysUntilExpiry,
    STRING_AGG(o.OrganizationName, ', ') as VolunteerRoles
FROM BackgroundChecks bc
JOIN People p ON bc.PeopleId = p.PeopleId
LEFT JOIN OrganizationMembers om ON p.PeopleId = om.PeopleId
LEFT JOIN Organizations o ON om.OrganizationId = o.OrganizationId
WHERE om.MemberTypeId IN (140, 150, 160, 710)  -- Leader/volunteer types
  AND om.InactiveDate IS NULL
GROUP BY p.Name, bc.Status, bc.DateCompleted, bc.ExpirationDate
ORDER BY bc.ExpirationDate

Security Best Practices

• Never query or display password fields
• Always check IsApproved and IsLockedOut status
• Join with People table for complete user information
• Monitor LastActivityDate for inactive accounts
• Use role-based queries for permission checks

📝 Activity Logging & Audit Trail

ActivityLog Table

Audit Queries

  -- Recent user activity (MUST have date filter)
  SELECT TOP 100
      al.ActivityDate,
      u.Name as UserName,  -- Changed from 'User' to avoid reserved keyword
      al.Activity,
      al.Machine,
      al.PageUrl
  FROM ActivityLog al
  LEFT JOIN Users u ON al.UserId = u.UserId
  WHERE al.ActivityDate >= DATEADD(day, -7, GETDATE())  -- REQUIRED
  ORDER BY al.ActivityDate DESC

  -- Activity summary
  SELECT
      al.Activity,
      COUNT(*) as ActivityCount,
      COUNT(DISTINCT al.UserId) as UniqueUsers,
      MIN(al.ActivityDate) as FirstOccurrence,
      MAX(al.ActivityDate) as LastOccurrence
  FROM ActivityLog al
  WHERE al.ActivityDate >= DATEADD(month, -1, GETDATE())  -- REQUIRED
  GROUP BY al.Activity
  ORDER BY COUNT(*) DESC

🔄 Change Tracking & History

Change Tracking Tables

ChangeLog Table

Change History Queries

-- Recent changes to People records
  SELECT TOP 100
      cl.Created,
      p.Name as ChangedBy,
      cl.Field,
      cl.Before,
      cl.After
  FROM ChangeLog cl
  JOIN People p ON cl.UserPeopleId = p.PeopleId
  WHERE cl.PeopleId IS NOT NULL  -- Changes to people records
    AND cl.Created >= DATEADD(day, -7, GETDATE())
  ORDER BY cl.Created DESC

  -- Track specific person's history
  SELECT
      cl.Created,
      modifier.Name as ModifiedBy,
      cl.Field,
      cl.Before,
      cl.After
  FROM ChangeLog cl
  LEFT JOIN People modifier ON cl.UserPeopleId = modifier.PeopleId
  WHERE cl.PeopleId = 12345  -- Specific PeopleId
  ORDER BY cl.Created DESC

💳 Financial System Details

Bundle System (Batch Processing)

BundleHeader Table

BundleDetail Table

Batch Processing Queries

-- Recent batch summary
SELECT TOP 500
    bh.BundleHeaderId,
    bh.ContributionDate,
    bh.DepositDate,
    bh.TotalCash + bh.TotalChecks as TotalAmount,
    bh.TotalEnvelopes,
    COUNT(bd.BundleDetailId) as ContributionCount
FROM BundleHeader bh
LEFT JOIN BundleDetail bd ON bh.BundleHeaderId = bd.BundleHeaderId
WHERE bh.ContributionDate >= DATEADD(month, -1, GETDATE())
GROUP BY bh.BundleHeaderId, bh.ContributionDate, bh.DepositDate, 
         bh.TotalCash, bh.TotalChecks, bh.TotalEnvelopes
ORDER BY bh.ContributionDate DESC

💰 Transactions & Outstanding Balances

-- ✅ CORRECT
SELECT * FROM [Transaction] WHERE OrgId = 123

-- ❌ WRONG - syntax error
SELECT * FROM Transaction WHERE OrgId = 123

-- ✅ CORRECT
SELECT * FROM [Transaction] WHERE Approved = 1

-- ❌ WRONG - Approved is not a string
SELECT * FROM [Transaction] WHERE Approved = 'Approved'

Transaction Tables

[Transaction] Table Brackets Required!

EnrollmentTransaction (Junction Table)

-- Correct way to get transaction details with enrollment info
SELECT
    p.Name,
    o.OrganizationName,
    t.Amt,
    t.TransactionDate,
    t.Approved,
    t.PaymentType,
    et.Amt as IndividualAmt
FROM [Transaction] t
JOIN EnrollmentTransaction et ON t.Id = et.TransactionId
JOIN People p ON t.PeopleId = p.PeopleId
JOIN Organizations o ON t.OrgId = o.OrganizationId
WHERE t.Approved = 1
  AND t.TransactionDate >= DATEADD(month, -3, GETDATE())
ORDER BY t.TransactionDate DESC

Outstanding Balance Query

-- People with outstanding balances
SELECT TOP 500
    p.Name,
    SUM(t.amtdue) as TotalDue,
    COUNT(*) as OpenTransactions,
    MIN(t.TransactionDate) as OldestTransaction,
    DATEDIFF(day, MIN(t.TransactionDate), GETDATE()) as DaysOutstanding
FROM TransactionPeople tp
JOIN People p ON tp.PeopleId = p.PeopleId
JOIN [Transaction] t ON tp.Id = t.Id
WHERE t.amtdue > 0
  AND p.IsDeceased = 0 AND p.ArchivedFlag = 0
GROUP BY p.PeopleId, p.Name
HAVING SUM(t.amtdue) > 0
ORDER BY SUM(t.amtdue) DESC

🎉 Conclusion

This comprehensive guide covers TouchPoint's extensive database architecture, from basic queries to advanced analytics. Remember these key principles: