SELECT
  DECODE(LAW_NEW_ID, NULL, 0, LAW_NEW_ID) AS LAW_NEW_ID,
  NVL(DESC_LAW, 'ไม่ได้ระบุ') AS DESC_LAW,
  SUM(N_AMOUT_PAY) AS n_gen,
  SUM(N_GENERTION_STAFF) AS n_staff ,
  COUNT(DISTINCT COMPANY_ID) AS count_com
FROM
  DW_ES_SUM_TRAIN_INOUT 
WHERE
--   GENERATION_START_DATE BETWEEN TO_DATE('2024-11-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
--   AND TO_DATE('2025-05-14 23:59:59', 'YYYY-MM-DD HH24:MI:SS') 
  
  GENERATION_START_DATE >= TO_DATE('2024-11-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
  GEN_APPROVE_STATUS = 2
  AND GEN_APPROVE_STATUS = 2
  AND TRAIN_STATUS_CHK = 6
  AND CERTIFICATE_DATE BETWEEN TO_DATE (
										'2024-10-01 00:00:00',
										'YYYY-MM-DD HH24:MI:SS'
									)
								AND TO_DATE (
									'2025-08-22 23:59:00',
									'YYYY-MM-DD HH24:MI:SS')
GROUP BY
  DECODE(LAW_NEW_ID, NULL, 0, LAW_NEW_ID),
  NVL(DESC_LAW, 'ไม่ได้ระบุ')
ORDER BY LAW_NEW_ID