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.
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;