我正在运行一个查询,以返回每周完成的工作数量的计数,并且根据单独的财务周期表将周细分为4个每周的周期,但是查询未返回正确的计数。
这是代码示例:
select (SELECT (fp.financialperiod || ' week ' ||
ceil(floor((wo.actfinish - p.periodstart+1))/7))
FROM maximo.financialperiods fp
WHERE TRUNC (wo.actfinish) BETWEEN fp.periodstart
AND fp.periodend) fin_period,
wo.wo8 as assetgroup,
Count(wo.wonum)
from maximo.workorder wo
where (TRUNC (wo.actfinish) BETWEEN TO_DATE (:startdate, 'DD/MM/YYYY')
AND TO_DATE (:enddate, 'DD/MM/YYYY'))
group by wo.actfinish,
wo.wo8
我怀疑原因是您需要按第一列进行汇总。根据查询的结构,最简单的方法是使用子查询:
select fin_period, wo.wo8, count(wo.wonum) as cnt
from (select (SELECT (fp.financialperiod || ' week ' || ceil(floor((wo.actfinish - p.periodstart+1))/7))
FROM maximo.financialperiods fp
WHERE TRUNC (wo.actfinish) BETWEEN fp.periodstart AND fp.periodend
) as fin_period,
wo.wo8 as assetgroup, wo.wonum
from maximo.workorder wo
where TRUNC(wo.actfinish) BETWEEN TO_DATE (:startdate, 'DD/MM/YYYY')
AND TO_DATE (:enddate, 'DD/MM/YYYY')
) t
group by fin_period, wo.wo8;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句