SELECT
  model,
  ld.fcst_init_beg,
  ld.fcst_valid_beg,
  if( (select fcst_lead_offset FROM model_fcst_lead_offset WHERE model = h.model) is NULL , ld.fcst_lead , ld.fcst_lead + (select fcst_lead_offset FROM model_fcst_lead_offset WHERE model = h.model) ) fcst_lead,
  fcst_lev,
  obtype,
  vx_mask,
'AOTK' fcst_var,
  'BCMSE' stat_name,
  'NA' stat_value,
  ld.total,
  ld.fbar,
  ld.obar,
  ld.fobar,
  ld.ffbar,
  ld.oobar,
 ld.mae
FROM
  stat_header h,
  line_data_sl1l2 ld
WHERE
    BINARY h.fcst_lev IN ('L0')
  AND   BINARY h.obtype IN ('AERONET_AOD')
  AND   BINARY h.vx_mask IN ('AZCA')
  AND   BINARY ld.fcst_init_beg IN ('2024-01-08 12:00:00', '2024-01-08 18:00:00', '2024-01-09 06:00:00', '2024-01-09 12:00:00', '2024-01-09 18:00:00', '2024-01-10 00:00:00', '2024-01-10 06:00:00', '2024-01-10 12:00:00', '2024-01-10 18:00:00', '2024-01-11 00:00:00', '2024-01-11 06:00:00', '2024-01-11 12:00:00', '2024-01-11 18:00:00', '2024-01-12 00:00:00', '2024-01-12 06:00:00', '2024-01-12 12:00:00', '2024-01-12 18:00:00', '2024-01-13 00:00:00', '2024-01-13 06:00:00', '2024-01-13 12:00:00', '2024-01-13 18:00:00', '2024-01-14 00:00:00', '2024-01-14 06:00:00', '2024-01-14 12:00:00', '2024-01-14 18:00:00', '2024-01-15 00:00:00', '2024-01-15 06:00:00', '2024-01-15 12:00:00', '2024-01-15 18:00:00', '2024-01-16 00:00:00', '2024-01-16 06:00:00', '2024-01-16 12:00:00', '2024-01-16 18:00:00', '2024-01-17 00:00:00', '2024-01-17 06:00:00', '2024-01-17 12:00:00', '2024-01-17 18:00:00', '2024-01-18 00:00:00', '2024-01-18 06:00:00', '2024-01-18 12:00:00', '2024-01-18 18:00:00', '2024-01-19 00:00:00', '2024-01-19 06:00:00', '2024-01-19 12:00:00', '2024-01-19 18:00:00', '2024-01-20 00:00:00', '2024-01-20 06:00:00', '2024-01-20 12:00:00', '2024-01-20 18:00:00', '2024-01-21 00:00:00', '2024-01-21 06:00:00', '2024-01-21 12:00:00', '2024-01-21 18:00:00', '2024-01-22 00:00:00', '2024-01-22 06:00:00', '2024-01-22 12:00:00', '2024-01-22 18:00:00', '2024-01-23 00:00:00', '2024-01-23 06:00:00', '2024-01-23 12:00:00', '2024-01-23 18:00:00', '2024-01-24 00:00:00', '2024-01-24 06:00:00', '2024-01-24 12:00:00', '2024-01-24 18:00:00', '2024-01-25 00:00:00', '2024-01-25 06:00:00', '2024-01-25 12:00:00', '2024-01-25 18:00:00', '2024-01-26 00:00:00', '2024-01-26 06:00:00', '2024-01-26 12:00:00', '2024-01-26 18:00:00', '2024-01-27 00:00:00', '2024-01-27 06:00:00', '2024-01-27 12:00:00', '2024-01-27 18:00:00', '2024-01-28 00:00:00', '2024-01-28 06:00:00', '2024-01-28 12:00:00', '2024-01-28 18:00:00', '2024-01-29 00:00:00', '2024-01-29 06:00:00', '2024-01-29 12:00:00', '2024-01-29 18:00:00', '2024-01-30 00:00:00', '2024-01-30 06:00:00', '2024-01-30 12:00:00', '2024-01-30 18:00:00', '2024-01-31 00:00:00', '2024-01-31 06:00:00', '2024-01-31 12:00:00', '2024-01-31 18:00:00', '2024-02-01 00:00:00', '2024-02-01 06:00:00', '2024-02-01 12:00:00', '2024-02-01 18:00:00', '2024-02-02 00:00:00', '2024-02-02 06:00:00', '2024-02-02 12:00:00', '2024-02-02 18:00:00', '2024-02-03 00:00:00', '2024-02-03 06:00:00', '2024-02-03 12:00:00', '2024-02-03 18:00:00', '2024-02-04 00:00:00', '2024-02-04 06:00:00', '2024-02-04 12:00:00', '2024-02-04 18:00:00', '2024-02-05 00:00:00', '2024-02-05 06:00:00', '2024-02-05 12:00:00', '2024-02-05 18:00:00', '2024-02-06 00:00:00', '2024-02-06 06:00:00', '2024-02-06 12:00:00', '2024-02-06 18:00:00', '2024-02-07 00:00:00', '2024-02-07 06:00:00', '2024-02-07 12:00:00', '2024-02-07 18:00:00', '2024-02-08 00:00:00', '2024-02-08 06:00:00', '2024-02-08 12:00:00', '2024-02-08 18:00:00')
  AND   BINARY h.model IN ('HRRR_verification_mem000', 'RRFS_verification_mem000')
  AND   BINARY  if( (select fcst_lead_offset FROM model_fcst_lead_offset WHERE model = h.model) is NULL , ld.fcst_lead , ld.fcst_lead + (select fcst_lead_offset FROM model_fcst_lead_offset WHERE model = h.model) )  IN ('0', '10000', '20000', '30000', '40000', '50000', '60000', '70000', '80000', '90000', '100000', '110000', '120000', '130000', '140000', '150000', '160000', '170000', '180000', '190000', '200000', '210000', '220000', '230000', '240000', '250000', '260000', '270000', '280000', '290000', '300000', '310000', '320000', '330000', '340000', '350000', '360000', '370000', '380000', '390000', '400000', '410000', '420000', '430000', '440000', '450000', '460000', '470000', '480000')
  AND   BINARY h.fcst_var = 'AOTK'
  AND ld.stat_header_id = h.stat_header_id;

