select time_year,
	  konggu,
	  itemcode,
	  sum(hk_ytd_act)/1000000 as ytdact,
	  sum(hk_ytd_lat)/1000000 as ytdlat,
	  decode(sum(hk_ytd_lat),0,null,(sum(hk_ytd_act)-sum(hk_ytd_lat))/sum(hk_ytd_lat)) as uprate, --增减率
	  sum(ch_all_bud)/1000000 as ytdbud,
	  decode(sum(ch_all_bud),0,null,sum(hk_ytd_act)/sum(ch_all_bud)) as budcom
from
(
select time_year,
	  konggu,
	  time_month,
	  case itemcode
	  when 'C3' then 'ELSE'
	  when 'DER4' then 'ELSE'
	  when 'H3' then 'ELSE'
	  when 'LB4' then 'ELSE'
	  when 'ENG3' then 'ELSE'
	  else itemcode
	  end as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
union all
select time_year,
	  case konggu
	  when 'HCIL' then 'MU'
	  else 'MU'
	  end as konggu,
	  time_month,
	  case itemcode
	  when 'C3' then 'ELSE'
	  when 'DER4' then 'ELSE'
	  when 'H3' then 'ELSE'
	  when 'LB4' then 'ELSE'
	  when 'ENG3' then 'ELSE'
	  else itemcode
	  end as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
)
where 1=1
and time_year >= ${year} - 5
and time_year < ${year}
and time_month = 12
${if(len(konggu)==0,"","and konggu = '"+ konggu +"'")}
${if(len(itemcodeb) ==0,"","and itemcode = '" + itemcodeb + "'")}
group by time_year,konggu,itemcode
union all
select time_year,
	  konggu,
	  itemcode,
	  sum(hk_ytd_act)/1000000 as ytdact,
	  sum(hk_ytd_lat)/1000000 as ytdlat,
	   decode(sum(hk_ytd_lat),0,null,(sum(hk_ytd_act)-sum(hk_ytd_lat))/sum(hk_ytd_lat)) as uprate, --增减率
	  sum(ch_all_bud)/1000000 as ytdbud,
	  decode(sum(ch_all_bud),0,null,sum(hk_ytd_act)/sum(ch_all_bud)) as budcom
from 
(
select time_year,
	  konggu,
	  time_month,
	  'ALL' as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
union all
select time_year,
	  case konggu
	  when 'HCIL' then 'MU'
	  else 'MU'
	  end as konggu,
	  time_month,
	  'ALL' as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
)
where 1=1
and time_year >= ${year} - 5
and time_year < ${year}
and time_month = 12
${if(len(konggu)==0,"","and konggu = '"+ konggu +"'")}
${if(len(itemcodeb) == 0,"","and itemcode = '" + itemcodeb + "'")}
group by time_year,konggu,itemcode
union all
select time_year,
	  konggu,
	  itemcode,
	  sum(hk_ytd_act)/1000000 as ytdact,
	  sum(hk_ytd_lat)/1000000 as ytdlat,
	  decode(sum(hk_ytd_lat),0,null,(sum(hk_ytd_act)-sum(hk_ytd_lat))/sum(hk_ytd_lat)) as uprate, --增减率
	  sum(ch_all_bud)/1000000 as ytdbud,
	  decode(sum(ch_all_bud),0,null,sum(hk_ytd_act)/sum(ch_all_bud)) as budcom
from
(
select time_year,
	  konggu,
	  time_month,
	  case itemcode
	  when 'C3' then 'ELSE'
	  when 'DER4' then 'ELSE'
	  when 'H3' then 'ELSE'
	  when 'LB4' then 'ELSE'
	  when 'ENG3' then 'ELSE'
	  else itemcode
	  end as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
union all
select time_year,
	  case konggu
	  when 'HCIL' then 'MU'
	  else 'MU'
	  end as konggu,
	  time_month,
	  case itemcode
	  when 'C3' then 'ELSE'
	  when 'DER4' then 'ELSE'
	  when 'H3' then 'ELSE'
	  when 'LB4' then 'ELSE'
	  when 'ENG3' then 'ELSE'
	  else itemcode
	  end as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
)
where 1=1
and time_year = ${year}
and time_month = ${month}
${if(len(konggu)==0,"","and konggu = '"+ konggu +"'")}
${if(len(itemcodeb) == 0,"","and itemcode = '" + itemcodeb + "'")}
group by time_year,konggu,itemcode
union all
select time_year,
	  konggu,
	  itemcode,
	  sum(hk_ytd_act)/1000000 as ytdact,
	  sum(hk_ytd_lat)/1000000 as ytdlat,
	   decode(sum(hk_ytd_lat),0,null,(sum(hk_ytd_act)-sum(hk_ytd_lat))/sum(hk_ytd_lat)) as uprate, --增减率
	  sum(ch_all_bud)/1000000 as ytdbud,
	  decode(sum(ch_all_bud),0,null,sum(hk_ytd_act)/sum(ch_all_bud)) as budcom
from 
(
select time_year,
	  konggu,
	  time_month,
	  'ALL' as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
union all
select time_year,
	  case konggu
	  when 'HCIL' then 'MU'
	  else 'MU'
	  end as konggu,
	  time_month,
	  'ALL' as itemcode,
	  hk_ytd_act,
	  hk_ytd_lat,
	  ch_all_bud
from GHBIDW.DM_TMS_FACT_FI_SH_PL_JTI
where itemcode in ('A4','B3','C3','DER4','H3','I3','LB4','ENG3')
)
where 1=1
and time_year = ${year}
and time_month = ${month}
${if(len(konggu)==0,"","and konggu = '"+ konggu +"'")}
${if(len(itemcodeb) == 0,"","and itemcode = '" + itemcodeb + "'")}
group by time_year,konggu,itemcode
order by time_year,konggu