The query below work s great.
I need to add the Year and Month to show on filter panel.
When I add the YEAR(c.actfinish) as [Year], to the filter panel the data will not load and times out.
The Month works great.
The data is in the list and runs in < 1s.
Why month works but year does not.
!! Oh by the way it was working 4 PHPRunner build back.
Using PHPRunner Enterprise! 10.5 build 37251 x64
/ Query /
/ MS-102 Completed YEar-Month-Summary Estimated Labor by Classification/
select
--a.description,
YEAR(c.actfinish) as [Year],
MONTH(c.actfinish) as [Month],
cs.classificationid,
CAST(SUM(c.estdur) as numeric(6,2)) as [Esthours],
CAST(SUM(b.regularhrs) as Numeric(6,2)) as [Reghrs],
--(CAST(SUM(b.regularhrs) as Numeric(6,2))) / (CAST(SUM(c.estdur) as numeric(6,2))) as [%POC], / Percentage of Completion /
(CAST(SUM(b.regularhrs) as Numeric(6,2))) / (CAST(SUM(c.estdur) as numeric(6,2))) 100 as [%Est-Accuracy], / Percentage of Completion Easy Read*/
Count(cs.classificationid) as [#WO]
from workorder a
left outer join labtrans b on b.refwo=a.wonum
left outer join woactivity c on c.wonum=a.wonum --a.wonum=c.wonum
Left Join classstructure as cs ON cs.classstructureid = a.classstructureid
where (a.status in('comp','Close')) and a.wogroup in
(select wogroup from workorder
where maintby='ms102' and (worktype!='ppm' and worktype!='mpm' OR worktype is null) and a.taskid is not null)
group by cs.classificationid, YEAR(c.actfinish),MONTH(c.actfinish)
Order By YEAR(c.actfinish)desc,MONTH(c.actfinish)desc,cs.classificationid