SELECT ISNULL(B.ROW_SEQ, ROW_NUMBER() OVER (ORDER BY A.COMM_CD)) ROW_SEQ
, A.COMM_DTL_CD DOWN_TIME_CD
, A.COMM_DTL_NM DOWN_TIME_NM
, (CASE A.COMM_DTL_NM
WHEN 'shift' THEN B.SHIFT_CD
ELSE A.COMM_DTL_NM2 END) SUB_NM
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C01')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE1 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE1
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C02')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE2 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE2
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C03')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE3 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE3
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C04')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE4 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE4
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C05')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE5 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE5
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C06')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE6 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE6
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C07')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE7 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE7
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C08')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE8 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE8
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C09')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE9 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE9
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C10')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE10 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE10
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C11')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE11 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE11
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C12')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE12 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE12
, ISNULL((CASE A.COMM_DTL_CD
WHEN 'R01' THEN (SELECT COMM_DTL_NM FROM COMM WHERE COMM_CD = 'ShiftDT' AND COMM_DTL_CD = '2' AND COMM_DTL_CD2 = 'C13')
ELSE ISNULL( CAST(B.DOWM_TIME_VALUE13 AS VARCHAR(10)) ,'0') END),'0') DOWM_TIME_VALUE13
FROM COMM A LEFT OUTER JOIN
P_DOWN_TIME_MNG B
ON A.COMM_DTL_CD = B.DOWN_CD1
AND B.WORK_PRT_GB = '02' -- Formulation Solid or Liquid or Packing
AND B.LINE_CD = 'WG3F' -- Line
AND B.AGI_CODE = '228797' -- Product
AND B.SHIFT_CD = '2' -- Shift
AND B.WORK_DATE = '2021-03-02' -- Date
WHERE A.COMM_CD = 'DTM'
