How can I have only 1 record for a case statement by group?

  Kiến thức lập trình

How can I have a single record for case statement by grouping? If there is an other solution, please let me know.

Here is an example of 2 records but I only want one. You will see the only difference is the last 2 columns which are either F or T. If it is Late_Process, I want a T and if it is also Miss_Cutoff, I want a T in the same record instead of 2.

Example

SEL
    SPDM.PRODUCT
    ,SPDM.OCNTR
    ,SPDM.DCNTR
    ,SPDM.MLTYP
    ,SQ.RETURN_FLG
    ,SPDM.ODT
    ,SQ.F_PLT_F_PROC_WC ORIGINATING_WC
    ,SQ.F_PLT_F_PROC_DTM_LOC
    ,SCANS.RELATED_SCAN_WC DESTINATION_WC
    ,SCANS.RELATED_SCAN_DTM_LOC
    ,SPDM.ECUST CUSTOMER_ID
    ,SPDM.ITEM
    ,CASE 
        WHEN SCANS.PAT_CAT_SEQ = 500
            AND CHECK_RESULT_CODE = 'OT'
        THEN 'T' ELSE 'F'
     END LATE_PROCESS
    ,CASE 
        WHEN SCANS.PAT_CAT_SEQ = 600
            AND CHECK_RESULT_CODE = 'OT'
        THEN 'T' ELSE 'F'
     END MISS_CUTOFF
FROM DL_SQ_PRD_INT.FCT_SPDM_FLASH SPDM
LEFT JOIN DL_SQ_PRD_INT.FCT_PAT_FLASH_SCAN SCANS
ON SPDM.ITEM = SCANS.PIN
    AND SPDM.FYFW = SCANS.FYFW
    AND SCANS.PAT_CAT_SEQ IN (500, 600)
    AND SCANS.DP0_IND = 1
INNER JOIN PIN_ORIGINATING_OT OOT
ON SPDM.ITEM = OOT.PIN
LEFT JOIN DL_SQ_PRD_INT.VW_FCT_ITEM SQ
ON SCANS.ITEM_ID = SQ.ITEM_ID
WHERE SPDM.ITEM = '2010778075710766'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;

LEAVE A COMMENT