Untitled

apoorv_me

Apr 3rd, 2025 (edited)

412

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

MySQL 3.16 KB | None | 0 0

raw download clone embed print report

WITH max_date_cte AS (
SELECT MAX(year_month) AS max_date_month FROM {val_fact_weekly}
),
val_data_cte AS (
SELECT
claim_id, patient_sk, plan_sk, product_sk, physician_sk, fill_date,
mastered_claim_status_code AS claim_status_code, new_to_product_flag AS val_nbrx,
YEAR(fill_date) AS mpd_year, pharmacy_distribution_desc AS channel,
source_initial_final_flag AS claim_status, source_flag, source_market, market_code
FROM {val_fact_weekly}
WHERE source_market IN ('MIGRAINE', 'PSYCH')
AND source_flag = '{source_flag}'
AND UPPER(market_code) LIKE ANY ('ACUTE', 'MIGRAINE', 'PREVENTIVE', 'PSYCH')
AND UPPER(market_code) NOT LIKE '%CON%'
AND fill_date >= (
SELECT DISTINCT ADD_MONTHS(TO_DATE(CONCAT(max_date_month,'01'), 'yyyyMMdd'), -16)
FROM max_date_cte
)
),
val_product_cte AS (
SELECT DISTINCT
product_sk, source_product_ndc_code AS ndc11, mdm_product_id AS child_product_id,
source_product_name AS product_name, abbvie_product_id
FROM {val_dim_prd_weekly}
WHERE source_market IN ('MIGRAINE', 'PSYCH')
AND source_flag = '{source_flag}'
AND source_product_name IN ('VRAYLAR', 'QULIPTA', 'UBRELVY')
),
val_product_data_cte AS (
SELECT * FROM val_data_cte
INNER JOIN val_product_cte USING (product_sk)
WHERE child_product_id IS NOT NULL
),
val_plan_cte AS (
SELECT DISTINCT
plan_sk, source_plan_id AS primary_plan_id, source_payer_name AS primary_payor_name,
abbvie_plan_name, abbvie_plan_id, abbvie_plan_channel, pfv_payer_name,
pfv_payer_channel, source_plan_channel, source_plan_name, pfv_plan_id
FROM {val_dim_plan_weekly}
WHERE source_market IN ('MIGRAINE', 'PSYCH')
AND source_flag = '{source_flag}'
),
val_plan_data_cte AS (
SELECT * FROM val_product_data_cte
LEFT JOIN val_plan_cte USING (plan_sk)
),
plan_bridge_cte AS (
SELECT DISTINCT PAYER_PLAN_IDENTIFIER AS payer_plan_id, payer_entity_identifier AS pfv_plan_id
FROM pfv_ma360_plan_formulary.pfv_hierarchy_ims_bridge_tbl
),
val_plan_data_final_cte AS (
SELECT * FROM val_plan_data_cte
LEFT JOIN plan_bridge_cte USING (pfv_plan_id)
),
acc_map_cte AS (
SELECT
account_id_level1 AS abbvie_plan_id, channel_sk, account_name_level1,
account_name_level2, account_name_level3, account_name_level4
FROM {acc_map_tbl}
WHERE account_type_level = 'PLAN'
),
val_acc_map_join_cte AS (
SELECT * FROM val_plan_data_final_cte
INNER JOIN acc_map_cte USING (abbvie_plan_id)
),
data_with_mdm_cte AS (
SELECT DISTINCT child_product_id, product_group_id, market_id, product_group_name
FROM {mdm_product_hierarchy_wide_tbl}
WHERE market_id IN ('MKT02416165661','MKT33405150037', 'MKT33314581420')
AND market_name LIKE '%MA_ARC'
AND product_group_id != market_id
AND hierarchy_level IN ('PRD', 'PKGSZ')
AND dds_active_flag = 'Y'
AND future_current_flag = 'C'
)
SELECT *
FROM val_acc_map_join_cte
INNER JOIN data_with_mdm_cte USING (child_product_id);

Add Comment

Please, Sign In to add comment