SELECT
s.model,s.fcst_init,s.fcst_valid,s.fcst_lead,HOUR(s.fcst_valid) valid_hour,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,
  HOUR(fcst_valid) valid_hour,
  fcst_thr,
'WRF_precip' 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 ('>=0.254')
  AND   BINARY model IN ('AI_step12')
  AND HOUR(fcst_valid) IN ('00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23')
 AND  BINARY  fcst_var = 'WRF_precip'
  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,
  HOUR(fcst_valid) valid_hour,
  fcst_thr,
'WRF_precip' 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 ('>=0.254')
  AND   BINARY model IN ('AI_step12')
  AND HOUR(fcst_valid) IN ('00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23')
 AND  BINARY  fcst_var = 'WRF_precip'
  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;

SELECT
s.model,s.fcst_init,s.fcst_valid,s.fcst_lead,HOUR(s.fcst_valid) valid_hour,s.fcst_thr,s.fcst_var,
  'CENTY_DCM' stat_name,
  s.stat_value - s2.stat_value stat_value
FROM (SELECT
  model,
  NOW()  fcst_init,
  fcst_valid,
  fcst_lead,
  HOUR(fcst_valid) valid_hour,
  fcst_thr,
'WRF_precip' fcst_var,  object_id,
  'CENTY_FCM' stat_name,
  centroid_y stat_value
FROM
  mode_header ,
  mode_obj_single ,
  mode_cts 
WHERE
    BINARY fcst_thr IN ('>=0.254')
  AND   BINARY model IN ('AI_step12')
  AND HOUR(fcst_valid) IN ('00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23')
 AND  BINARY  fcst_var = 'WRF_precip'
  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,
  HOUR(fcst_valid) valid_hour,
  fcst_thr,
'WRF_precip' fcst_var,  object_id,
  'CENTY_OCM' stat_name,
  centroid_y stat_value
FROM
  mode_header ,
  mode_obj_single ,
  mode_cts 
WHERE
    BINARY fcst_thr IN ('>=0.254')
  AND   BINARY model IN ('AI_step12')
  AND HOUR(fcst_valid) IN ('00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23')
 AND  BINARY  fcst_var = 'WRF_precip'
  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;

