0

I am attempting create a KPI in Maximo that will count the work orders completed on time and divide it by the number of work orders due for the current month, then multiply it by 100 to return a percentage. And I'm failing. The queries will return the correct values independently, but do not seem to work in this situation. Please help.

select distinct (select count(wonum) 
                   from workorder 
                  where((status = 'CLOSE' or status = 'COMP') 
                    and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
                    and upper(crewid) = '17D02' 
                    and istask = 0 
                    and siteid = 'INDPLS' 
                    and worktype = 'PM') 
                    and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0)) 
                    and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001) 
                    AND ACTFINISH < TARGCOMPDATE) /
                (select count(wonum) 
                   from workorder 
                  where((woclass = 'WORKORDER' or woclass = 'ACTIVITY')
                    and upper(crewid) = '17D02' 
                    and istask = 0 
                    and siteid = 'INDPLS' 
                    and worktype = 'PM')  
                    and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0)) 
                    and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001))) * 100 
  from dummy_table
1

I'll add an answer since I believe you can improve the efficiency of your query by doing 1 scan instead of 2:

select 1.0*count(case when ACTFINISH < TARGCOMPDATE then wonum end) 
     / count(wonum) 
from workorder 
where (status = 'CLOSE' or status = 'COMP') 
  and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
  and upper(crewid) = '17D02' 
  and istask = 0 
  and siteid = 'INDPLS' 
  and worktype = 'PM' 
  and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0)) 
  and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001) 

I guess the original problem is integer division, i.e 1/4=0 while 1.0/4=0.25. If you want the result as a percent, multiply with 100.0 instead of 1.0:

select 100.0*count(case when ACTFINISH < TARGCOMPDATE then wonum end) 
     / count(wonum) 
from workorder 
where (status = 'CLOSE' or status = 'COMP') 
  and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
  and upper(crewid) = '17D02' 
  and istask = 0 
  and siteid = 'INDPLS' 
  and worktype = 'PM' 
  and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0)) 
  and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001) 

Not the answer you're looking for? Browse other questions tagged or ask your own question.