claim_member_view

Provides all the fields about a member on a claim. Note that if the member ID is not unique, there will be both a member (subscriber) and a patient. If the member ID is unique, the member and patient columns will be identical

Materialized: false

Definition

 SELECT member.claim_member_id,
    member.payer_responsibility_sequence,
    member.relationship_to_subscriber,
    member.group_or_policy_number,
    member.group_name,
    member.insurance_type,
    member.claim_filing_indicator,
    member.member_subscriber_or_patient_id,
    member.member_ssn,
    member.member_identification_code,
    member.individual_health_identifier,
    member.insurer_payer_id,
    member.patient_subscriber_or_patient_id,
    member.member_canonical_id_chosen,
    member.member_canonical_id_chosen_at,
    member.member_canonical_id_chosen_by,
    member.member_match_status,
    m.address_id AS member_address_id,
    m.date_of_birth AS member_date_of_birth,
    m.name_prefix AS member_name_prefix,
    m.name_first AS member_name_first,
    m.name_middle AS member_name_middle,
    m.name_last AS member_name_last,
    m.name_suffix AS member_name_suffix,
    m.gender_or_sex AS member_gender_or_sex,
    ma.address1 AS member_address1,
    ma.address2 AS member_address2,
    ma.city AS member_city,
    ma.state_or_province AS member_state_or_province,
    ma.postal_code AS member_postal_code,
    mph.phone_number AS member_phone_number,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN patient.name_prefix
            ELSE m.name_prefix
        END AS patient_name_prefix,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN patient.name_first
            ELSE m.name_first
        END AS patient_name_first,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN patient.name_middle
            ELSE m.name_middle
        END AS patient_name_middle,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN patient.name_last
            ELSE m.name_last
        END AS patient_name_last,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN patient.name_suffix
            ELSE m.name_suffix
        END AS patient_name_suffix,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN patient.gender_or_sex
            ELSE m.gender_or_sex
        END AS patient_gender_or_sex,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN pa.address1
            ELSE ma.address1
        END AS patient_address1,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN pa.address2
            ELSE ma.address2
        END AS patient_address2,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN pa.city
            ELSE ma.city
        END AS patient_city,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN pa.state_or_province
            ELSE ma.state_or_province
        END AS patient_state_or_province,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN pa.postal_code
            ELSE ma.postal_code
        END AS patient_postal_code,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN patient.date_of_birth
            ELSE m.date_of_birth
        END AS patient_date_of_birth,
        CASE
            WHEN member.patient_subscriber_or_patient_id IS NOT NULL THEN pph.phone_number
            ELSE mph.phone_number
        END AS patient_phone_number
   FROM claims.claim_member member
     JOIN claims.subscriber_or_patient m ON m.subscriber_or_patient_id = member.member_subscriber_or_patient_id
     LEFT JOIN claims.address_seen ma ON ma.address_id = m.address_id
     LEFT JOIN claims.phone mph ON mph.phone_id = m.phone_number_id
     LEFT JOIN claims.subscriber_or_patient patient ON patient.subscriber_or_patient_id = member.patient_subscriber_or_patient_id
     LEFT JOIN claims.address_seen pa ON pa.address_id = patient.address_id
     LEFT JOIN claims.phone pph ON pph.phone_id = patient.phone_number_id;

Column	Data Type	Indexed
claim_member_id	bigint	No
payer_responsibility_sequence	payer_responsibility_sequence_type	No
relationship_to_subscriber	individual_relationship_type	No
group_or_policy_number	text	No
group_name	text	No
insurance_type	insurance_type	No
claim_filing_indicator	claim_filing_indicator_type	No
member_subscriber_or_patient_id	bigint	No
member_ssn	text	No
member_identification_code	text	No
individual_health_identifier	text	No
insurer_payer_id	bigint	No
patient_subscriber_or_patient_id	bigint	No
member_canonical_id_chosen	bigint	No
member_canonical_id_chosen_at	date	No
member_canonical_id_chosen_by	bigint	No
member_match_status	match_status_type	No
member_address_id	bigint	No
member_date_of_birth	date	No
member_name_prefix	text	No
member_name_first	text	No
member_name_middle	text	No
member_name_last	text	No
member_name_suffix	text	No
member_gender_or_sex	gender_or_sex_type	No
member_address1	text	No
member_address2	text	No
member_city	text	No
member_state_or_province	text	No
member_postal_code	text	No
member_phone_number	text	No
patient_name_prefix	text	No
patient_name_first	text	No
patient_name_middle	text	No
patient_name_last	text	No
patient_name_suffix	text	No
patient_gender_or_sex	gender_or_sex_type	No
patient_address1	text	No
patient_address2	text	No
patient_city	text	No
patient_state_or_province	text	No
patient_postal_code	text	No
patient_date_of_birth	date	No
patient_phone_number	text	No