An Export script for use when migrating from RockRMS to Planning Center Online's CSV import format. Use this with SQL Operations Studio to export the results directly to .csv.

RockRmsExportToPCO.sql

SELECT TOP (1000) 
      p.[Id] as PersonId
      ,COALESCE( [FirstName] , '' ) as FirstName
      ,COALESCE([NickName], '') AS NickName
      ,COALESCE([MiddleName], '') AS MiddleName
      ,COALESCE([LastName], '') AS LastName
      ,[BirthDate] as Birthdate     
      ,[AnniversaryDate] as Anniversary
      --,MedicalNotes
      ,g.id as HouseholdID
      --HouseholdPrimaryContact
      --Grade
      --SchoolType
      --SchoolName
      ,CAST(
             CASE 
                  WHEN [Gender] = 1
                     THEN 'M'
                  WHEN [Gender] = 2
                    THEN 'F'
                  ELSE
                    ''
             END AS varchar) as Gender
      --Child (bool)
      --Barcode (Not used)
      --Status (Active/Inactive)
      ,CAST(
          CASE
            WHEN [MaritalStatusValueId] = 143
              THEN 'Married'
            WHEN [MaritalStatusValueId] = 144
              THEN 'Single'
            ELSE
              ''
           End as varchar) as MaritalStatus
      --Membership (Unassigned, Member, Attender, Guest, Outreach, or custom - is case-sensitive)
      --NamePrefix (Mr, Mrs, Ms, Miss, Dr, Rev, or custom - is case-sensitive)
      --NameSuffix (Jr, Sr, Ph.D, I, III, or custom - is case-sensitive)
      --BackgroundCheckCleared (bool)
      --BackgroundCheckDateCompleted
      --BackgroundCheckExpiresOn
      --BackgroundCheckNote
      ,p.Email as HomeEmail
      ,'' as WorkEmail
      ,'' as OtherEmail
      ,pn.Number as HomePhoneNumber
      ,'' as WorkPhoneNumber
      ,'' as MobilePhoneNumber
      ,'' as PagerPhoneNumber
      ,'' as FaxPhoneNumber
      ,'' as SkypePhoneNumber
      ,'' as OtherPhoneNumber
      ,dbo.ufnCrm_GetAddress(p.Id, 'Home', 'Street1') as HomeAddressStreetLine1
      ,dbo.ufnCrm_GetAddress(p.Id, 'Home', 'Street2') as HomeAddressStreetLine2
      ,dbo.ufnCrm_GetAddress(p.Id, 'Home', 'City') as HomeAddressCity
      ,dbo.ufnCrm_GetAddress(p.Id, 'Home', 'State') as HomeAddressState
      ,dbo.ufnCrm_GetAddress(p.Id, 'Home', 'PostalCode') as HomeAddressPostalCode
      
      ,dbo.ufnCrm_GetAddress(p.Id, 'Work', 'Street2') as WorkAddressStreetLine2
      ,dbo.ufnCrm_GetAddress(p.Id, 'Work', 'Street1') as WorkAddressStreetLine1
      ,dbo.ufnCrm_GetAddress(p.Id, 'Work', 'City') as WorkAddressCity
      ,dbo.ufnCrm_GetAddress(p.Id, 'Work', 'State') as WorkAddressState
      ,dbo.ufnCrm_GetAddress(p.Id, 'Work', 'PostalCode') as WorkAddressPostalCode

    -- Rock only has a "Previous Address", which may not be what you want in "Other", so commented out
      --,dbo.ufnCrm_GetAddress(p.Id, "Previous", 'Street1') as OtherAddressStreetLine1
      --,dbo.ufnCrm_GetAddress(p.Id, "Previous", 'Street2') as OtherAddressStreetLine2
      --,dbo.ufnCrm_GetAddress(p.Id, "Previous", 'City') as OtherAddressCity
      --,dbo.ufnCrm_GetAddress(p.Id, "Previous", 'State') as OtherAddressState
      --,dbo.ufnCrm_GetAddress(p.Id, "Previous", 'PostalCode') as OtherAddressPostalCode      
  FROM [dbo].[Person] as p
  LEFT JOIN PhoneNumber as pn on pn.PersonId = p.Id
  LEFT JOIN GroupMember as gm on gm.PersonId = p.Id
  LEFT JOIN [Group] as g on g.Id = gm.GroupId
  WHERE g.GroupTypeId = 10
  order by HouseholdID