大家下午好，我的马克斯到底怎么了，我遇到了一点障碍。而不是为每个唯一设备和pm关系行返回预期的1到1，其中workorder具有最近的completeddate。然而，作为输出，我得到的是每一个相关的工作订单每一个独特的设备pm已经发生。例子：

CURRENT RESULTS
EQUIPMENT  |  PM   |  WORKORDER   |  COMPLETEDDATE  |
ALPHA-WL   |    P1 |    DS1234    |    7/9/2020
ALPHA-WL   |    P1 |    DS1233    |    6/3/2020
ALPHA-WL   |    P2 |    DS1532    |    7/9/2020
BETA-WL    |    P2 |    DS4321    |    4/2/2020
BETA-WL    |    P2 |    DS4331    |    5/2/2020

EXPECTED RESULTS
EQUIPMENT  |   PM  |  WORKORDER  |  COMPLETEDDATE  | 
ALPHA-WL   |   P1  |   DS1234    |   7/9/2020
ALPHA-WL   |   P2  |  DS1532     |   7/9/2020
BETA-WL    |   P2  |  DS4331     |   5/2/2020

我想可能是因为completeddate中日期的格式有问题，所以我继续把它转换成应该可以工作的格式，但是仍然没有解决这个问题。我真的很感激在这个问题上能得到任何支持，这里是一个使用sql的例子。

SELECT  
MRC,
EQUIPMENT,
DESCRIPTION,
CLASSCODE,
CRITICALITY,
STATUS,
PM,
PMDESCRIPTION,
NEXTDUE,
PMTYPE,
ASSIGNEDBY,
ASSIGNEDTO,
FREQUENCY,
UOM,
WOEQUIP,
WORKORDER,
WORKORDERPM,
PMCHECKS,
EQCHECKS,
MAX(LastCompleted) AS COMPLETEDDATE,
WOSTATUS
FROM
(SELECT
SE.EquipmentSurfaceAssetBK AS "MRC",
SE.EquipmentCodeBK AS "EQUIPMENT",
SE.EquipmentDescription AS "DESCRIPTION",
SE.EquipmentClassBK AS "CLASSCODE",
SE.EquipmentCriticality AS "CRITICALITY",
SE.EquipmentCostCode AS "COSTCODE",
SE.EquipmentStatus AS "STATUS",
PE.EqPMCodeBK AS "PM",
PM.PMDescription AS "PMDESCRIPTION",
PE.EqPMDueDate AS "NEXTDUE",
PE.EqPMType AS "PMTYPE",
PE.EqPMDeactivatedDate AS "DDATE",
PE.EqPMAssignedBy AS "ASSIGNEDBY",
PE.EqPMAssignedToResourceBK AS "ASSIGNEDTO",
PE.EqPMFrequency AS "FREQUENCY",
PE.EqPMFrequencyUOM AS "UOM",
WO.WorkOrderEquipmentcodeBK AS "WOEQUIP",
WO.WorkOrderCodeBK AS "WORKORDER",
WO.WorkOrderPMCodeBK AS "WORKORDERPM",
CASE
    WHEN WO.WorkOrderPMCodeBK = PE.EqPMCodeBK THEN 'YES' ELSE 'NO'
END AS "PMCHECKS",
CASE
    WHEN WO.WorkOrderEquipmentcodeBK = SE.EquipmentCodeBK THEN 'YES' ELSE 'NO'
END AS "EQCHECKS",
WO.WorkOrderStatus AS "WOSTATUS",
CAST( CAST(WO.WorkOrderCompletedDate AS DATE FORMAT 'MM/DD/YYYY') AS DATE FORMAT 'MM/DD/YYYY') AS "LastCompleted"
FROM IDW_PL_SURFACE.DIMSurfaceEquipment SE 
JOIN IDW_PL_SURFACE.DIMEquipmentPM PE ON SE.EquipmentCodeBK = PE.EqPMEquipmentCodeBK
JOIN IDW_PL_SURFACE.DIMSurfacePM PM ON PE.EqPMCodeBK = PM.PMCodeBK
JOIN IDW_PL_SURFACE.DIMWorkOrder WO ON SE.EquipmentCodeBK = WO.WorkOrderEquipmentcodeBK) AS ST
WHERE 
1=1
AND
STATUS IN ( 'I', 'IDLE')
AND
DDATE is Null
AND
PMTYPE IN (  'V', 'F')
AND PMCHECKS IN ( 'YES')
AND EQCHECKS IN ( 'YES')
AND WOSTATUS IN ('C', 'D')
AND NEXTDUE IS NOT NULL
GROUP BY
MRC,
EQUIPMENT,
DESCRIPTION,
CLASSCODE,
CRITICALITY,
STATUS,
PM,
PMDESCRIPTION,
NEXTDUE,
PMTYPE,
ASSIGNEDBY,
ASSIGNEDTO,
FREQUENCY,
UOM,
WOEQUIP,
WORKORDER,
WORKORDERPM,
PMCHECKS,
EQCHECKS,
WOSTATUS