-- 
-- 中分類別年月別売上予実績リスト
-- 
CREATE OR REPLACE VIEW v_sales_budget_and_actual_results_by_lv2_ym
AS 
SELECT 
    v.category_lv2_id,  -- 中分類ID
    c.category_name AS category_lv2_name,   -- 中分類名
    c.sort_order AS category_lv2_sort_order,-- 中分類表示順
    v.target_year,  -- 対象年
    v.target_month, -- 対象年月

    -- 売上目標
    SUM(v.sales_budget_amount) AS sales_budget_amount,  
    -- 総売上額
    SUM(v.sales_amount) AS sales_amount,
    -- 返品額
    SUM((v.return_amount * -1)) AS return_amount,
    -- 通常値引額
    SUM(v.normal_discount_amount) AS normal_discount_amount,
    -- 政策値引額
    SUM(v.policy_discount_amount) AS policy_discount_amount,
    -- 値引合計
    SUM(v.discount_amount) AS discount_amount,
    -- 純売上額
    SUM((v.sales_amount + v.return_amount - v.discount_amount)) AS amount,
    -- 売上達成率
    SUM(CASE WHEN v.sales_budget_amount = 0 THEN 0.0 ELSE
        ROUND(((v.sales_amount + v.return_amount - v.discount_amount) / v.sales_budget_amount * 100), 1) END) 
        AS achievement_rate,    
    -- 粗利目標
    SUM(gross_profit_budget_amount) AS gross_profit_budget_amount, 
    -- 粗利額
    SUM(v.sales_amount + v.return_amount - v.discount_amount - v.cost_amount) AS gross_profit_amount,
    -- 粗利達成率
    SUM(CASE WHEN v.gross_profit_budget_amount = 0 THEN 0.0 ELSE
        ROUND((((v.sales_amount + v.return_amount - v.discount_amount - v.cost_amount) / v.gross_profit_budget_amount) * 100), 1) END)
        AS gross_profit_achievement_rate, 
    -- 粗利率
    SUM(CASE WHEN (v.sales_amount + return_amount - v.discount_amount) = 0 THEN 0.0 ELSE
        ROUND(((v.sales_amount + return_amount - v.discount_amount - v.cost_amount) / (v.sales_amount + return_amount - v.discount_amount) * 100), 1) END) 
        AS gross_profit_margin 

-- ｜担当者別中分類別年月別売上予実績リスト
FROM v_sales_budget_and_actual_results_by_rep_lv2_ym v

-- ｜商品分類マスタ（中分類）
LEFT JOIN categories c ON v.category_lv2_id = c.id

GROUP BY
    v.category_lv2_id,
    c.category_name,
    c.sort_order,
    v.target_year,
    v.target_month