1

2

ds1

SELECT

--top 1000

    t.*,

    concat(year_num,'Y',week_num,'W') AS combined_value,

    CASE 

        WHEN cc_type = 'Tech CC' THEN '技术类'  

        WHEN cc_type = 'BO CC' THEN '配件类'

        ELSE '其他类'  

    END AS 类型,

    ISNULL(t1.area, '区域未维护') AS area

FROM 

    smil_dw..courtesy_cost_request_weeknum_currency t

LEFT JOIN 

    smil_dw..dim_aft_company_area_imp t1 

ON 

    t1.approval_company_code = t.approval_company_code

    AND t.buyoff_company_code = t1.buyoff_company_code

WHERE

1=1

${if(len(currency) == 0,"","and target_currency = '" + currency + "'")}  

${if(len(area) == 0,"","and ISNULL(t1.area, '区域未维护') in ('" + area + "')")} 

${if(len(status_adj) == 0,"","and status_adj in ('" + status_adj + "')")}  

${if(len(YearWeek) == 0,"","and concat(year_num,'Y',week_num,'W') in ('" + YearWeek + "')")}  

${if(len(country_code) == 0,"","and country_code in ('" + country_code + "')")}   

${if(len(customer_no) == 0,"","and customer_no in ('" + customer_no + "')")}  

${if(len(cc_type) == 0,"","and cc_type in ('" + cc_type + "')")} 

ORDER BY 

 DATEADD(week, week_num - 1, DATEFROMPARTS(year_num, 1, 1))

3柱状图

SELECT

---concat(year_num,'Y',week_num,'W'),

   CASE 

        WHEN cc_type = 'Tech CC' THEN '技术类'  

        WHEN cc_type = 'BO CC' THEN '配件类'

        ELSE '其他类'  

    END AS 类型,

   cc_type,

count(distinct cc_no)  as cc_no

FROM 

    smil_dw..courtesy_cost_request_weeknum_currency t

LEFT JOIN 

    smil_dw..dim_aft_company_area_imp t1 

ON 

    t1.approval_company_code = t.approval_company_code

    AND t.buyoff_company_code = t1.buyoff_company_code

WHERE

1=1

${if(len(currency) == 0,"","and target_currency = '" + currency + "'")}  

${if(len(area) == 0,"","and ISNULL(t1.area, '区域未维护') in ('" + area + "')")} 

${if(len(status_adj) == 0,"","and status_adj in ('" + status_adj + "')")}  

${if(len(YearWeek) == 0,"","and concat(year_num,'Y',week_num,'W') in ('" + YearWeek + "')")}  

${if(len(country_code) == 0,"","and country_code in ('" + country_code + "')")}   

${if(len(customer_no) == 0,"","and customer_no in ('" + customer_no + "')")}  

${if(len(cc_type) == 0,"","and cc_type in ('" + cc_type + "')")}

  group by 

 -- concat(year_num,'Y',week_num,'W'),

   CASE 

        WHEN cc_type = 'Tech CC' THEN '技术类'  

        WHEN cc_type = 'BO CC' THEN '配件类'

        ELSE '其他类'  

    END,

    cc_type

4、

5、