崔家豪(提问者)with monthrate as
(select fyear,
fmonth,
factorycode ffactorycode,
workshopcode fworkshopcode,
fyear||\'-\'||fmonth dateMonth,
to_char(ADD_MONTHS(to_date(fyear||\'-\'||fmonth,\'yyyy-fmmm\'), -1),\'yyyy-fmmm\') preDateMonth,
nvl(round(sum(workHours)/sum(workCount),2),0) frate
from mg_conversionHours_day
group by fyear, fmonth,factorycode,workshopcode
),dayRate as (
select fyear,
fmonth,
fday,
factorycode ffactorycode,
workshopcode fworkshopcode,
fyear||\'-\'||fmonth dateMonth,
to_char(ADD_MONTHS(to_date(fyear||\'-\'||fmonth,\'yyyy-fmmm\'), -1),\'yyyy-fmmm\') preDateMonth,
TO_DATE(FYEAR || \'-\' || FMONTH || \'-\' || FDAY ,\'YYYY-MM-DD\') rq,
conversionHours frate
from mg_conversionHours_day
)
select dr.rq,dr.fday,dr.ffactorycode,dr.fworkshopcode,dr.frate*60 as 转模转色时间,
(select frate*60 from monthrate t where t.dateMonth=mr.preDateMonth and t.ffactorycode=mr.ffactorycode and t.fworkshopcode=mr.fworkshopcode) as 基准值,
/*(select fachv_rate from mg_achv_rate_target@dsx5mes mart where mart.fyear=mr.fyear and mart.fmonth=mr.fmonth and mart.ffactorycode=mr.ffactorycode and mart.fworkshopcode=mr.fworkshopcode)*/ 0 as 目标值
from dayRate dr
left join monthrate mr on dr.ffactorycode=mr.ffactorycode and dr.fworkshopcode=mr.fworkshopcode
and dr.fyear=mr.fyear and dr.fmonth=mr.fmonth
where 1=1
${if(len(year_c)==0,\" and dr.fyear = \'\"+year+\"\'\",\" and dr.fyear = \'\"+year_c+\"\'\")}
${if(len(month_c)==0,\" and dr.fmonth = \'\"+month+\"\'\",\" and dr.fmonth = \'\"+month_c+\"\'\")}
and dr.ffactorycode = \'B\'
order by dr.rq
1min目标场景问卷
立即参与
