KPI to show number of leak investigations with no follow up

Question

Hi Everyone,

A pleasure being here, I was hoping someone could lend me a hand with the following query, I'm trying to display a number using a KPI through in a startcenter, it deals with "Leak Investigations with no follow up WO's" - I really appreciate your feedback and thank you for such an awesome resource, a little caveat I did not write this query, it was inherit to me.

under the "Select" pane I have
select count (1)
from workorder.workorderspec

under the "Where" pane I have
(classstructureid in (select workorderspec.classstructureid
from workorderspec where classstructureid is not null
and workorderspec.refobjectid = workorder.workorderid
and workorderspec.assetattrid= 'PLLEAKCLASS'
and upper(workorderspec.alnvalue) != 'NOLEAK' ))
and ((jpnum = 'INVEST-LEAK-D' or jpnum = 'RSPND-GO-D'))
and wonum not in (select recordkey from relatedrecord where relatedrecclass = 'WORKORDER')

Accepted Answer

Marvin,

Something like this should work:

select count(1) from workorder
where workorder.jpnum in ('INVEST-LEAK-D','RSPND-GO-D')
and
exists(select 1 from workorderspec
where workorder.wonum = workorderspec.wonum
and workorder.siteid = workorderspec.siteid
and workorder.classstructureid = workorderspec.classstructureid
and workorderspec.assetattrid = 'PLLEAKCLASS'
and upper(workorderspec.alnvalue) != 'NOLEAK')
and
not exists(select 1 from relatedrecord
where workorder.wonum = relatedrecord.recordkey
and workorder.siteid = relatedrecord.siteid
and workorder.woclass = relatedrecord.class
and relatedrecord.relatedrecclass = 'WORKORDER'
and relatedrecord.relatetype = 'FOLLOWUP')

Hope this helps.

Maximo Open Forum