SQL for Metric Categorization and Sales Forecast Summarization

Develop a SQL script to generate aggregated sales metrics from the s_bai_sales_le_plan_all_brands table. The script should first filter records where the week_calendar is within the past 36 months from the cdl_effective_date, excluding any entries where the market_product contains the word “market”.

 

From this filtered dataset, only the most recent scenario per le_plan_flag should be retained. Then, for each combination of market_product, basket, week_calendar, le_plan_flag, metric, indication, and specialty, the script should calculate the total value using the forecast_values when available or fallback to actuals_values.

 

In the final output:

 

* market_product and basket should be trimmed and converted to uppercase.

* week_calendar should be standardized to the first 10 characters (YYYY-MM-DD).

* le_plan_flag should be trimmed and converted to lowercase.

* The metric field should be renamed based on defined logic:

** If metric is "n units", label it as "NORMALIZED UNITS".

** If metric is "trx dot" or "trxdot", label it as "DOT".

** If metric is "867 units" or "852 units", and conditions based on market_product and le_plan_flag are met, label it as "UNITS".

** For specific combinations of metric and market_product, label as "SKU PFP" or "SKU PFS".

** Otherwise, convert the metric value to uppercase as is.

* The indication and specialty fields should be cleaned and converted to uppercase.

* The script should return one row per combination of the selected fields with the aggregated values.