So how did you get on?

Here's an alternative way to write your query which cuts the complexity in half. Note that I've only included about 10% of the output rows for clarity:

INSERT INTO [QMIS_KPI120_DB].[dbo].[00_INPUT_ER_RSSI_TX_CARRIER_POWER_INSERTION]

SELECT

LocalCellID

RNC,

Start_Time,

DC_RELEASE,

Start_Date,

SUM(Case when DCVECTOR_INDEX = 0 then pmAverageRssi ELSE 0 End) as pmAverageRssi_0,

SUM(Case when DCVECTOR_INDEX = 0 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_0,

SUM(Case when DCVECTOR_INDEX = 10 then pmAverageRssi ELSE 0 End) as pmAverageRssi_10,

SUM(Case when DCVECTOR_INDEX = 10 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_10,

SUM(Case when DCVECTOR_INDEX = 20 then pmAverageRssi ELSE 0 End) as pmAverageRssi_20,

SUM(Case when DCVECTOR_INDEX = 20 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_20,

SUM(Case when DCVECTOR_INDEX = 30 then pmAverageRssi ELSE 0 End) as pmAverageRssi_30,

SUM(Case when DCVECTOR_INDEX = 30 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_30,

SUM(Case when DCVECTOR_INDEX = 40 then pmAverageRssi ELSE 0 End) as pmAverageRssi_40,

SUM(Case when DCVECTOR_INDEX = 40 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_40,

SUM(Case when DCVECTOR_INDEX = 50 then pmAverageRssi ELSE 0 End) as pmAverageRssi_50,

SUM(Case when DCVECTOR_INDEX = 50 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_50,

SUM(Case when DCVECTOR_INDEX = 60 then pmAverageRssi ELSE 0 End) as pmAverageRssi_60,

SUM(Case when DCVECTOR_INDEX = 60 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_60,

SUM(Case when DCVECTOR_INDEX = 64 then pmAverageRssi ELSE 0 End) as pmAverageRssi_64,

SUM(Case when DCVECTOR_INDEX = 64 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_64

FROM (

SELECT -- DISTINCT -- avoid using DISTINCT with an aggregating query

E.UCELL_ID As LocalCellID

,RNC As RNC

,Cast( Cast(YEAR_ID As varchar(4)) + '-' + Cast(MONTH_ID As varchar(2)) + '-' + Cast(DAY_ID As varchar(2)) + ' ' + Cast(HOUR_ID As varchar(2)) + ':00:00' As datetime) As Start_Time

,Cast( Cast(YEAR_ID As varchar(4)) + '-' + Cast(MONTH_ID As varchar(2)) + '-' + Cast(DAY_ID As varchar(2)) + ' ' + '00:00:00' As datetime) As Start_Date

,RBS As RbsUtranId

,H.Sector As Sector

,DCVECTOR_INDEX As DCVECTOR_INDEX

,MIN(H.DC_RELEASE) as DC_RELEASE

,Sum(pmAverageRssi) As pmAverageRssi

,Sum(pmTransmittedCarrierPower) As pmTransmittedCarrierPower

FROM ENIQSQLSERVER.DWHDB.dc.DC_E_RBS_CARRIER_V_RAW H

INNER Join ( -- e

SELECT DISTINCT

RBS_ID,

Ucell_ID,

RNC_ID,

LOCALCellID

FROM ENIQSQLSERVER.DWHDB.DC.DIM_E_RAN_UCELL

WHERE STATUS = 'ACTIVE'

--SELECT Distinct RBS_ID,Ucell_ID,RNC_ID,LOCALCellID FROM OPENQUERY(ENIQSQLSERVER, 'SELECT * FROM ENIQSQLSERVER.DWHDB.DC.DIM_E_RAN_UCELL Where STATUS = ''ACTIVE''')

) e

ON H.RBS = E.RBS_ID

And H.RNC = E.RNC_ID

And E.LOCALCellID = H.Sector

WHERE ROWSTATUS = 'LOADED'

--and ROWSTATUS <> 'DUPLICATE' -- unnecessary

--and ROWSTATUS <> 'SUSPECTED' -- unnecessary

AND DATE_ID = CAST(DATEADD(D, -1, GETDATE()) AS DATE)

GROUP BY

e.UCELL_ID

,RBS

,H.Sector

,DCVECTOR_INDEX

,YEAR_ID

,MONTH_ID

,DAY_ID

,HOUR_ID

,H.DC_RELEASE

,RNC

) Hourly

WHERE 1 = 1

--AND LocalCellID is not null -- unnecessary

--and RNC is not null -- unnecessary

and Start_Time is not null

and Start_Date is not null

and RbsUtranId is not null

--and Sector is not null -- unnecessary

and DC_RELEASE is not null

and LOCALCellID is not Null

and LOCALCellID not like ' %'

and LOCALCellID not like '%BEA%'

and LOCALCellID not like '%BSC%'

and LOCALCellID not like '%BTS%'

and LOCALCellID not like '%CB%'

and LOCALCellID not like '%CWH%'

and LOCALCellID not like '%G655%'

and LOCALCellID not like '%NULL%'

and LOCALCellID not like '%RM%'

and LOCALCellID not like '%RNC%'

and LOCALCellID not like '%TB%'

and LOCALCellID not like '%TEST%'

and LOCALCellID not like '%TST%'

and LOCALCellID not like '%WH%'

and LOCALCellID not like '655%'

and LOCALCellID not like 'B%'

GROUP BY

LocalCellID

,RNC

,Start_Time

,Start_Date

,DC_RELEASE