How to get registration responses in SQL?

Question

Originally posted by Unknown

Is there an easy way to get all of someone's responses to a registration regardless of whether it's a person field, person attribute, or registrant attribute?

Answer 1

Originally posted by Unknown

The following SQL will get you about 90% of the way there:

SELECT 
    FF.[FieldSource]
    ,CASE 
        WHEN A.Name IS NOT NULL THEN A.Name
        ELSE 
            CASE
                WHEN FF.PersonFieldType = 0 AND FF.FieldSource = 0 THEN 'First Name'
                WHEN FF.PersonFieldType = 1 AND FF.FieldSource = 0 THEN 'Last Name'
                WHEN FF.PersonFieldType = 2 AND FF.FieldSource = 0 THEN 'Campus'
                WHEN FF.PersonFieldType = 3 AND FF.FieldSource = 0 THEN 'Address'
                WHEN FF.PersonFieldType = 4 AND FF.FieldSource = 0 THEN 'Email'
                WHEN FF.PersonFieldType = 5 AND FF.FieldSource = 0 THEN 'Birth Date'
                WHEN FF.PersonFieldType = 6 AND FF.FieldSource = 0 THEN 'Gender'
                WHEN FF.PersonFieldType = 7 AND FF.FieldSource = 0 THEN 'Marital Status'
                WHEN FF.PersonFieldType = 8 AND FF.FieldSource = 0 THEN 'Mobile Phone'
                WHEN FF.PersonFieldType = 9 AND FF.FieldSource = 0 THEN 'Home Phone'
                WHEN FF.PersonFieldType = 10 AND FF.FieldSource = 0 THEN 'Work Phone'
                WHEN FF.PersonFieldType = 11 AND FF.FieldSource = 0 THEN 'Grade'
                WHEN FF.PersonFieldType = 12 AND FF.FieldSource = 0 THEN 'Connection Status'
                WHEN FF.PersonFieldType = 13 AND FF.FieldSource = 0 THEN 'Middle Name'
                WHEN FF.PersonFieldType = 14 AND FF.FieldSource = 0 THEN 'Anniversary Date'
                ELSE NULL
            END
    END AS [Name]
    ,CASE 
        WHEN V.Value IS NOT NULL THEN V.Value
        ELSE 
            CASE
                WHEN FF.PersonFieldType = 0 AND FF.FieldSource = 0 THEN P.FirstName
                WHEN FF.PersonFieldType = 1 AND FF.FieldSource = 0 THEN P.LastName
                WHEN FF.PersonFieldType = 2 AND FF.FieldSource = 0 THEN C.Name
                WHEN FF.PersonFieldType = 3 AND FF.FieldSource = 0 THEN CONCAT(L.Street1 + L.Street2,' ',L.City,', ',L.State,' ',L.PostalCode)
                WHEN FF.PersonFieldType = 4 AND FF.FieldSource = 0 THEN P.Email
                WHEN FF.PersonFieldType = 5 AND FF.FieldSource = 0 THEN TRY_CAST(P.BirthDate AS VARCHAR)
                WHEN FF.PersonFieldType = 6 AND FF.FieldSource = 0 THEN CASE WHEN P.Gender = 0 THEN 'Unknown' WHEN P.Gender = 1 THEN 'Male' WHEN P.Gender = 2 THEN 'Female' END
                WHEN FF.PersonFieldType = 7 AND FF.FieldSource = 0 THEN MS.Value
                WHEN FF.PersonFieldType = 8 AND FF.FieldSource = 0 THEN MPN.NumberFormatted
                WHEN FF.PersonFieldType = 9 AND FF.FieldSource = 0 THEN HPN.NumberFormatted
                WHEN FF.PersonFieldType = 10 AND FF.FieldSource = 0 THEN WPN.NumberFormatted
                WHEN FF.PersonFieldType = 11 AND FF.FieldSource = 0 THEN G.[Description]
                WHEN FF.PersonFieldType = 12 AND FF.FieldSource = 0 THEN CS.Value
                WHEN FF.PersonFieldType = 13 AND FF.FieldSource = 0 THEN P.MiddleName
                WHEN FF.PersonFieldType = 14 AND FF.FieldSource = 0 THEN TRY_CAST(P.AnniversaryDate AS VARCHAR)
                ELSE NULL
            END
    END AS [Value]
    ,A.[Key] AS [AttributeKey]
    ,A.FieldTypeId AS [AttributeFieldType]
FROM [RegistrationRegistrant] RR
    INNER JOIN [PersonAlias] PA ON PA.[Id] = RR.[PersonAliasId]
    INNER JOIN [Person] P ON P.[Id] = PA.[PersonId]
    LEFT OUTER JOIN [Campus] C ON C.Id = P.PrimaryCampusId
    LEFT OUTER JOIN [Group] F ON F.Id = P.PrimaryFamilyId
    OUTER APPLY ( 
                    SELECT TOP 1 L.*
                    FROM [Location] L
                        INNER JOIN GroupLocation GL ON GL.LocationId = L.Id
                    WHERE GL.GroupId = F.Id
                        AND GL.GroupLocationTypeValueId = 19
                    ORDER BY GL.CreatedDateTime DESC
                ) L 
    LEFT OUTER JOIN [DefinedValue] MS ON MS.Id = P.MaritalStatusValueId AND MS.DefinedTypeId = 7
    LEFT OUTER JOIN [PhoneNumber] MPN ON MPN.PersonId = P.Id AND MPN.NumberTypeValueId = 12 --Mobile
    LEFT OUTER JOIN [PhoneNumber] HPN ON HPN.PersonId = P.Id AND HPN.NumberTypeValueId = 13 --Home
    LEFT OUTER JOIN [PhoneNumber] WPN ON WPN.PersonId = P.Id AND WPN.NumberTypeValueId = 136 --Work
    LEFT OUTER JOIN [DefinedValue] CS ON CS.Id = P.ConnectionStatusValueId
    LEFT OUTER JOIN [DefinedValue] G ON G.Value = [dbo].ufnCrm_GetGradeOffset(P.GraduationYear,CAST(CONCAT((SELECT [Value] FROM AttributeValue WHERE AttributeId = 498), '/', YEAR(GETDATE())) AS DATETIME)) AND G.DefinedTypeId = 51
    INNER JOIN [Registration] R ON R.[Id] = RR.[RegistrationId]
    INNER JOIN [RegistrationInstance] I ON I.[Id] = R.[RegistrationInstanceId]
    INNER JOIN [RegistrationTemplateForm] TF ON TF.[RegistrationTemplateId] = I.[RegistrationTemplateId]
    INNER JOIN [RegistrationTemplateFormField] FF ON FF.[RegistrationTemplateFormId] = TF.[Id]
    LEFT OUTER JOIN [Attribute] A ON A.[Id] = FF.[AttributeId]
    LEFT OUTER JOIN [AttributeValue] V 
        ON V.[AttributeId] = A.[Id] 
        AND V.[EntityId] = CASE WHEN FF.[FieldSource] = 1 THEN PA.[PersonId] ELSE RR.[Id] END
        AND V.[Value] IS NOT NULL 
        AND V.[Value] <> ''
WHERE RR.[Id] = 68706
ORDER BY TF.[Order], FF.[Order]

this produces output similar to the following:

Obviously for some things you'll need some additional joins to get the information you are looking for (defined values for example)