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,
  obtype,
  fcst_lev,
  vx_mask,
'VIS' fcst_var,
  'BCRMSE' 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.obtype IN ('ADPSFC')
  AND   BINARY h.fcst_lev IN ('L0')
  AND   BINARY h.vx_mask IN ('WEST')
  AND   BINARY ld.fcst_init_beg IN ('2023-07-14 00:00:00', '2023-07-14 06:00:00', '2023-07-14 12:00:00', '2023-07-14 18:00:00', '2023-07-15 00:00:00', '2023-07-15 06:00:00', '2023-07-15 12:00:00', '2023-07-15 18:00:00', '2023-07-16 00:00:00', '2023-07-16 06:00:00', '2023-07-16 12:00:00', '2023-07-16 18:00:00', '2023-07-17 00:00:00', '2023-07-17 06:00:00', '2023-07-17 12:00:00', '2023-07-17 18:00:00', '2023-07-18 00:00:00', '2023-07-18 06:00:00', '2023-07-18 12:00:00', '2023-07-18 18:00:00', '2023-07-19 00:00:00', '2023-07-19 06:00:00', '2023-07-19 12:00:00', '2023-07-19 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 = 'VIS'
  AND ld.stat_header_id = h.stat_header_id;

