Code

ComponentSchema

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.prod_rollup_code

{% set rollup_cols_in_abridged = [
    'bill_type', 
    'billing_code_type', 
    'billing_code', 
    'billing_code_full',
    'service_description',
    'service_line',
    'therapeutic_area',
    'drug_name_array',
    'is_drug_code',
    'is_surg_code'
] %}

CREATE OR REPLACE TABLE {{ schema_name }}.prod_rollup_code 
AS
WITH 
df AS (
    SELECT 
        p.bill_type,
        p.billing_code_type,
        p.billing_code,
        p.billing_code_full,
        ANY_VALUE(p.service_description) as service_description,
        ANY_VALUE(p.service_line) as service_line,
        ANY_VALUE(p.therapeutic_area) as therapeutic_area,
        ANY_VALUE(p.drug_name_array) as drug_name_array,
        ANY_VALUE(p.is_drug_code) as is_drug_code,
        ANY_VALUE(p.is_surg_code) as is_surg_code,
        {% for col in cld_params.views.CODE_DETAIL_COLUMNS %}
            {% if col not in rollup_cols_in_abridged and col not in cld_params.views.ROLLUP_COLUMNS %}
                ANY_VALUE(p.{{ col }}) as {{ col }},
            {% endif %}
        {% endfor %}
        {{ cld_params.views.ROLLUP_STATS }}
    FROM {{ schema_name }}.prod_combined_all p
    WHERE p.billing_code_full IS NOT NULL
    GROUP BY 1, 2, 3, 4
)
SELECT 
    {% for col in cld_params.views.CODE_DETAIL_COLUMNS %}
    {{ col }}{% if not loop.last %}, {% endif %}
    {% endfor %}
FROM df