denorm claims update query

UPDATE denormalized_claim
SET
    claim_id = subquery.claim_id,
    fill_processed_at = subquery.fill_processed_at,
    fill_started_at = subquery.fill_started_at,
    end_service_at = subquery.end_service_at,
    authorization_number = subquery.authorization_number,
    prior_auth_code = subquery.prior_auth_code,
    network_type = subquery.network_type,
    ndc = subquery.ndc,
    strength = subquery.strength,
    days_supply = subquery.days_supply,
    quantity = subquery.quantity,
    claim_type = subquery.claim_type,
    cost_basis_term = subquery.cost_basis_term,
    billed_ingredient_cost = subquery.billed_ingredient_cost,
    billed_dispensing_fee = subquery.billed_dispensing_fee,
    billed_tax = subquery.billed_tax,
    billed_to_group = subquery.billed_to_group,
    total_billed_pre_copay = subquery.total_billed_pre_copay,
    awp_unit_price = subquery.awp_unit_price,
    mac_unit_price = subquery.mac_unit_price,
    copay = subquery.copay,
    new_refill_code = subquery.new_refill_code,
    deductible = subquery.deductible,
    out_of_pocket = subquery.out_of_pocket,
    out_of_network = subquery.out_of_network,
    compound_code = subquery.compound_code,
    dispense_as_written = subquery.dispense_as_written,
    dispense_as_written_diff = subquery.dispense_as_written_diff,
    who_paid_daw_diff = subquery.who_paid_daw_diff,
    member_id = subquery.member_id,
    self_pay = subquery.self_pay,
    prescription_id = subquery.prescription_id,
    rx_max_refills = subquery.rx_max_refills,
    rx_written_at = subquery.rx_written_at,
    dea_code = subquery.dea_code,
    rx_number = subquery.rx_number,
    reversed_auth_number = subquery.reversed_auth_number,
    route_of_administration = subquery.route_of_administration,
    claim_submitted_ingredient_cost = subquery.claim_submitted_ingredient_cost,
    claim_paid_ingredient_cost = subquery.claim_paid_ingredient_cost,
    claim_usual_cost = subquery.claim_usual_cost,
    claim_gross_amount_due = subquery.claim_gross_amount_due,
    claim_gross_copay = subquery.claim_gross_copay,
    claim_net_copay = subquery.claim_net_copay,
    reversed_processed_at = subquery.reversed_processed_at,
    reversed_started_at = subquery.reversed_started_at,
    doctor_name = subquery.doctor_name,
    doctor_first_name = subquery.doctor_first_name,
    doctor_last_name = subquery.doctor_last_name,
    doctor_address_1 = subquery.doctor_address_1,
    doctor_address_2 = subquery.doctor_address_2,
    doctor_city = subquery.doctor_city,
    doctor_state = subquery.doctor_state,
    doctor_zip = subquery.doctor_zip,
    doctor_npi = subquery.doctor_npi,
    drug_bgt = subquery.drug_bgt,
    drug_rx_otc_mddb = subquery.drug_rx_otc_mddb,
    drug_rx_otc_fdb = subquery.drug_rx_otc_fdb,
    mony = subquery.mony,
    gpi = subquery.gpi,
    label_name = subquery.label_name,
    label_name_mddb = subquery.label_name_mddb,
    dea_class_code = subquery.dea_class_code,
    hic3 = subquery.hic3,
    maintenance_mddb = subquery.maintenance_mddb,
    third_party_restriction = subquery.third_party_restriction,
    package_size_unit_of_measure = subquery.package_size_unit_of_measure,
    drug_gcn = subquery.drug_gcn,
    drug_desi = subquery.drug_desi,
    drug_dd_id = subquery.drug_dd_id,
    wac_unit_price = subquery.wac_unit_price,
    generic_name = subquery.generic_name,
    group_name = subquery.group_name,
    group_number = subquery.group_number,
    carrier_code = subquery.carrier_code,
    division_name = subquery.division_name,
    subgroup_name = subquery.subgroup_name,
    plan_code_name = subquery.plan_code_name,
    formulary_tier = subquery.formulary_tier,
    formulary_tier_desc = subquery.formulary_tier_desc,
    formulary_quantity_limit = subquery.formulary_quantity_limit,
    formulary_is_specialty = subquery.formulary_is_specialty,
    formulary_is_health_care_reform = subquery.formulary_is_health_care_reform,
    formulary_is_preferred_specialty = subquery.formulary_is_preferred_specialty,
    formulary_is_prior_auth = subquery.formulary_is_prior_auth,
    formulary_coverage_code = subquery.formulary_coverage_code,
    formulary_is_step_therapy = subquery.formulary_is_step_therapy,
    formulary_name = subquery.formulary_name,
    formulary_version_name = subquery.formulary_version_name,
    formulary_type = subquery.formulary_type,
    pharmacy_name = subquery.pharmacy_name,
    pharmacy_address_1 = subquery.pharmacy_address_1,
    pharmacy_address_2 = subquery.pharmacy_address_2,
    pharmacy_city = subquery.pharmacy_city,
    pharmacy_state = subquery.pharmacy_state,
    pharmacy_zip = subquery.pharmacy_zip,
    pharmacy_phone = subquery.pharmacy_phone,
    pharmacy_npi = subquery.pharmacy_npi,
    pharmacy_nabp = subquery.pharmacy_nabp,
    pharmacy_type_code = subquery.pharmacy_type_code,
    pharmacy_type = subquery.pharmacy_type,
    chain_code = subquery.chain_code,
    chain_name = subquery.chain_name,
    patient_person_code = subquery.patient_person_code,
    patient_laker_person_code = subquery.patient_laker_person_code,
    patient_coverage_start_date = subquery.patient_coverage_start_date,
    patient_coverage_end_date = subquery.patient_coverage_end_date,
    patient_relationship = subquery.patient_relationship,
    patient_first_name = subquery.patient_first_name,
    patient_last_name = subquery.patient_last_name,
    patient_middle_name = subquery.patient_middle_name,
    patient_suffix = subquery.patient_suffix,
    patient_address_1 = subquery.patient_address_1,
    patient_address_2 = subquery.patient_address_2,
    patient_city = subquery.patient_city,
    patient_state = subquery.patient_state,
    patient_zip = subquery.patient_zip,
    patient_gender = subquery.patient_gender,
    patient_date_of_birth = subquery.patient_date_of_birth,
    patient_ssn = subquery.patient_ssn,
    patient_age = subquery.patient_age,
    primary_first_name = subquery.primary_first_name,
    primary_middle_name = subquery.primary_middle_name,
    primary_last_name = subquery.primary_last_name,
    primary_suffix = subquery.primary_suffix,
    primary_gender = subquery.primary_gender,
    primary_date_of_birth = subquery.primary_date_of_birth,
    primary_ssn = subquery.primary_ssn,
    primary_address_1 = subquery.primary_address_1,
    primary_address_2 = subquery.primary_address_2,
    primary_city = subquery.primary_city,
    primary_state = subquery.primary_state,
    primary_zip = subquery.primary_zip,
    department_code = subquery.department_code,
    patient_pay_flag = subquery.patient_pay_flag,
    awp_total_price = subquery.awp_total_price,
    claim_paid_dispensing_fee = subquery.claim_paid_dispensing_fee,
    claim_paid_tax = subquery.claim_paid_tax,
    claim_other_coverage = subquery.claim_other_coverage,
    claim_member_number = subquery.claim_member_number,
    claim_person_number = subquery.claim_person_number
FROM (
    SELECT
        claims.id AS claim_id,
        claims.fill_processed_at,
        claims.fill_started_at,
        claims.end_service_at,
        claims.authorization_number,
        claims.prior_auth_code,
        claims.network_name AS network_type,
        claims.ndc,
        claims.strength,
        claims.days_supply,
        claims.quantity,
        claims.type AS claim_type,
        claims.cost_basis_term,
        claims.billed_ingredient_cost,
        claims.billed_dispensing_fee,
        claims.billed_tax,
        claims.total_billed AS billed_to_group,
        claims.billed_ingredient_cost + claims.billed_dispensing_fee + claims.billed_tax AS total_billed_pre_copay,
        claims.awp_unit_price,
        claims.mac_unit_price,
        claims.copay,
        claims.new_refill_code,
        claims.deductible,
        claims.out_of_pocket,
        claims.out_of_network,
        claims.compound_code,
        claims.dispense_as_written,
        claims.dispense_as_written_diff,
        claims.who_paid_daw_diff,
        claims.claimant_card_id AS member_id,
        claims.self_pay,
        claims.prescription_id,
        claims.rx_max_refills,
        claims.rx_written_at,
        claims.dea_code,
        claims.rx_number,
        claims.reversed_auth_number,
        claims.route_of_administration,
        claims.submitted_ingredient_cost AS claim_submitted_ingredient_cost,
        claims.paid_ingredient_cost AS claim_paid_ingredient_cost,
        claims.usual_cost AS claim_usual_cost,
        claims.gross_amount_due AS claim_gross_amount_due,
        claims.gross_copay AS claim_gross_copay,
        claims.net_copay AS claim_net_copay,
        reversed_claims.fill_processed_at AS reversed_processed_at,
        reversed_claims.fill_started_at AS reversed_started_at,
        doctors.name AS doctor_name,
        split_part(doctors.name::text, ', '::text, 2) AS doctor_first_name,
        split_part(doctors.name::text, ', '::text, 1) AS doctor_last_name,
        doctors.address_1 AS doctor_address_1,
        doctors.address_2 AS doctor_address_2,
        doctors.city AS doctor_city,
        doctors.state AS doctor_state,
        doctors.zip AS doctor_zip,
        doctors.npi AS doctor_npi,
        drug.bgt AS drug_bgt,
        drug.rx_otc_mddb AS drug_rx_otc_mddb,
        drug.rx_otc_fdb AS drug_rx_otc_fdb,
        drug.mony,
        drug.gpi,
        replace(drug.label_name_fdb::text, ','::text, ''::text) AS label_name,
        replace(drug.label_name_mddb::text, ','::text, ''::text) AS label_name_mddb,
        drug.dea_class_code,
        drug.hic3,
        drug.maintenance_mddb,
        drug.third_party_restriction,
        drug.package_size_unit_of_measure,
        drug.gcn AS drug_gcn,
        drug.desi AS drug_desi,
        drug.dd_id AS drug_dd_id,
        drug_price_by_type.price AS wac_unit_price,
        druggpi.tc_gpi_name AS generic_name,
        grp.name AS group_name,
        grp.code AS group_number,
        grp.carrier_code::enum_group_carrier_code,
        concat(department.code, '_', plan_code.received) AS division_name,
        concat(department.code, '_', plan_code.normalized) AS subgroup_name,
        plan_code.received AS plan_code_name,
        fd.tier AS formulary_tier,
        fd.tier_desc AS formulary_tier_desc,
        (fd.quantity_limit_count::text || '/'::text) || fd.quantity_limit_duration::text AS formulary_quantity_limit,
        fd.is_specialty AS formulary_is_specialty,
        fd.is_health_care_reform AS formulary_is_health_care_reform,
        fd.is_preferred_specialty AS formulary_is_preferred_specialty,
        fd.is_prior_auth AS formulary_is_prior_auth,
        fd.coverage_code AS formulary_coverage_code,
        fd.is_step_therapy AS formulary_is_step_therapy,
        f.name AS formulary_name,
        f.version_name AS formulary_version_name,
        f.type AS formulary_type,
        pharmacy.name AS pharmacy_name,
        pharmacy.address_1 AS pharmacy_address_1,
        pharmacy.address_2 AS pharmacy_address_2,
        pharmacy.city AS pharmacy_city,
        pharmacy.state AS pharmacy_state,
        pharmacy.zip AS pharmacy_zip,
        pharmacy.phone AS pharmacy_phone,
        pharmacy.npi AS pharmacy_npi,
        pharmacy.nabp AS pharmacy_nabp,
        pharmacy.type AS pharmacy_type_code,
        pharmacy_types.description AS pharmacy_type,
        chain.code AS chain_code,
        chain.name AS chain_name,
        patient_membership.person_code AS patient_person_code,
        claims.claimant_person_code AS patient_laker_person_code,
        patient_membership.coverage_start AS patient_coverage_start_date,
        patient_membership.coverage_end AS patient_coverage_end_date,
        patient_membership.relationship_to_subscriber AS patient_relationship,
        patient.first_name AS patient_first_name,
        patient.last_name AS patient_last_name,
        patient.middle_name AS patient_middle_name,
        patient.suffix AS patient_suffix,
        patient.address_1 AS patient_address_1,
        patient.address_2 AS patient_address_2,
        patient.city AS patient_city,
        patient.state AS patient_state,
        patient.zip AS patient_zip,
        patient.gender AS patient_gender,
        patient.date_of_birth AS patient_date_of_birth,
        patient.ssn AS patient_ssn,
        date_part('year'::text, age(claims.fill_processed_at::timestamp with time zone, patient.date_of_birth::timestamp with time zone)) AS patient_age,
        prim.first_name AS primary_first_name,
        prim.middle_name AS primary_middle_name,
        prim.last_name AS primary_last_name,
        prim.suffix AS primary_suffix,
        prim.gender AS primary_gender,
        prim.date_of_birth AS primary_date_of_birth,
        prim.ssn AS primary_ssn,
        prim.address_1 AS primary_address_1,
        prim.address_2 AS primary_address_2,
        prim.city AS primary_city,
        prim.state AS primary_state,
        prim.zip AS primary_zip,
        department.code AS department_code,
        CASE
            WHEN claims.total_billed = 0 THEN 'Y'::text
            ELSE 'N'::text
        END AS patient_pay_flag,
        claims.quantity * claims.awp_unit_price AS awp_total_price,
        claims.paid_dispensing_fee AS claim_paid_dispensing_fee,
        claims.paid_tax AS claim_paid_tax,
        claims.other_coverage AS claim_other_coverage,
        concat(claims.claimant_card_id, claims.claimant_person_code) AS claim_member_number,
        concat('0', claims.claimant_person_code) AS claim_person_number
    FROM claims
        LEFT JOIN pharmacy ON pharmacy.id = claims.pharmacy_id
        LEFT JOIN pharmacy_types ON pharmacy.type = pharmacy_types.id
        LEFT JOIN chain ON pharmacy.chain_id = chain.id
        LEFT JOIN doctors ON doctors.id = claims.doctor_id
        LEFT JOIN plan_design pd ON pd.code::text = claims.plan_design_id::text AND pd.validity @> claims.fill_processed_at::timestamp without time zone AND tsrange(pd.effective_date::timestamp without time zone, pd.end_date::timestamp without time zone, '[)'::text) @> claims.fill_started_at::timestamp without time zone
        LEFT JOIN formulary f ON f.uuid = pd.formulary_uuid AND f.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN drug ON drug.ndc::text = replace(claims.ndc::text, '-'::text, ''::text) AND drug.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN ( 
            SELECT 
                drug_gpi.tc_gpi_key,
                drug_gpi.tc_gpi_name
            FROM drug_gpi
            GROUP BY drug_gpi.tc_gpi_key, drug_gpi.tc_gpi_name
            ) druggpi 
            ON druggpi.tc_gpi_key::text = "left"(drug.gpi::text, 10)
        LEFT JOIN drug_price_by_type ON drug.ndc::text = drug_price_by_type.ndc::text AND drug_price_by_type.price_type = 'wac_unit_price_extended'::enum_drug_price_type AND drug_price_by_type.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN formulary_drug fd ON fd.ndc::text = claims.ndc::text AND fd.formulary_id = f.id AND fd.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN subscription patient_sub ON patient_sub.card_id::text = claims.claimant_card_id::text AND patient_sub.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN membership patient_membership ON patient_membership.laker_person_code::text = claims.claimant_person_code::text AND patient_membership.subscription_uuid = patient_sub.uuid AND patient_membership.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN person patient ON patient.uuid = patient_membership.person_uuid AND patient.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN subscription prim_sub ON prim_sub.card_id::text = claims.primary_card_id::text AND prim_sub.group_uuid = patient_sub.group_uuid AND prim_sub.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN membership prim_membership ON prim_membership.laker_person_code::text = claims.primary_person_code::text AND prim_membership.subscription_uuid = prim_sub.uuid AND prim_membership.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN person prim ON prim.uuid = prim_membership.person_uuid AND prim.validity @> claims.fill_processed_at::timestamp without time zone
        JOIN "group" grp ON grp.code::text = claims.partner_group_uid::text AND grp.uuid = patient_sub.group_uuid AND grp.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN available_coverage cov ON cov.uuid = patient_sub.available_coverage_uuid AND cov.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN department ON department.uuid = cov.department_uuid AND department.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN plan_code ON plan_code.uuid = cov.plan_code_uuid AND plan_code.validity @> claims.fill_processed_at::timestamp without time zone
        LEFT JOIN claims reversed_claims ON claims.reversed_auth_number::text = reversed_claims.authorization_number::text
    WHERE claims.fill_processed_at >= (CURRENT_DATE - '1 day'::interval day)
) AS subquery
WHERE denormalized_claim.claim_id = subquery.claim_id
;