SELECT ms.serial_number, cd.last_meter_import, cd.`End`, cd.`Begin`, cd.`End` - cd.`Begin` blk_mtr,
ABS(DATEDIFF(cd.last_meter_import, '2019-06-10')) cur_yr_remain,
ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) jul_to_last_mtr,
(cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) usage_jul_to_last_mtr,
ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) * ABS(DATEDIFF(cd.last_meter_import, '2019-06-10'))) est_vol,
ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) * ABS(DATEDIFF(cd.last_meter_import, '2019-06-10'))) + (cd.`End` - cd.`Begin`) fy_vol
FROM machine_status ms
INNER JOIN current_devices cd ON cd.SerialNumber = ms.serial_number
WHERE cd.org_id_ma = 39
AND ms.service_needed = 1
GROUP BY serial_number

UNION

SELECT SerialNumber, cd.last_meter_import, cd.`End`, cd.`Begin`, cd.`End` - cd.`Begin` blk_mtr,
ABS(DATEDIFF(cd.last_meter_import, '2019-06-10')) cur_yr_remain,
ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) jul_to_last_mtr,
(cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) usage_jul_to_last_mtr,
ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) * ABS(DATEDIFF(cd.last_meter_import, '2019-06-10'))) est_vol,
ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) * ABS(DATEDIFF(cd.last_meter_import, '2019-06-10'))) + (cd.`End` - cd.`Begin`) fy_vol
FROM current_devices cd
LEFT JOIN machine_status ms ON ms.serial_number = cd.SerialNumber
WHERE cd.org_id_ma = 39
AND ms.serial_number IS NULL

UNION

SELECT SerialNumber, cd.last_meter_import, cd.`End`, cd.`Begin`, cd.`End` - cd.`Begin` blk_mtr,
ABS(DATEDIFF(cd.last_meter_import, '2019-06-10')) cur_yr_remain,
ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) jul_to_last_mtr,
(cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.`BDate`, cd.last_meter_import)) usage_jul_to_last_mtr,
ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) * ABS(DATEDIFF(cd.last_meter_import, '2019-06-10'))) est_vol,
ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`)) * ABS(DATEDIFF(cd.last_meter_import, '2019-06-10'))) + (cd.`End` - cd.`Begin`) fy_vol
FROM current_devices cd
WHERE cd.org_id_ma = 39
AND DATEDIFF(CURRENT_TIMESTAMP, cd.last_meter_import) > 30;