# Issue with Maximo KPI Returning “0” Instead of a Calculated % [closed]

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
``````

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)
``````