SQL Server查询优化日期计算

Question

我正在尝试找到一种更有效的方式来重写我的查询。

有什么建议吗？我目前也在考虑将会计月份名称显示为数字本身，然后比较并返回月份名称。

(case                                   --proj_end_fis_mon
    when (MYMW.mfg_year_no = FYQM.fiscal_year_no and MYMW.mfg_month_no = 12)
    then                    
        (convert(varchar,(MYMW.mfg_year_no-1))+'-'+(case
            when FM.fiscal_month_name like 'Jan%' then '01'
            when FM.fiscal_month_name like 'Feb%' then '02'
            when FM.fiscal_month_name like 'Mar%' then '03'
            when FM.fiscal_month_name like 'Apr%' then '04'
            when FM.fiscal_month_name like 'May' then '05'
            when FM.fiscal_month_name like 'June' then '06'
            when FM.fiscal_month_name like 'July' then '07'
            when FM.fiscal_month_name like 'Aug%' then '08'
            when FM.fiscal_month_name like 'Sep%' then '09'
            when FM.fiscal_month_name like 'Oct%' then '10'
            when FM.fiscal_month_name like 'Nov%' then '11'
            when FM.fiscal_month_name like 'Dec%' then '12'
            end))
    else
    (convert(varchar,MYMW.mfg_year_no)+'-'+(case
    when FM.fiscal_month_name like 'Jan%' then '01'
    when FM.fiscal_month_name like 'Feb%' then '02'
    when FM.fiscal_month_name like 'Mar%' then '03'
    when FM.fiscal_month_name like 'Apr%' then '04'
    when FM.fiscal_month_name like 'May' then '05'
    when FM.fiscal_month_name like 'June' then '06'
    when FM.fiscal_month_name like 'July' then '07'
    when FM.fiscal_month_name like 'Aug%' then '08'
    when FM.fiscal_month_name like 'Sep%' then '09'
    when FM.fiscal_month_name like 'Oct%' then '10'
    when FM.fiscal_month_name like 'Nov%' then '11'
    when FM.fiscal_month_name like 'Dec%' then '12'
    end))


    end)

下面是我的表的样子：

​

​

Answer 1

假设您有可用的会计月数：

Convert( VarChar(4), MYMW.mfg_year_no -
    case when MYMW.mfg_year_no = FYQM.fiscal_year_no and MYMW.mfg_month_no = 12 then 1
      else 0 end ) +
    '-' + Right('0' + Convert( VarChar(2), FM.fiscal_month_no ), 2 )