Hi There,
I am trying to retreive the most recent dated record from two joined tables. One table has the unique record that I am looking for, the second has seperate log entries that are related to the single record.
What I am trying to do is return the most recent dated log record to match the original record, but as soon as I add more than one column from the "log" table all the historical log records are returned, not wanted, we just want to see the most recent log entry, and when it was entered.
QUERY:
SELECT WO.WONUM
,WO.STATUSDATE
,WO.ACTSTART
,WO.DESCRIPTION
,WO.LOCATION
,WO.ASSETNUM
,WO.WORKTYPE
,WO.FAILURECODE
,WO.PROBLEMCODE
,WO.SUPERVISOR
,WO.CREWID
,WO.WOPRIORITY
,W.LOGTYPE
,W.DESCRIPTION
,W.CREATEDATE
FROM WORKORDER WO
INNER JOIN(SELECT MAX(wl.createdate)
,WL.RECORDKEY
,WL.LOGTYPE
,WL.DESCRIPTION
,WL.CLASS
FROM WORKLOG WL
group by wl.recordkey
,wl.logtype
,WL.DESCRIPTION
,WL.CLASS) W ON (W.RECORDKEY = WO.WONUM AND W.CLASS = 'WORKORDER')
WHERE WO.STATUS IN ('INPRG','WAPPR','APPR','WINFO')
AND WO.WONUM LIKE '3393278' -- selecting a sample record that has many log entries for testing
AND WO.WORKTYPE IN ('RM','RW')
I would like to add columns from the WORKLOG table to the output, but there is a many to one relationship between the WORKLOG table and the WORKORDERS table.