Hi Team,

Please find the Query and expected result pasted below.

Sql Query:
SELECT DISTINCT W.WONUM,W.DESCRIPTION,W.ASSETNUM,A.DESCRIPTION AS ASSET_DESCRIPTION,
L.DESCRIPTION AS LOCATION_DESCRIPTION,W.STATUS,W.ACTFINISH,TS.NUMVALUE,WL.KA_DETERIORATED,W.OWNER,
WL.CREATEDATE,T.TICKETID FROM WORKORDER W
LEFT JOIN ASSET A ON A.ASSETNUM=W.ASSETNUM
LEFT JOIN LOCATIONS L ON L.LOCATION=A.LOCATION
LEFT JOIN TICKET T ON T.ASSETNUM=W.ASSETNUM AND T.STATUS IN ('INPROG','NEW','ONHOLD','PENDING','QUEUED')
LEFT JOIN TICKETSPEC TS ON T.TICKETID=TS.TICKETID
LEFT JOIN WORKLOG WL ON WL.RECORDKEY=T.TICKETID
WHERE (W.WORKORDERID IN
(SELECT OWNERID FROM WFASSIGNMENT WHERE ASSIGNSTATUS='ACTIVE' AND ROLEID='SEEG' AND OWNERTABLE='WORKORDER') AND
W.STATUS = 'COMPLETE' AND (W.WOCLASS = 'WORKORDER' OR W.WOCLASS = 'ACTIVITY') AND
W.HISTORYFLAG = 0 AND W.ISTASK = 0 AND W.SITEID = 'CVL' AND
(W.JPNUM LIKE '%C00528%' OR W.JPNUM = 'LOW001' OR W.JPNUM = 'AK0041' OR W.JPNUM = 'C00530' OR W.JPNUM = 'C00529' OR W.JPNUM = 'AK0190' OR W.JPNUM = 'AK0189' OR
W.JPNUM = 'C00546' OR W.JPNUM = 'C00547' OR W.JPNUM = 'AK6016' OR W.JPNUM = 'AK6015' OR W.JPNUM = 'AK6012' OR W.JPNUM = 'AK6011' OR W.JPNUM = 'AK6010' OR W.JPNUM = 'AK6009' OR
W.JPNUM = 'AK6001' OR W.JPNUM = 'AK6002' OR W.JPNUM = 'AK6003' OR W.JPNUM = 'AK6004' OR W.JPNUM = 'AK6005' OR W.JPNUM = 'AK6006' OR W.JPNUM = 'AK0038' OR W.JPNUM = 'C00555' OR
W.JPNUM = 'C00557' OR W.JPNUM = 'C00556')
) AND
TS.NUMVALUE IN(SELECT MAX(NUMVALUE) FROM TICKETSPEC
WHERE ASSETATTRID='DFCT_RISK_SCR' AND TICKETID IN(SELECT TICKETID FROM TICKET
WHERE ASSETNUM=W.ASSETNUM AND TICKET.STATUS IN ('INPROG','NEW','ONHOLD','PENDING','QUEUED') ))
AND WL.CREATEDATE =(SELECT MAX(CREATEDATE)FROM WORKLOG WHERE RECORDKEY=T.TICKETID)
ORDER BY W.WONUM,TS.NUMVALUE,CASE WHEN WL.KA_DETERIORATED='Y' THEN 1
WHEN WL.KA_DETERIORATED='UNK' THEN 2
WHEN WL.KA_DETERIORATED='N' THEN 3
WHEN WL.KA_DETERIORATED='BL' THEN 4
WHEN WL.KA_DETERIORATED='NA' THEN 5
ELSE 6 END,WL.CREATEDATE;

The above Query is Returning Data From 2 row to 8 row.

​Thanks in advance.

Regards,
Anuja.D

------------------------------
Anuja Dhanekula
------------------------------

#Maximo
#AssetandFacilitiesManagement

Hi Anuja,

This is really hard to judge from your data as there is lots of data we cannot see.  One specifically is that we don't know if the work order is currently in Workflow.  We don't see what job plan is attached to the work order.

I'd try an use an EXISTS over an IN statement as that access an index versus a possible full table scan.

I'm trying to understand the TS part of the query as you're asking for work orders where their associated tickets (NB: This is across all tickets not just SR) have a ticketspec of ASSETATTRID = 'DFCT_RISK_SCR' --> where that value is matching the maximum value across all tickets for that asset (from the work order).  Surely if your using a MAX then there should only be one value returned and it should be = instead?

Also, when you are asking for the max(createdate) remember it goes down to the millisecond, so the Excel spreadsheet won't show us that unless you configure it correctly.

We don't know what job plan was associated with each work order.

------------------------------
===============================
Craig Kokay,
Lead Senior Maximo/IoT Consultant
ISW
Sydney, NSW, Australia
Ph: 0411-682-040
=================================
#IBMChampion2021
------------------------------