Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.