Creating c360_combined_ell_reporting_us View

Requirement:

 

Develop a SQL Script to generate c360_combined_ell_reporting_us view based on the below logic

 

Join two tables — sales_team_brand_interactions_reporting_us and ell_field_force_brand_mapping — to generate a consolidated dataset for brand-level reporting. The query should filter only the records where interaction_channel = 'ALL'.

 

Calculated fields:

 

* source: constant value 'FLD'

* field_force_name: derived from the mapping table (config.field_force_name)

* brand_name: if fld.br_field_force_name is in ('RED BASE', 'RED CARDIO', 'RED OVER'), set it as 'REPATHA'; otherwise, use fld.product_name

* market_name: empty string ''

* hcp_count_targets_at_frequency: if brand_name is in ('AMG757', 'IMDELLTRA'), use the existing hcp_count_targets_at_frequency value; otherwise, NULL

* percent_hcp_targets_at_frequency: calculated as ((hcp_count_targets_above_frequency / total_tier_universe_poa_eligible) + (hcp_count_targets_at_frequency / total_tier_universe_poa_eligible))

 

As-it-is fields:

target_category,

interaction_channel,

rte_ff_total_reps,

no_of_territories

 

Join condition:

Join sales_team_brand_interactions_reporting_us (alias fld) with ell_field_force_brand_mapping (alias config)

on br_field_force_name = field_force_name and product_name = brand_name