SELECT
  DECODE(LAW_OCCUPATION_ID, NULL, 0, LAW_OCCUPATION_ID) AS LAW_OCCUPATION_ID,
  NVL(LAW_OCCUPATION_NAME, 'ไม่ได้ระบุ') AS LAW_OCCUPATION_NAME,
  SUM(N_AMOUT_PAY) AS n_gen,
  SUM(N_GENERTION_STAFF) AS n_staff 
FROM
  DW_ES_SUM_TRAIN_INOUT 
WHERE
--   GENERATION_START_DATE BETWEEN TO_DATE('2024-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
--   AND TO_DATE('2024-11-02 23:59:59', 'YYYY-MM-DD HH24:MI:SS') 
--   AND 

GENERATION_START_DATE <= TO_DATE('2024-10-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS') AND
  GEN_APPROVE_STATUS = 2
  AND TRAIN_STATUS_CHK IN (6)
  AND CERTIFICATE_DATE BETWEEN TO_DATE (
										'2024-10-01 00:00:00',
										'YYYY-MM-DD HH24:MI:SS'
									)
								AND TO_DATE (
									'2024-11-02 23:59:00',
									'YYYY-MM-DD HH24:MI:SS')
GROUP BY
  DECODE(LAW_OCCUPATION_ID, NULL, 0, LAW_OCCUPATION_ID),
  NVL(LAW_OCCUPATION_NAME, 'ไม่ได้ระบุ')
ORDER BY LAW_OCCUPATION_ID