SELECT * FROM (
SELECT inv_unit.id As [unit_id],
srv_event.creator ,
srv_event_types.id as [service_id],
inv_unit_reefer_records.recording_time,
inv_unit_reefer_records.co2,
--max(round(inv_unit_reefer_records.return_tmp,2)) AS [temp_recorded],
round(inv_unit_reefer_records.return_tmp,2) AS [temp_recorded],
COALESCE(inv_unit_reefer_records.remark,'') AS [remarks],
COALESCE(inv_unit_reefer_records.humdity,0) as [humidity],
COALESCE(inv_unit_reefer_records.vent,0) AS [vent],
COALESCE(inv_unit_reefer_records.fault_code,'') as [fault_code],
coalesce(inv_unit_reefer_records.o2,'') as [O2],
inv_unit.power_rqst_time,
inv_goods.temp_reqd_C,
inv_unit_fcy_visit.arrive_pos_locid,
inv_unit.category,
CASE WHEN dbo.inv_unit.freight_kind = 'FCL' THEN 'F' WHEN dbo.inv_unit.freight_kind = 'MTY' THEN 'E' END AS eq_status,
inv_unit_fcy_visit.time_in, inv_unit_fcy_visit.time_out
,argo_carrier_visit.id as ob_locid
,ref_bizunit_scoped.id as container_operator
,inv_unit_fcy_visit.last_pos_slot

from
inv_unit
INNER JOIN inv_unit_reefer_records on inv_unit_reefer_records.unit_gkey = inv_unit.gkey
INNER JOIN srv_event on inv_unit.gkey = srv_event.applied_to_gkey
INNER JOIN srv_event_types on srv_event_types.gkey = srv_event.event_type_gkey and srv_event_types.id in ('UNIT_POWER_CONNECT','UNIT_POWER_DISCONNECT','REEFER_SETTINGS_RECORDED')
inner join inv_goods on inv_goods.gkey=inv_unit.goods
inner join inv_unit_fcy_visit on inv_unit.gkey=inv_unit_fcy_visit.unit_gkey
inner join dbo.argo_carrier_visit ON dbo.inv_unit_fcy_visit.intend_ob_cv = dbo.argo_carrier_visit.gkey
inner join ref_bizunit_scoped on ref_bizunit_scoped.gkey =inv_unit.line_op

Where
inv_unit_reefer_records.recording_time = srv_event.placed_time and
srv_event.creator not in ('move:Stow Factor Upd','Server:TruckPosition','Server:AssignPosition','xps')

UNION ALL

SELECT DISTINCT inv_unit.id As [unit_id],
srv_event.creator ,
srv_event_types.id as [service_id],
srv_event.placed_time as recording_time,
null as co2,
null as [temp_recorded],
'' AS [remarks],
'' as [humidity],
'' AS [vent],
''as [fault_code],
'' as [O2],
inv_unit.power_rqst_time,
inv_goods.temp_reqd_C,
inv_unit_fcy_visit.arrive_pos_locid,
inv_unit.category,
CASE WHEN dbo.inv_unit.freight_kind = 'FCL' THEN 'F' WHEN dbo.inv_unit.freight_kind = 'MTY' THEN 'E' END AS eq_status,
inv_unit_fcy_visit.time_in, inv_unit_fcy_visit.time_out
,argo_carrier_visit.id as ob_locid
,ref_bizunit_scoped.id as container_operator
,inv_unit_fcy_visit.last_pos_slot
from
inv_unit
INNER JOIN srv_event on inv_unit.gkey = srv_event.applied_to_gkey
INNER JOIN srv_event_types on srv_event_types.gkey = srv_event.event_type_gkey and srv_event_types.id in ('UNIT_POWER_CONNECT','UNIT_POWER_DISCONNECT')
inner join inv_goods on inv_goods.gkey=inv_unit.goods
inner join inv_unit_fcy_visit on inv_unit.gkey=inv_unit_fcy_visit.unit_gkey
inner join dbo.argo_carrier_visit ON dbo.inv_unit_fcy_visit.intend_ob_cv = dbo.argo_carrier_visit.gkey
inner join ref_bizunit_scoped on ref_bizunit_scoped.gkey =inv_unit.line_op ) ReeferReadings

--where ReeferReadings.unit_id = 'OOLU6403398' order by 4 desc

GO