SEC Data Collection

SELECT  B.person_id,
         B.family_id,
         B.family_role,
               B.first_name,
               B.last_name,
               B.Age,
               B.[status],
               sc.Is_Reg_Member, --vs associate_member
               [Regular Membership Date] = md.datetime_value,
               --B.email,
               spouse_id = s.person_id,
               spouse = s.first_last,
               spouse_status = s.[status],
               CN.child_names,
               CN.child_ages,
               CN.child_statuses,
               [Service Area Tags] = ST.profile_list,
               [Board/Committee/Leadership Service Tag] = L.profile_list,
               [Background Check Service Tag] = BC.profile_list,
               [STARS related Tags] = STARS.profile_list,
               [Measurable Involvment In Last Year] = CASE WHEN I.[Involvment In Last Year] = 1 THEN 'Yes' ELSE 'No' END,
               [Measurable Involvment In Last 2 Years] = CASE WHEN I.[Involvment In Last 2 Years] = 1 THEN 'Yes' ELSE 'No' END,
               I.last_attendance,
               I.Last_52_Weeks_Count,
               I.Last_52_Weeks_Family_Count,
               [In a Small Group] = CASE WHEN I.SmallGroup = 1 THEN 'Yes' ELSE 'No' END
FROM           cust_CCIW_v_person_basic B
               LEFT JOIN cust_CCIW_v_member_status_category sc ON B.member_status_id = sc.lookup_id
               LEFT JOIN core_person_attribute md ON md.person_id = B.person_id and md.attribute_id = 126
               LEFT JOIN cust_CCIW_v_person_basic s on s.person_id = B.spouse_id
               LEFT JOIN dbo.cust_CCIW_funct_get_child_info(1) CN on CN.person_id = B.person_id
               --id list here is a list of exclude ids that are confidential, volunters who are not approved to serve in some capacity
               LEFT JOIN dbo.cust_CCIW_funct_get_profile_list(2,'134,893,898,3044,3203,3613,3880,4062,4533,4649',-1,'1651,1741') ST on B.person_id = ST.person_id
               LEFT JOIN dbo.cust_CCIW_funct_get_profile_list(2,'-1',1651,'') L on B.person_id = L.person_id
               LEFT JOIN dbo.cust_CCIW_funct_get_profile_list(2,'-1',1741,'') BC on B.person_id = BC.person_id
               LEFT JOIN dbo.cust_CCIW_funct_get_profile_list(1,'-1',1639,'') STARS on B.person_id = STARS.person_id
               LEFT JOIN dbo.cust_CCIW_v_involvment I on I.person_id = B.person_id