SELECT
s.model,s.fcst_init,s.fcst_valid,s.fcst_lead,s.fcst_thr,s.fcst_var,
  'CENTX_DCM' stat_name,
  s.stat_value - s2.stat_value stat_value
FROM (SELECT
  model,
  NOW()  fcst_init,
  fcst_valid,
  fcst_lead,
  fcst_thr,
'REFC' fcst_var,  object_id,
  'CENTX_FCM' stat_name,
  centroid_x stat_value
FROM
  mode_header ,
  mode_obj_single ,
  mode_cts 
WHERE
    BINARY fcst_thr IN ('>=30')
  AND   BINARY model IN ('GSL_05_MPAS', 'HRRR', 'NSSL_MPAS_HN', 'NSSL_MPAS_HT', 'NSSL_MPAS_RT', 'RRFS')
  AND   BINARY fcst_lead 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')
 AND  BINARY  fcst_var = 'REFC'
  AND fcst_flag = 1
  AND simple_flag = 0
  AND matched_flag = 1  AND mode_obj_single.mode_header_id = mode_header.mode_header_id
  AND mode_cts.mode_header_id = mode_obj_single.mode_header_id
  AND mode_cts.field = 'OBJECT' ) s, ( SELECT
  model,
  NOW()  fcst_init,
  fcst_valid,
  fcst_lead,
  fcst_thr,
'REFC' fcst_var,  object_id,
  'CENTX_OCM' stat_name,
  centroid_x stat_value
FROM
  mode_header ,
  mode_obj_single ,
  mode_cts 
WHERE
    BINARY fcst_thr IN ('>=30')
  AND   BINARY model IN ('GSL_05_MPAS', 'HRRR', 'NSSL_MPAS_HN', 'NSSL_MPAS_HT', 'NSSL_MPAS_RT', 'RRFS')
  AND   BINARY fcst_lead 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')
 AND  BINARY  fcst_var = 'REFC'
  AND fcst_flag = 0
  AND simple_flag = 0
  AND matched_flag = 1  AND mode_obj_single.mode_header_id = mode_header.mode_header_id
  AND mode_cts.mode_header_id = mode_obj_single.mode_header_id
  AND mode_cts.field = 'OBJECT' ) s2
WHERE
 s.fcst_var = s2.fcst_var
 AND  BINARY  SUBSTRING(s.object_id, -3) = SUBSTRING(s2.object_id,  -3) 
  AND s.stat_value != -9999 AND s2.stat_value != -9999 AND s.fcst_valid = s2.fcst_valid  AND s.fcst_lead = s2.fcst_lead AND s.model = s2.model;

